Can we query SQL column value by name instead of column number?

[ATC_ATC]

With the following codes, it works fine:

   hstmt = DBActivateSQL (hdbc, "SELECT ValDB FROM Account"); 
   if (DBFetchNext (hstmt) != DB_EOF)
   {           
         DBGetColDouble(hstmt, 1, &ReadValue);
   }

However, I had to use colunm 1 (ValDB) to get the value into ReadValue, is there any command code we can just say ... ValDB ... so that we do not have learn column oreder by heart? 

Thank to any help

[Alex D]

The column number you specify is not the column number in the table, it's the column number in the result set from your select statement. For example, if you execute "SELECT ValDB, ValTwoDB FROM Account" (and have both of those fields available), column 1 will be ValDB and column 2 will be ValTwoDB. In the case where you are only requesting one field, the column will always be 1.

Hope this helps,

-alex

[ATC_ATC]

I did know what you explained, but in this case we have to mention 1 (for first field values or else), I prefer use term by name to get value from that column1 (ie: in VC++, VB.net they do not have to use coulumn number to access the field value. They use Table.m_ValDB instead

[Alex D]

In that case, I would recommend that you use the binding functions to access the data. If you wanted to emulate a struct-type data access, you could still do this.

Instead of using DBGetColDouble(), use DBBindColDouble() and bind the fields to struct fields.

ex:

struct DBTable
{
    double m_ValDB;
    // more fields

    int m_ValDB_status;
    // more field status variables
};

int SelectAndBind(struct DBTable *table)
{
    int hstmt = DBActivateSQL (hdbc, "SELECT ValDB FROM Account");
    DBBindColDouble (hstmt, 1, &table->m_ValDB, &table->m_ValDB_status);
    // bind other fields as needed
    return hstmt;
}

void ProcessResults(void)
{
    struct DBTable table;
    int hstmt = SelectAndBind(&table);
    while (DBFetchNext(hstmt) == 0)
    {
        if (table.m_valDB_status >= 0)
        {
            // process table.m_ValDB
        }
    }
}
You still have to maintain the column -> SQL mapping in one place, but everywhere else you can use the named variable instead.

Regards,

-alex

Message Edited by Alex D on 12-19-2006 10:07 AM