Oninit cdi / Archive
Talk to an expert →

Getting table name from a result set with esqlc

Posted in 2008

Topics: Connectivity: ESQL/C, 4GL & Embedded SQL, Platform-Specific Issues, Versions, Editions & End-of-Life

Mike — — source: Usenet: comp.databases.informix

I'm using IDS 9.3, CSDK 3.0 on AIX 5.3 ML 0. The following code is what's used to get the table name from a result set with esql/c. if (SQLColAttribute (stmt_res->hstmt, colno + 1, SQL_DESC_BASE_TABLE_NAME, (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, (SQLPOINTER) & numericAttribute) != SQL_ERROR) { /* * Most of the time, this seems to return a null string. only * return this if we have something real. */ if (length > 0) { add_assoc_stringl(return_value, "table", attribute_buffer, length, 1); } } The problem is that (as the comment says), this returns a blank string most of the time. Does anyone have a better way to do this that will work all the time? It would add about 10 years to my life. Here is the entire function (it's the php pdo_informix driver. I didn't write it, but if I can get this working, I'll be in good shape). Thanks! Mike. /* * Return all of the meta data information that makes sense for * this database driver. */ static int informix_stmt_get_column_meta( pdo_stmt_t *stmt, long colno, zval *return_value TSRMLS_DC) { stmt_handle *stmt_res = NULL; column_data *col_res = NULL; #define ATTRIBUTEBUFFERSIZE 256 char attribute_buffer[ATTRIBUTEBUFFERSIZE]; SQLSMALLINT length; SQLINTEGER numericAttribute; zval *flags; if (colno >= stmt->column_count) { RAISE_INFORMIX_STMT_ERROR("HY097", "getColumnMeta", "Column number out of range"); return FAILURE; } stmt_res = (stmt_handle *) stmt->driver_data; /* access our look aside data */ col_res = &stmt_res->columns[colno]; /* make sure the return value is initialized as an array. */ array_init(return_value); add_assoc_long(return_value, "scale", col_res->scale); /* see if we can retrieve the table name */ if (SQLColAttribute (stmt_res->hstmt, colno + 1, SQL_DESC_BASE_TABLE_NAME, (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, (SQLPOINTER) & numericAttribute) != SQL_ERROR) { /* * Most of the time, this seems to return a null string. only * return this if we have something real. */ if (length > 0) { add_assoc_stringl(return_value, "table", attribute_buffer, length, 1); } } /* see if we can retrieve the type name */ if (SQLColAttribute(stmt_res->hstmt, colno + 1, SQL_DESC_TYPE_NAME, (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, (SQLPOINTER) & numericAttribute) != SQL_ERROR) { add_assoc_stringl(return_value, "native_type", attribute_buffer, length, 1); } MAKE_STD_ZVAL(flags); array_init(flags); add_assoc_bool(flags, "not_null", !col_res->nullable); /* see if we can retrieve the unsigned attribute */ if (SQLColAttribute(stmt_res->hstmt, colno + 1, SQL_DESC_UNSIGNED, (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, (SQLPOINTER) & numericAttribute) != SQL_ERROR) { add_assoc_bool(flags, "unsigned", numericAttribute == SQL_TRUE); } /* see if we can retrieve the autoincrement attribute */ if (SQLColAttribute (stmt_res->hstmt, colno + 1, SQL_DESC_AUTO_UNIQUE_VALUE, (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, (SQLPOINTER) & numericAttribute) != SQL_ERROR) { add_assoc_bool(flags, "auto_increment", numericAttribute == SQL_TRUE); } /* add the flags to the result bundle. */ add_assoc_zval(return_value, "flags", flags); return SUCCESS; }

↪ replying to Mike
Art S. Kagel (Oninit LLC) — — source: Usenet: comp.databases.informix

Mike wrote: Mike, DESCRIBE the statement either into an SQL DESCRIPTOR area or into an sqlda structure. The columns name, or the field alias assigned in the projection list, or an appropriate generic name like "EXPRESSION", will be found in (using hte sqlda structure): sqlda.sqlvar[fieldno].sqlname See the ESQL/C Manual chapters 14, 15, 16, & 17. Art S. Kagel Oninit > I'm using IDS 9.3, CSDK 3.0 on AIX 5.3 ML 0. > > The following code is what's used to get the table name from a result > set with esql/c. > > if (SQLColAttribute (stmt_res->hstmt, colno + 1, > SQL_DESC_BASE_TABLE_NAME, > (SQLPOINTER) attribute_buffer, > ATTRIBUTEBUFFERSIZE, &length, > (SQLPOINTER) & numericAttribute) != SQL_ERROR) > { > /* > * Most of the time, this seems to return a null > string. only > * return this if we have something real. > */ > if (length > 0) { > add_assoc_stringl(return_value, "table", > attribute_buffer, length, 1); > } > } > > The problem is that (as the comment says), this returns a blank string > most of the time. > > Does anyone have a better way to do this that will work all the time? > It would add about 10 years to my life. > > Here is the entire function (it's the php pdo_informix driver. I > didn't write it, but if I can get this working, I'll be in good > shape). > > Thanks! > Mike. > > /* > * Return all of the meta data information that makes sense for > * this database driver. > */ > static int informix_stmt_get_column_meta( > pdo_stmt_t *stmt, > long colno, > zval *return_value > TSRMLS_DC) > { > stmt_handle *stmt_res = NULL; > column_data *col_res = NULL; > > #define ATTRIBUTEBUFFERSIZE 256 > char attribute_buffer[ATTRIBUTEBUFFERSIZE]; > SQLSMALLINT length; > SQLINTEGER numericAttribute; > zval *flags; > > if (colno >= stmt->column_count) { > RAISE_INFORMIX_STMT_ERROR("HY097", "getColumnMeta", > "Column number out of range"); > return FAILURE; > } > > stmt_res = (stmt_handle *) stmt->driver_data; > /* access our look aside data */ > col_res = &stmt_res->columns[colno]; > > /* make sure the return value is initialized as an array. */ > array_init(return_value); > add_assoc_long(return_value, "scale", col_res->scale); > > /* see if we can retrieve the table name */ > if (SQLColAttribute (stmt_res->hstmt, colno + 1, > SQL_DESC_BASE_TABLE_NAME, > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > /* > * Most of the time, this seems to return a null string. only > * return this if we have something real. > */ > if (length > 0) { > add_assoc_stringl(return_value, "table", attribute_buffer, length, > 1); > } > } > /* see if we can retrieve the type name */ > if (SQLColAttribute(stmt_res->hstmt, colno + 1, SQL_DESC_TYPE_NAME, > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > add_assoc_stringl(return_value, "native_type", attribute_buffer, > length, 1); > } > > MAKE_STD_ZVAL(flags); > array_init(flags); > add_assoc_bool(flags, "not_null", !col_res->nullable); > > /* see if we can retrieve the unsigned attribute */ > if (SQLColAttribute(stmt_res->hstmt, colno + 1, SQL_DESC_UNSIGNED, > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > add_assoc_bool(flags, "unsigned", numericAttribute == SQL_TRUE); > } > > /* see if we can retrieve the autoincrement attribute */ > if (SQLColAttribute (stmt_res->hstmt, colno + 1, > SQL_DESC_AUTO_UNIQUE_VALUE, > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > add_assoc_bool(flags, "auto_increment", > numericAttribute == SQL_TRUE); > } > > > /* add the flags to the result bundle. */ > add_assoc_zval(return_value, "flags", flags); > > return SUCCESS; > } > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== > > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

↪ replying to Art S. Kagel (Oninit LLC)
Mike — — source: Usenet: comp.databases.informix

On Jan 26, 10:28 pm, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> wrote: > Mike wrote: > > Mike, > > DESCRIBE the statement either into an SQL DESCRIPTOR area or into an > sqlda structure. The columns name, or the field alias assigned in the > projection list, or an appropriate generic name like "EXPRESSION", will > be found in (using hte sqlda structure): > > sqlda.sqlvar[fieldno].sqlname > > See the ESQL/C Manual chapters 14, 15, 16, & 17. > > Art S. Kagel > Oninit > > > > > I'm using IDS 9.3, CSDK 3.0 on AIX 5.3 ML 0. > > > The following code is what's used to get the table name from a result > > set with esql/c. > > > if (SQLColAttribute (stmt_res->hstmt, colno + 1, > > SQL_DESC_BASE_TABLE_NAME, > > (SQLPOINTER) attribute_buffer, > > ATTRIBUTEBUFFERSIZE, &length, > > (SQLPOINTER) & numericAttribute) != SQL_ERROR) > > { > > /* > > * Most of the time, this seems to return a null > > string. only > > * return this if we have something real. > > */ > > if (length > 0) { > > add_assoc_stringl(return_value, "table", > > attribute_buffer, length, 1); > > } > > } > > > The problem is that (as the comment says), this returns a blank string > > most of the time. > > > Does anyone have a better way to do this that will work all the time? > > It would add about 10 years to my life. > > > Here is the entire function (it's the php pdo_informix driver. I > > didn't write it, but if I can get this working, I'll be in good > > shape). > > > Thanks! > > Mike. > > > /* > > * Return all of the meta data information that makes sense for > > * this database driver. > > */ > > static int informix_stmt_get_column_meta( > > pdo_stmt_t *stmt, > > long colno, > > zval *return_value > > TSRMLS_DC) > > { > > stmt_handle *stmt_res = NULL; > > column_data *col_res = NULL; > > > #define ATTRIBUTEBUFFERSIZE 256 > > char attribute_buffer[ATTRIBUTEBUFFERSIZE]; > > SQLSMALLINT length; > > SQLINTEGER numericAttribute; > > zval *flags; > > > if (colno >= stmt->column_count) { > > RAISE_INFORMIX_STMT_ERROR("HY097", "getColumnMeta", > > "Column number out of range"); > > return FAILURE; > > } > > > stmt_res = (stmt_handle *) stmt->driver_data; > > /* access our look aside data */ > > col_res = &stmt_res->columns[colno]; > > > /* make sure the return value is initialized as an array. */ > > array_init(return_value); > > add_assoc_long(return_value, "scale", col_res->scale); > > > /* see if we can retrieve the table name */ > > if (SQLColAttribute (stmt_res->hstmt, colno + 1, > > SQL_DESC_BASE_TABLE_NAME, > > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > > /* > > * Most of the time, this seems to return a null string. only > > * return this if we have something real. > > */ > > if (length > 0) { > > add_assoc_stringl(return_value, "table", attribute_buffer, length, > > 1); > > } > > } > > /* see if we can retrieve the type name */ > > if (SQLColAttribute(stmt_res->hstmt, colno + 1, SQL_DESC_TYPE_NAME, > > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > > add_assoc_stringl(return_value, "native_type", attribute_buffer, > > length, 1); > > } > > > MAKE_STD_ZVAL(flags); > > array_init(flags); > > add_assoc_bool(flags, "not_null", !col_res->nullable); > > > /* see if we can retrieve the unsigned attribute */ > > if (SQLColAttribute(stmt_res->hstmt, colno + 1, SQL_DESC_UNSIGNED, > > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > > add_assoc_bool(flags, "unsigned", numericAttribute == SQL_TRUE); > > } > > > /* see if we can retrieve the autoincrement attribute */ > > if (SQLColAttribute (stmt_res->hstmt, colno + 1, > > SQL_DESC_AUTO_UNIQUE_VALUE, > > (SQLPOINTER) attribute_buffer, ATTRIBUTEBUFFERSIZE, &length, > > (SQLPOINTER) & numericAttribute) != SQL_ERROR) { > > add_assoc_bool(flags, "auto_increment", > > numericAttribute == SQL_TRUE); > > } > > > /* add the flags to the result bundle. */ > > add_assoc_zval(return_value, "flags", flags); > > > return SUCCESS; > > } > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > > =========================================================================================== > > Please access the attached hyperlink for an important electronic communications disclaimer: > > >http://www.oninit.com/home/disclaimer.php > > > =========================================================================================== > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== The table for each column in the result set. So, if I run a select and join in multiple tables, I want to get the table name as well as the column name. As a real life example, I have a facility table, and a department table, and when I run a select against the person table (who belongs to both a facility and a department), I join in the facility and department tables (both of which have a column named 'description). For that joined result set, I'd like to be able to get the table name and column name for each column, rather than just the column name. If I can only get the column name, I can't tell which 'description' it is. Hope that makes sense.

Glossary