Column names in dynamically generated datawindows

Question

When I dynamically create a datastore using SyntaxFromSQL (in order to generate datastore source code, based on a SQL SELECT statement), with syntax like this

string ERRORS, sql_syntax, dwsyntax_str, presentation_str
dwsyntax_str = trans_object.SyntaxFromSQL ( sql_syntax, presentation_str, ERRORS)
ds_1.Create( dwsyntax_str, ERRORS)

how can I check the names of the generated columns of datastore ds_1? I remind you that in the case of a select statement that joins two or more tables, the resulting column names may be preceded by the relevant table name e.g. instead of getting the column name field_id I may get a column name like: my_table_field_id. This causes problems when later on I provide the column name (field_id) as an argument of a GetItem function, while the relevant datastore has named the column my_table_field_id instead.

To make things worse, I found out that one of the reasons why I was getting different column definitions (preceded by table name) was the fact that the user's login had been assigned the sa role !?!?!

Answer 1

Generally, in Describe() calls, the column number can be used in place of the column name, so you can do something like:

string ls_FirstColumnName, ls_SecondColumnName

ls_FirstColumnName = dw_1.Describe ("#1.Name")
ls_SecondColumnName = dw_1.Describe ("#2.Name")
MessageBox ("Column Names", ls_FirstColumnName + "~r~n" + ls_SecondColumnName)

Cache the values, or just use the column numbers in the future. Most DataWindow/DataStore functions referencing columns have overloads that allow an integer to be used as a column number instead of a string as the column name.

Good luck,

Terry.

Answer 2

I had the same problem with SQL Anywhere 11 and i found several causes.

1) If you rebuild a database using dbUnload then the value of systab.creator may be incremented. In this case, you can get different column names

2) When the user connected to the database is the owner of the tables (the user who created the tables), i noted that i got *my_table_field_id* when the pb catalog tables (pbcatcol, pbcattab, ...) had already been created in the database.