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

Southwell Schuyler wrote: > 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. > Yes... You're just on the wrong path... xpto.nextval is not a column of a dual table. xpto is a SEQUENCE object, and you can think of nextval as it's method of returning the next value. Assuming you're using IDS 9.40+ you'll need to check the SQL syntax guide and look for CREATE SEQUENCE. You shouldn't have any problems since AFAIK it's equivalent to Oracle. dual will have to be a table or view that has just one record. It's structure is completely irrelevant for this query. It's the same as our usual "FROM systables WHERE tabid = 1". By the way, I'm just curious to know if you can solve all the issues with Hibernate and Informix. I had a few problems sometime ago... If I recall correctly I was able to workaround them, but the idea was never implemented. I was working on OpenReports. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...