Thread: Problem getting sequences under 8.02.03.00 driver

Hi all,
    I installed the 8.02.03.00 driver during my installation of Postgres
8.2 onto my machine.  After installation of that driver, the following
code related to sequences would not work:

     Dim cnGetAutoGenID As ADODB.Connection

     ' Get a local handle to the single ADO connection
     Set cnGetAutoGenID = GetPgADOConnection
     Dim sSequenceName As String, sGetIDSQL As String

     sGetIDSQL = "SELECT nextval('""" & sSequenceName & """'::text) as
seqval;"

     Dim rsSeqVal As New ADODB.Recordset

     Set rsSeqVal = cnGetAutoGenID.Execute(sGetIDSQL) ' Execute the SQL
     With rsSeqVal
         ' If there are now rows in this recordset, then there was an
    ' error getting the sequence value
         If .BOF Then
             GoTo Err_GetAutogenID
         Else
             GetAutogenID = .Fields("seqval")
         End If
     End With

Where sSequenceName is holding the name of a sequence in the search_path
visible to the connection.  The problem is that the value returned from
the ADODB Recordset is always 0.  I can get a sequence perfectly well
using pgAdmin, and logs of statements showed that the driver was in fact
executing the SELECT nextval statement.  I can also execute other SELECT
statements just fine using the Connection and Recordset objects.  For
some reason, however, sequence values are always returned as 0.

By rolling back to the 8.02.01.00 driver I was able to solve the
problem, but I'm concerned that this bug will keep me from being able to
move forward in versions of Postgres, and keep me from getting other
fixes.  Does anyone recognize this problem or know anything about the
nature of it?

Thanks,

Eric

Eric E schrieb:
> Hi all,
>     I installed the 8.02.03.00 driver during my installation of
> Postgres 8.2 onto my machine.  After installation of that driver, the
> following code related to sequences would not work:
[...]
> Where sSequenceName is holding the name of a sequence in the
> search_path visible to the connection.  The problem is that the value
> returned from the ADODB Recordset is always 0.  I can get a sequence
> perfectly well using pgAdmin, and logs of statements showed that the
> driver was in fact executing the SELECT nextval statement.  I can also
> execute other SELECT statements just fine using the Connection and
> Recordset objects.  For some reason, however, sequence values are
> always returned as 0.

Maybe it's because you define the resulting type as text?

I've installed latest ODBC V8.0.2.04.01 and there a similar query works.

    Dim objRstADO As ADODB.Recordset

    Set objRstADO = objConnADO.Execute("SELECT nextval('" &
strSequenceName & "')", , adLockReadOnly)
    debug.print Nz(objRstADO(0), 0)


Is the Login-role in the connectionstring the same as you use in pgAdmin?
Perhaps - ok, not very likely - is your ADO connection just not allowed
to see the sequence.
Depending on your function's error handler there could be a hidden error
condition and the function delivers the default value for numeric types = 0.

Andreas wrote:

Many thanks for the reply and the suggestions, Andreas.

> Maybe it's because you define the resulting type as text?

I double-checked and the cast to text is inside the function call, so
the result should be coming back as an integer.  Also, the field in the
recordset is set up as an integer.

> I've installed latest ODBC V8.0.2.04.01 and there a similar query works.
>
>    Dim objRstADO As ADODB.Recordset
>
>    Set objRstADO = objConnADO.Execute("SELECT nextval('" &
> strSequenceName & "')", , adLockReadOnly)
>    debug.print Nz(objRstADO(0), 0)
I guess I'll try 8.02.04.01
>
> Is the Login-role in the connectionstring the same as you use in pgAdmin?
> Perhaps - ok, not very likely - is your ADO connection just not allowed
> to see the sequence.
I thought it might be something like that.
> Depending on your function's error handler there could be a hidden error
> condition and the function delivers the default value for numeric types
> = 0.
>
Possible, though it seems like there's no error being returned by the
ADODB connection - I'll check that, too.

Thanks again,

EE