Re: Make MySQL syntax work in Informix
Hello Schuyler,
Looking at the below SQL, I presume that you are trying to get th next value in a sequence of values?
If this is the case, then you need to create the hibernate_sequence table as a sequence, or, presuming the table has more columns than
just sequence related columns, create a sequence for that table.
>>-CREATE SEQUENCE--+----------+--sequence---------------------->
'-owner--.-'
>--+--------------------------------------+--------------------><
| .-----------------------------. |
| V .-BY-. | (1) |
'---+-INCREMENT--+----+--step-+-+------'
| .-WITH-. |
+-START--+------+--origin-+
| .-NOMAXVALUE----. |
+-+-MAXVALUE--max-+-------+
| .-NOMINVALUE----. |
+-+-MINVALUE--min-+-------+
| .-NOCYCLE-. |
+-+-CYCLE---+-------------+
+-+-CACHE--size-+---------+
| '-NOCACHE-----' |
| .-ORDER---. |
'-+-NOORDER-+-------------'
Honestly though you could just run
create sequence hibernate_sequence;and then
SELECT hibernate_sequence.nextval FROM dual;
should work, presuming you have the dual stuff working.
Now my old sql like this
select hibernate_sequence.nextval from table(set{1});
returns:
3 (of course I had already called it two times, so the sequence value had just incremented)
I believe cheetah allows dual, but sequences have been around for many years now.
----- Original Message ----
From: Southwell Schuyler <southwel@mcao.maricopa.gov>
To: informix-list@iiug.org
Sent: Thursday, July 5, 2007 9:59:26 AM
Subject: Make MySQL syntax work in Informix
I have a semi-canned package (JasperSoft JasperReports / JasperServer)
that I am attempting to make use Informix as it's report repository, in
lieu of installing an Oracle / MySQL instance.
I have the schema (successfully ?) converted (I won't know for certain
until I can get the JasperServer product (installed in Tomcat) to come
up.
It is failing on the following MySQL/Oracle syntax:
-----
SELECT hibernate_sequence.nextval FROM dual;
14:45:20,272 WARN JDBCExceptionReporter,main:77 - SQL Error: -217,
SQLState: IX000
14:45:20,300 ERROR JDBCExceptionReporter,main:78 - Column (nextval) not
found in any table in the query (or SLV is undefined).
-----
I have implemented several variants of the 'dual' table (everything from
Jonathan Leffler's 'dual' view to an actual table), and have also
created the hibernate_sequence table. Both tables have a 'nextval'
column.
If I do this without the nextval column in the table, then I get an
error that states column hibernate_sequence not in any table...
---
Is there ANY way to write a VIEW / SYNONYM / SELECT TRIGGER (this is
relatively low volume) to FAKE this query into executing? I really
would like to avoid implementing the Oracle / MySQL instance if at all
possible.
Thanks,
Schuyler L. Southwell
Software Engineer, Consulting
Maricopa County Attorney's Office
tel: 602 506 8107
southwel@mcao.maricopa.gov
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list