Oracle: Selecting values from sequences in XoJo

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) + “]”)
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 Production on Sun Jun 14 23:08:57 2015
Copyright © 1982, 2014, Oracle. All rights reserved.

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

SQL> select AP00ID.NEXTVAL from dual;



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;
    char    conn_string [80];
    int     counter;
    int     nextval, currval;
    strcpy (conn_string, "rsxx/");
    EXEC SQL CONNECT :conn_string;
    EXEC SQL SELECT AP00ID.NEXTVAL into :nextval FROM dual;
    printf ("NextVal = [%d]\
", nextval);
    return 0;

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

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

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

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.


I wonder what it would have done with

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

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.