Thread: Are multiple selects of the same field allowed using CRecordset::GetFieldValue() ?

Hello,

I've recently begun using the v7.01.0010 ODBC driver Hiroshi made
available. I'm issuing the following SQL statement:

"SELECT id, id FROM WaterResource"

Using VC++ I'm executing the following:

++++++++++++++
while (!rsNewRecords.IsEOF())
    {
         rsNewRecords.GetFieldValue("id", vID, SQL_C_SLONG);
        rsNewRecords.GetFieldValue(strFieldName, strValue);
        [...]

        rsNewRecords.MoveNext();
    }
++++++++++++++

When execution hits the second .GetFieldValue() call with strFieldname
being = "id" I get an exception, something that did not happen using the
v7.01.0008 driver

As I understand, when .GetFieldName() is used once to retrieve the value
of a field, a subsequent call to retrieve the exact same field fails.
But in the case of selecting (and thus retrieving) a column twice,
shouldn't two calls be possible/allowed for reading that field's value
twice?

Thanks,
-Kristis

Kristis Makris wrote:
>
> Hello,
>
> I've recently begun using the v7.01.0010 ODBC driver Hiroshi made
> available. I'm issuing the following SQL statement:
>
> "SELECT id, id FROM WaterResource"
>
> Using VC++ I'm executing the following:
>
> ++++++++++++++
> while (!rsNewRecords.IsEOF())
>         {
>                 rsNewRecords.GetFieldValue("id", vID, SQL_C_SLONG);
>                 rsNewRecords.GetFieldValue(strFieldName, strValue);
>                 [...]
>
>                 rsNewRecords.MoveNext();
>         }
> ++++++++++++++
>
> When execution hits the second .GetFieldValue() call with strFieldname
> being = "id" I get an exception, something that did not happen using the
> v7.01.0008 driver
>
> As I understand, when .GetFieldName() is used once to retrieve the value
> of a field, a subsequent call to retrieve the exact same field fails.
> But in the case of selecting (and thus retrieving) a column twice,
> shouldn't two calls be possible/allowed for reading that field's value
> twice?

Hmm both field have the same field name "id" and the driver
couldn't distinguish them. Couldn't you change the query like
  "select id, id as id2 from WaterResourec"
?

regards,
Hiroshi Inoue

>
> Hmm both field have the same field name "id" and the driver
> couldn't distinguish them. Couldn't you change the query like
>   "select id, id as id2 from WaterResourec"
> ?
>

I certainly could, at a great inconvinience of modifying sources of a
huge application that was behaving "normally" so far. I noticed that the
command-line psql client succesfully reports a column's value twice if
instructed to do so:

# select usesysid, usesysid from pg_user;
 usesysid | usesysid
----------+----------
       26 |       26
       27 |       27
       33 |       33
...

How is that implementation different from what I'm trying to achieve
using the .GetField() function? In other languages (e.g. perl using
DBD:Pg) it is possible to get a result of a row, and index in it the
column whose data are to be retrieved. Can't the GetField() be
implemented to function internally in a similar fashion where it marks
which column names it has "visited" already so it moves to the next in
the case of duplicate column names? Or most importantly, why was the
v7.01.0008 driver handling this correctly, while v7.01.0010 isn't? Was a
significant change in an attempt to remain ODBC compliant (or similar)
made that would absolutely, 100% forbid using .GetField() twice on the
same field name? Why is there such a restriction now all of the sudden,
and is that restriction necessary?

Thanks,
-Kristis

Kristis Makris wrote:
>
> >
> > Hmm both field have the same field name "id" and the driver
> > couldn't distinguish them. Couldn't you change the query like
> >   "select id, id as id2 from WaterResourec"
> > ?
> >
>
> I certainly could, at a great inconvinience of modifying sources of a
> huge application that was behaving "normally" so far. I noticed that the
> command-line psql client succesfully reports a column's value twice if
> instructed to do so:
>
> # select usesysid, usesysid from pg_user;
>  usesysid | usesysid
> ----------+----------
>        26 |       26
>        27 |       27
>        33 |       33
> ...
>
> How is that implementation different from what I'm trying to achieve
> using the .GetField() function? In other languages (e.g. perl using
> DBD:Pg) it is possible to get a result of a row, and index in it the
> column whose data are to be retrieved.

The problem is you are using
 GetFieldValue(LPCTSTR lpszName, CDBVariant& varValue, short ..
Probably you could get your expected result if you use
 GetFieldValue(short nIndex, CDBVariant& varValue, ..

GetFieldValue() calls SQLGetData() internally. SQLGetData()
uses the index not the name to specifiy the target column.
Using the first style of GetFieldValue(), it searches
the index using the field name first and calls SQLGetData()
using the index. So SQLGetData() has nothing to do with it
and I was wrong at this point in my previous mail.

> Can't the GetField() be
> implemented to function internally in a similar fashion where it marks
> which column names it has "visited" already so it moves to the next in
> the case of duplicate column names? Or most importantly, why was the
> v7.01.0008 driver handling this correctly, while v7.01.0010 isn't? Was a
> significant change in an attempt to remain ODBC compliant (or similar)
> made that would absolutely, 100% forbid using .GetField() twice on the
> same field name?

I changed SQLGetData() to return SQL_NO_DATA if it is
called more than one time in a row for a column containing
fixed-length data because it's a spec of SQLGetData() and
fixes a bug reported by Mika Mantyla. Note that a column
is identified by its index not the name.

I have to think how to deal with your trouble but have
little time now unfortunately.
Please wait for some time.

Anyway I recommend you to use the second style GetFieldValue
or distinguish the same field using alias in future
programming.

regards,
Hiroshi Inoue