From 96b8d5842cd6e991a44a01c128b1c3de821e882b Mon Sep 17 00:00:00 2001 From: Michael Meskes Date: Tue, 26 Jan 2010 09:10:11 +0000 Subject: [PATCH] Finally applied patch by Boszormenyi Zoltan to add the new features his patches added to the docs. --- doc/src/sgml/ecpg.sgml | 1073 +++++++++++++++++++++++++++++++--------- 1 file changed, 829 insertions(+), 244 deletions(-) diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml index ebf8e73a84..bb164e23e7 100644 --- a/doc/src/sgml/ecpg.sgml +++ b/doc/src/sgml/ecpg.sgml @@ -1,4 +1,4 @@ - + <application>ECPG</application> - Embedded <acronym>SQL</acronym> in C @@ -2376,6 +2376,478 @@ void PGTYPESdecimal_free(decimal *var); + + Using Descriptor Areas + + + An SQL descriptor area is a more sophisticated method for processing + the result of a SELECT, FETCH or + a DESCRIBE statement. An SQL descriptor area groups + the data of one row of data together with metadata items into one + data structure. The metadata is particularly useful when executing + dynamic SQL statements, where the nature of the result columns might + not be known ahead of time. PostgreSQL provides two ways to use + Descriptor Areas: the named SQL Descriptor Areas and the C-structure + SQLDAs. + + + + Named SQL Descriptor Areas + + + A named SQL descriptor area consists of a header, which contains + information concerning the entire descriptor, and one or more item + descriptor areas, which basically each describe one column in the + result row. + + + + Before you can use an SQL descriptor area, you need to allocate one: + +EXEC SQL ALLOCATE DESCRIPTOR identifier; + + The identifier serves as the variable name of the + descriptor area. The scope of the allocated descriptor is WHAT?. + When you don't need the descriptor anymore, you should deallocate + it: + +EXEC SQL DEALLOCATE DESCRIPTOR identifier; + + + + + 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 SQL DESCRIPTOR mydesc; + + If the resultset is empty, the Descriptor Area will still contain + the metadata from the query, i.e. the field names. + + + + For not yet executed prepared queries, the DESCRIBE + statement can be used to get the metadata of the resultset: + +EXEC SQL BEGIN DECLARE SECTION; +char *sql_stmt = "SELECT * FROM table1"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL PREPARE stmt1 FROM :sql_stmt; +EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; + + + + + Before PostgreSQL 8.5, the SQL keyword was optional, + so using DESCRIPTOR and SQL DESCRIPTOR + produced named SQL Descriptor Areas. Now it is mandatory, omitting + the SQL keyword produces SQLDA Descriptor Areas, + see . + + + + In DESCRIBE and FETCH statements, + the INTO and USING keywords can be + used to similarly: they produce the resultset and the metadata in a + Descriptor Area. + + + + Now how do you get the data out of the descriptor area? You can + think of the descriptor area as a structure with named fields. To + retrieve the value of a field from the header and store it into a + host variable, use the following command: + +EXEC SQL GET DESCRIPTOR name :hostvar = field; + + Currently, there is only one header field defined: + COUNT, which tells how many item + descriptor areas exist (that is, how many columns are contained in + the result). The host variable needs to be of an integer type. To + get a field from the item descriptor area, use the following + command: + +EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field; + + num can be a literal integer or a host + variable containing an integer. Possible fields are: + + + + CARDINALITY (integer) + + + number of rows in the result set + + + + + + DATA + + + actual data item (therefore, the data type of this field + depends on the query) + + + + + + DATETIME_INTERVAL_CODE (integer) + + + ? + + + + + + DATETIME_INTERVAL_PRECISION (integer) + + + not implemented + + + + + + INDICATOR (integer) + + + the indicator (indicating a null value or a value truncation) + + + + + + KEY_MEMBER (integer) + + + not implemented + + + + + + LENGTH (integer) + + + length of the datum in characters + + + + + + NAME (string) + + + name of the column + + + + + + NULLABLE (integer) + + + not implemented + + + + + + OCTET_LENGTH (integer) + + + length of the character representation of the datum in bytes + + + + + + PRECISION (integer) + + + precision (for type numeric) + + + + + + RETURNED_LENGTH (integer) + + + length of the datum in characters + + + + + + RETURNED_OCTET_LENGTH (integer) + + + length of the character representation of the datum in bytes + + + + + + SCALE (integer) + + + scale (for type numeric) + + + + + + TYPE (integer) + + + numeric code of the data type of the column + + + + + + + + In EXECUTE, DECLARE and OPEN + statements, the effect of the INTO and USING + keywords are different. A Descriptor Area can also be manually built to + provide the input parameters for a query or a cursor and + USING SQL DESCRIPTOR name + is the way to pass the input parameters into a parametrized query. The statement + to build a named SQL Descriptor Area is below: + +EXEC SQL SET DESCRIPTOR name VALUE num field = :hostvar; + + + + + PostgreSQL supports retrieving more that one record in one FETCH + statement and storing the data in host variables in this case assumes that the + variable is an array. E.g.: + +EXEC SQL BEGIN DECLARE SECTION; +int id[5]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc; + +EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA; + + + + + + + + SQLDA Descriptor Areas + + + An SQLDA Descriptor Area is a C language structure which can be also used + to get the resultset and the metadata of a query. One structure stores one + record from the resultset. + +EXEC SQL include sqlda.h; +sqlda_t *mysqlda; + +EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda; + + Note that the SQL keyword is omitted. The paragraphs about + the use cases of the INTO and USING + keywords in also apply here with an addition. + In a DESCRIBE statement the DESCRIPTOR + keyword can be completely omitted if the INTO keyword is used: + +EXEC SQL DESCRIBE prepared_statement INTO mysqlda; + + + + + The structure of SQLDA is: + +#define NAMEDATALEN 64 + +struct sqlname +{ + short length; + char data[NAMEDATALEN]; +}; + +struct sqlvar_struct +{ + short sqltype; + short sqllen; + char *sqldata; + short *sqlind; + struct sqlname sqlname; +}; + +struct sqlda_struct +{ + char sqldaid[8]; + long sqldabc; + short sqln; + short sqld; + struct sqlda_struct *desc_next; + struct sqlvar_struct sqlvar[1]; +}; + +typedef struct sqlvar_struct sqlvar_t; +typedef struct sqlda_struct sqlda_t; + + + + + The allocated data for an SQLDA structure is variable as it depends on the + number of fields in a resultset and also depends on the length of the string + data values in a record. The individual fields of the SQLDA + structure are: + + + + sqldaid + + + It contains the "SQLDA " literal string. + + + + + sqldabc + + + It contains the size of the allocated space in bytes. + + + + + sqln + + + It contains the number of input parameters for a parametrized query + case it's passed into OPEN, DECLARE or + EXECUTE statements using the USING + keyword. In case it's used as output of SELECT, + EXECUTE or FETCH statements, + its value is the same as sqld + statement + + + + + sqld + + + It contains the number of fields in a resultset. + + + + + desc_next + + + If the query returns more than one records, multiple linked SQLDA structures + are returned, the first record is stored in the SQLDA returned in the + + + + + sqlvar + + + This is the array of the fields in the resultset. The fields are: + + + + + sqltype + + + It contains the type identifier of the field. For values, + see enum ECPGttype in ecpgtype.h. + + + + + + sqllen + + + It contains the binary length of the field. E.g. 4 bytes for ECPGt_int. + + + + + + sqldata + + + (char *)sqldata points to the data. + + + + + + sqlind + + + (char *)sqlind points to the NULL indicator for data. + 0 means NOT NULL, -1 means NULL. + + + + + + sqlname + + + struct sqlname sqlname contains the name of the field + in a structure: + +struct sqlname +{ + short length; + char data[NAMEDATALEN]; +}; + + + + + length + + + sqlname.length contains the length of the field name. + + + + + data + + + sqlname.data contains the actual field name. + + + + + + + + + + + + + + + + + + + <productname>Informix</productname> compatibility mode @@ -2385,58 +2857,373 @@ void PGTYPESdecimal_free(decimal *var); the dollar sign instead of the EXEC SQL primitive to introduce embedded SQL commands.: - $int j = 3; - $CONNECT TO :dbname; - $CREATE TABLE test(i INT PRIMARY KEY, j INT); - $INSERT INTO test(i, j) VALUES (7, :j); - $COMMIT; + $int j = 3; + $CONNECT TO :dbname; + $CREATE TABLE test(i INT PRIMARY KEY, j INT); + $INSERT INTO test(i, j) VALUES (7, :j); + $COMMIT; + + + + There are two compatiblity modes: INFORMIX, INFORMIX_SE + + + When linking programs that use this compatibility mode, remember to link + against libcompat that is shipped with ecpg. + + + Besides the previously explained syntactic sugar, the Informix compatibility + mode ports some functions for input, output and transformation of data as + well as embedded SQL statements known from E/SQL to ecpg. + + + Informix compatibility mode is closely connected to the pgtypeslib library + of ecpg. pgtypeslib maps SQL data types to data types within the C host + program and most of the additional functions of the Informix compatibility + mode allow you to operate on those C host program types. Note however that + the extent of the compatibility is limited. It does not try to copy Informix + behaviour; it allows you to do more or less the same operations and gives + you functions that have the same name and the same basic behavior but it is + no drop-in replacement if you are using Informix at the moment. Moreover, + some of the data types are different. For example, + PostgreSQL's datetime and interval types do not + know about ranges like for example YEAR TO MINUTE so you won't + find support in ecpg for that either. + + + + 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 + + + This statement closes the current connection. In fact, this is a + synonym for ecpg's DISCONNECT CURRENT.: + + $CLOSE DATABASE; /* close the current connection */ + EXEC SQL CLOSE DATABASE; + + + + + + FREE cursor_name + + + Due to the differences how ECPG works compared to Informix's ESQL/C (i.e. which steps + are purely grammar transformations and which steps rely on the underlying runtime library) + there is no FREE cursor_name statement in ECPG. This is because in ECPG, + DECLARE CURSOR doesn't translate to a function call into + the runtime library that uses to the cursor name. This means 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. + + + + + + + + + Informix-compatible SQLDA Descriptor Areas + + Informix-compatible mode supports a different structure than the one described in + . See below: + +struct sqlvar_compat +{ + short sqltype; + int sqllen; + char *sqldata; + short *sqlind; + char *sqlname; + char *sqlformat; + short sqlitype; + short sqlilen; + char *sqlidata; + int sqlxid; + char *sqltypename; + short sqltypelen; + short sqlownerlen; + short sqlsourcetype; + char *sqlownername; + int sqlsourceid; + + char *sqlilongdata; + int sqlflags; + void *sqlreserved; +}; + +struct sqlda_compat +{ + short sqld; + struct sqlvar_compat *sqlvar; + char desc_name[19]; + short desc_occ; + struct sqlda_compat *desc_next; + void *reserved; +}; + +typedef struct sqlvar_compat sqlvar_t; +typedef struct sqlda_compat sqlda_t; + + + + + The 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 next SQLDA structure if the resultset contains more than one records. + + + + + + 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; + ... + } - - - There are two compatiblity modes: INFORMIX, INFORMIX_SE - - - When linking programs that use this compatibility mode, remember to link - against libcompat that is shipped with ecpg. - - - Besides the previously explained syntactic sugar, the Informix compatibility - mode ports some functions for input, output and transformation of data as - well as embedded SQL statements known from E/SQL to ecpg. - - - Informix compatibility mode is closely connected to the pgtypeslib library - of ecpg. pgtypeslib maps SQL data types to data types within the C host - program and most of the additional functions of the Informix compatibility - mode allow you to operate on those C host program types. Note however that - the extent of the compatibility is limited. It does not try to copy Informix - behaviour; it allows you to do more or less the same operations and gives - you functions that have the same name and the same basic behavior but it is - no drop-in replacement if you are using Informix at the moment. Moreover, - some of the data types are different. For example, - PostgreSQL's datetime and interval types do not - know about ranges like for example YEAR TO MINUTE so you won't - find support in ecpg for that either. - + + + - - Additional embedded SQL statements - - - CLOSE DATABASE + sqlind - This statement closes the current connection. In fact, this is a - synonym for ecpg's DISCONNECT CURRENT.: + 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: - $CLOSE DATABASE; /* close the current connection */ - EXEC SQL CLOSE DATABASE; + 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; + + 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. @@ -3660,208 +4447,6 @@ risnull(CINTTYPE, (char *) &i); - - Using SQL Descriptor Areas - - - An SQL descriptor area is a more sophisticated method for - processing the result of a SELECT or - FETCH statement. An SQL descriptor area groups - the data of one row of data together with metadata items into one - data structure. The metadata is particularly useful when executing - dynamic SQL statements, where the nature of the result columns might - not be known ahead of time. - - - - An SQL descriptor area consists of a header, which contains - information concerning the entire descriptor, and one or more item - descriptor areas, which basically each describe one column in the - result row. - - - - Before you can use an SQL descriptor area, you need to allocate one: - -EXEC SQL ALLOCATE DESCRIPTOR identifier; - - The identifier serves as the variable name of the - descriptor area. The scope of the allocated descriptor is WHAT?. - When you don't need the descriptor anymore, you should deallocate - it: - -EXEC SQL DEALLOCATE DESCRIPTOR identifier; - - - - - 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; - - - - - Now how do you get the data out of the descriptor area? You can - think of the descriptor area as a structure with named fields. To - retrieve the value of a field from the header and store it into a - host variable, use the following command: - -EXEC SQL GET DESCRIPTOR name :hostvar = field; - - Currently, there is only one header field defined: - COUNT, which tells how many item - descriptor areas exist (that is, how many columns are contained in - the result). The host variable needs to be of an integer type. To - get a field from the item descriptor area, use the following - command: - -EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field; - - num can be a literal integer or a host - variable containing an integer. Possible fields are: - - - - CARDINALITY (integer) - - - number of rows in the result set - - - - - - DATA - - - actual data item (therefore, the data type of this field - depends on the query) - - - - - - DATETIME_INTERVAL_CODE (integer) - - - ? - - - - - - DATETIME_INTERVAL_PRECISION (integer) - - - not implemented - - - - - - INDICATOR (integer) - - - the indicator (indicating a null value or a value truncation) - - - - - - KEY_MEMBER (integer) - - - not implemented - - - - - - LENGTH (integer) - - - length of the datum in characters - - - - - - NAME (string) - - - name of the column - - - - - - NULLABLE (integer) - - - not implemented - - - - - - OCTET_LENGTH (integer) - - - length of the character representation of the datum in bytes - - - - - - PRECISION (integer) - - - precision (for type numeric) - - - - - - RETURNED_LENGTH (integer) - - - length of the datum in characters - - - - - - RETURNED_OCTET_LENGTH (integer) - - - length of the character representation of the datum in bytes - - - - - - SCALE (integer) - - - scale (for type numeric) - - - - - - TYPE (integer) - - - numeric code of the data type of the column - - - - - - - Error Handling -- 2.40.0