"Create sequence" fails through ODBC driver

[christophe.benoit]

To execute an SQL command, my C++ code calls:
SQLPrepare(**handle**, **command text**, SQL _ NTS);
then SQLExecute.
SQLPrepare does fail (returns SQL_ERROR) on the following command:
"CREATE SEQUENCE autoinc_test_id_seq"

with the following error:
ODBC error code: 42000
Native Error code: 2500
Error message:"Syntax error on line 1. Last symbol read was: 'CREATE SEQUENCE'"

whereas with other SQL commands it works just fine (create table, create index, select..) and using another sql console tool I manage to execute the same command without any problem.


Environment information:
Database Version: INGRES Release 9.04 (ingres2006-9.0.4-105-gpl-win-x86)
Client Version: caiiod35.dll 03.50.00.62

ODBC Version: 3.525
OS: Windows XP

Odbc driver configuration:
VNode: (LOCAL)
Type: INGRES
Database: imadb

and the default options after a fresh install.

It looks like I am not the only one with the same symptoms: [Info-Ingres] Odd error?

Any idea?

[christophe.benoit]

I updated to latest version of Ingres (9.2.0.143 with caiiod35.dll v3.50.920) and I still get this error.

[loera01]

Hi Christophe,

I'm checking with the database server group as to whether this is considered a bug in the DBMS server or the ODBC. I tried ODBC, SQL script, and embedded SQL versions of CREATE SEQUENCE, and none of the allowed CREATE SEQUENCE to be prepared, even though I could find no documentation that specifically says you can't prepare a CREATE SEQUENCE query.

It does seem reasonable that one wouldn't want to prepare a CREATE SEQUENCE query, since preparing a statement implies that it is to be executed multiple times, which doesn't sound like a good idea in this case. However, other CREATE queries can be prepared, so maybe this is an oversight in the DBMS server parsing syntax.

I will let you know what the database folks say. If you have a support contract with Ingres, I recommend opening a Service Desk issue for this so that you can receive a patch.

Regards,
Ralph Loen

[hanal04]

This was logged as Bug 121411 and has already been fixed by Mike Cooper. Change are to the following files:

ingres!main!generic!back!psf!psl pslsgram.yi
ingres!main!generic!back!psf!pst pstdsql.c

and should be available in the svn repository shortly (if not already).

Regards

Alex

[christophe.benoit]

I have now the same kind of problem with CREATE PROCEDURE and DROP PROCEDURE statements when the statements are prepared in ODBC:
"Syntax error on line 1. Last symbol read was: 'PROCEDURE'."

I use Ingres 9.2.0 with caiiod35.dll v3.50.920 with patch 13214 (for bug 121411 above).

[loera01]

Hi Christophe,

Sorry for not getting to you sooner. I normally scan the "Database Drivers" section of the Forum, and thought this thread was resolved. For future reference, I recommend the "Database Drivers" forum for ODBC questions.

The error is actually coming from the DBMS server, because Ingres currently does not allow CREATE PROCEDURE to be prepared.

This case is a little different from the others because some queries can be prepared in Ingres and others can't. If an ODBC application attempts to prepare a statement using a keyword that unilaterally cannot be prepared in Ingres, such as SET, the driver pretends to prepare the query, but actually executes it directly under the covers.

The driver could look also for the CREATE PROCEDURE syntax, instead of just looking for CREATE, and then pretend to prepare and execute the CREATE PROCEDURE query.

Would this proposal help your situation? If you have direct control over your ODBC code, the easiest thig to do is just not prepare the CREATE PROCEDURE query.

Please let me know what you think.

Regards, Ralph