Re: Getting table name from a result set with esqlc

On Jan 28, 8:46 am, Mike <mike.baran...@gmail.com> wrote: > 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. Just in case anyone has found this on a web search or something: You, like me, are screwed. You cannot do this without some hack that aliases the table names into the column names in the select, and so on. I'm very disappointed.