Oracle: Selecting values from sequences in XoJo

Databases
1

It’s late, my eyes are burning, and one of the simplest things in the world seems to be eluding me. Namely, selecting the nextval form an Oracle Sequence.

First I tried it with a prepared statement, which absolutely cratered the application every time I tried a select. Okay, not to worry that much, probably me, so just use a dynamic SQL statement instead, and a small recordset to retrieve the result. So I thought… in the example below please assume the database is connected, online, and operating properly. SQLPLus works fine, as does embedded SQL in C and Cobol from the same machine, which is an iMac running Yosemite.

[code]Dim sqlString as String
Dim keyVal As Integer
Dim rs as RecordSet

sqlString = “select AP00ID.NEXTVAL from dual;”
rs = App.mDB.SQLSelect(sqlString)
if rs <> Nil then
keyVal = rs.IdxField(0).IntegerValue
MsgBox(“keyVal = [” + Str(keyVal) + “]”)
else
MsgBox(“Dang! The query on the sequence didn’t work…”)
end if[/code]

The recordset (rs) is always and invariably Nil after execution. This is moderately annoying, since it should have returned the next sequence value.

Indeed, in SQLPlus it works just fine.

[code]Pauls-iMac:~ Paul$ sqlplus rsxx/xxxxxxx@paul/XE
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 14 23:08:57 2015
Copyright © 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select AP00ID.NEXTVAL from dual;
NEXTVAL

 20006

SQL>[/code]

And Indeed, it also works fine from PROC and PROCOBOL. C example below:

/* 
 * File:   apm50.c
 * Author: Paul
 *
 * Created on May 29, 2009, 4:01 PM
 * -Workfile for testing SQL processes
 * -   requires: Pro*C precompiler and sql libraries
 * -   links and runs well with either 64 bit or 32 bit libraries.
 * 
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
#include "apm.h"

int main() {
    int c;
    int c1;
    
    EXEC SQL BEGIN DECLARE SECTION;
    
    char    conn_string [80];
    int     counter;
    int     nextval, currval;
   
    EXEC SQL END DECLARE SECTION;
    
    
    strcpy (conn_string, "rsxx/xxxxxxx@rsdb.rsenpubs.com/XE:POOLED");
    EXEC SQL CONNECT :conn_string;
    
    EXEC SQL SELECT AP00ID.NEXTVAL into :nextval FROM dual;
    
    printf ("NextVal = [%d]\
\
", nextval);
    
    EXEC SQL COMMIT WORK RELEASE;
    
    return 0;
}

[code]Pauls-iMac:APM Paul$ ./apmwork
NextVal = [20009]

Pauls-iMac:APM Paul$ ./apmwork
NextVal = [20010]
[/code]

2

Its not something silly like that trailing semicolon causing Oracle to perform the first statement, discard the result, then execute an empty second statement?

3

Easy enough to try- and that is, of course, the root of the problem. Too much C I suppose, I must have looked at that a 100 times. The semi looked right there to my old eyes… :slight_smile:

It does seem odd that the semicolon caused a problem though, it is the terminator for a SQL statement. Of course, XoJo treats semis like compile time errors, so it makes sense from that perspective. I just wish it had thrown an error or something.

Thanks
-Paul

4

I wonder what it would have done with

“select AP00ID.NEXTVAL from dual;select AP00ID.NEXTVAL from dual”

5

It throws an Oracle Error -
“Dang! The query on the sequence didn’t work…ORA-00911: invalid character?”

Which isn’t all that informative. Something is getting mistranslated with the semi-colon I suppose?

Interestingly, the double statement without the semi, gets you:

“Dang! The query on the sequence didn’t work…ORA-00933: SQL command not properly ended?”

Which I did expect, but then, I would have though the two statements separated by a semi would work too.