Additional embedded SQL statements

*** pgsql.doc/doc/src/sgml/ecpg.sgml.old 2009-10-07 12:05:28.000000000 +0200 --- pgsql.doc/doc/src/sgml/ecpg.sgml 2009-10-08 12:55:15.785009096 +0200 *************** *** 2420,2428 **** ! Additional embedded SQL statements CLOSE DATABASE --- 2420,2445 ---- ! Additional types ! ! The Informix-special "string" pseudo-type for storing right-trimmed character string data is now ! supported in Informix-mode without using typedef. In fact, in Informix-mode, ! ECPG refuses to process source files that contain typedef sometype string; ! ! EXEC SQL BEGIN DECLARE SECTION; ! string userid; /* this variable will contain trimmed data */ ! EXEC SQL END DECLARE SECTION; ! ! EXEC SQL FETCH MYCUR INTO :userid; ! ! ! ! ! ! Additional/missing embedded SQL statements + CLOSE DATABASE *************** *** 2436,2446 **** --- 2453,2755 ---- + + + FREE cursor_name + + + Due to the differences how ECPG works (i.e. which steps are purely grammar transformations + and which steps rely on the underlying runtime library compared to Informix's ESQL/C) + there is no FREE cursor_name statement in ECPG. This is because in ECPG, + DECLARE CURSOR previously didn't translate to a function call into + the runtime library at all. Now it does, just to simply reset SQLCA to follow Informix + behaviour. But it's still true that there's no runtime bookkeeping of SQL cursors in + the ECPG runtime library, only in the PostgreSQL server. + + + + + + FREE statement_name + + + FREE statement_name is a synonym for DEALLOCATE PREPARE statement_name. + + + + + + + + + + Additional syntax forms + + + + + Interchangeable USING and INTO + + + In DECRIBE and FETCH statements, the USING + and INTO keywords are interchangeable if the target is a Descriptor Area. + + + + + + Non-optional SQL keyword for Descriptor Areas + + + In DECRIBE and FETCH statements using Descriptor Areas, + the SQL keyword is not optional. DESCRIPTOR means an + SQLDA Descriptor Area, SQL DESCRIPTOR means a named SQL Descriptor Area. + + + + + Additional SQL descriptor type: SQLDA C-structure + + Besides the named SQL descriptors, ECPG now supports Informix-special SQLDA descriptors + for accessing fields properties and data from low-level C code. Global properties are: + + + + + sqld + + + The number of fields in the SQLDA descriptor. + + + + + + sqlvar + + + Pointer to the per-field properties. + + + + + + desc_name + + + Unused, filled with zerobytes. + + + + + + desc_occ + + + Size of the allocated structure. + + + + + + desc_next + + + Pointer to the structure, unused, contains NULL. + + + + + + reserved + + + Unused pointer, contains NULL. Kept for Informix-compatibility. + + + + + + + The per-field properties are below, they are stored in the sqlvar array: + + + + + sqltype + + + Type of the field. Constants are in sqltypes.h + + + + + + sqllen + + + Length of the field data. + + + + + + sqldata + + + Pointer to the field data. The pointer is of char * type, + the data pointed by it is in a binary format. Example: + + int intval; + + switch (sqldata->sqlvar[i].sqltype) + { + case SQLINTEGER: + intval = *(int *)sqldata->sqlvar[i].sqldata; + break; + ... + } + + + + + + + sqlind + + + Pointer to the NULL indicator. If returned by DESCRIBE or FETCH then it's always a valid pointer. + If used as input for EXECUTE ... USING sqlda; then NULL-pointer value means + that the value for this field is non-NULL. Otherwise a valid pointer and sqlitype + has to be properly set. Example: + + if (*(int2 *)sqldata->sqlvar[i].sqlind != 0) + printf("value is NULL\n"); + + + + + + + sqlname + + + Name of the field. 0-terminated string. + + + + + + sqlformat + + + Reserved in Informix, value of PQfformat() for the field. + + + + + + sqlitype + + + Type of the NULL indicator data. It's always SQLSMINT when returning data from the server. + When the SQLDA is used for a parametrized query, the data is treated + according to the set type. + + + + + + sqlilen + + + Length of the NULL indicator data. + + + + + + sqlxid + + + Extended type of the field, result of PQftype(). + + + + + + sqltypename + sqltypelen + sqlownerlen + sqlsourcetype + sqlownername + sqlsourceid + sqlflags + sqlreserved + + + Unused. + + + + + + sqlilongdata + + + It equals to sqldata if sqllen is larger than 32KB. + + + + + + + Example: + + EXEC SQL INCLUDE sqlda.h; + + pg_sqlda_t *sqlda; /* This doesn't need to be under embedded DECLARE SECTION */ + + EXEC SQL BEGIN DECLARE SECTION; + char *prep_stmt = "select * from table1"; + int i; + EXEC SQL END DECLARE SECTION; + + ... + + EXEC SQL PREPARE mystmt FROM :prep_stmt; + + EXEC SQL DESCRIBE mystmt INTO sqlda; + + printf("# of fields: %d\n", sqlda->sqld); + for (i = 0; i < sqlda->sqld; i++) + printf("field %d: \"%s\"\n", sqlda->sqlvar[i]->sqlname); + + EXEC SQL DECLARE mycursor CURSOR FOR mystmt; + EXEC SQL OPEN mycursor; + EXEC SQL WHENEVER NOT FOUND GOTO out; + + while (1) + { + EXEC SQL FETCH mycursor USING sqlda; + } + + EXEC SQL CLOSE mycursor; + + free(sqlda); /* The main structure is all to be free(), + * sqlda and sqlda->sqlvar is in one allocated area */ + + For more information, see the sqlda.h header and the + src/interfaces/ecpg/test/compat_informix/sqlda.pgc regression test. + + + + Additional functions *************** *** 3696,3705 **** To use a descriptor area, specify it as the storage target in an ! INTO clause, instead of listing host variables: EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc; --- 4005,4028 ---- To use a descriptor area, specify it as the storage target in an ! INTO clause in a DESCRIBE [OUTPUT] statement: ! ! EXEC SQL BEGIN DECLARE SECTION; ! char *stmt = "SELECT * FROM table1"; ! EXEC SQL END DECLARE SECTION; ! ! EXEC SQL PREPARE stmt1 FROM :stmt; ! EXEC SQL DESCRIBE stmt1 INTO DESCRIPTOR mydesc; ! EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; /* the SQL keyword is optional */ ! ! or a FETCH statement, instead of listing host variables: EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc; + EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc /* the SQL keyword is optional */; + The difference between DESCRIBE and FETCH + statements above is that DESCRIBE on an empty resultset + gives the field metadata, e.g. its name, while FETCH doesn't.