RE: Make MySQL syntax work in Informix

To all who suggested the CREATE SEQUENCE ... Thanks, that solved THAT problem. Works like a charm. (We've been using Informix since 1993 and I learned something new ... that's always nice.) >> Fernando Nunes >> By the way, I'm just curious to know if you can solve all the issues with Hibernate and Informix. I will let everyone know if I have any success -- we'll see how much time I can burn on this before I abandon all hope :-) FYI: I now receive a syntax error on the query below: Specifically, I believe, the following syntax: where this_.id=this_1_.id(+) Ah well, onward.... Complete query with syntax error: select this_.id as id2_0_, this_.version as version2_0_, this_.name as name2_0_, this_.parent_folder as parent4_2_0_, this_.childrenFolderas children5_2_0_, this_.label as label2_0_, this_.description as descript7_2_0_, this_.creation_date as creation8_2_0_, this_1_.options_name as options2_26_0_, this_1_.report_id as report3_26_0_, this_3_.type as type30_0_, this_3_.maxLength as maxLength30_0_, this_3_.decimals as decimals30_0_, this_3_.regularExpr as regularE5_30_0_, this_3_.minValue as minValue30_0_, this_3_.maxValue as maxValue30_0_, this_3_.strictMin as strictMin30_0_, this_3_.strictMax as strictMax30_0_, this_4_.data as data31_0_, this_4_.file_type as file3_31_0_, this_5_.jndiName as jndiName32_0_, this_5_.timezone as timezone32_0_, this_6_.data as data33_0_, this_6_.file_type as file3_33_0_, this_6_.reference as reference33_0_, this_7_.serviceClass as serviceC2_34_0_, this_8_.reportDataSource as reportDa2_36_0_, this_8_.query as query36_0_, this_8_.mainReport as mainReport36_0_, this_8_.controlrenderer as controlr5_36_0_, this_8_.reportrenderer as reportre6_36_0_, this_8_.promptcontrols as promptco7_36_0_, this_8_.controlslayout as controls8_36_0_, this_9_.adhocStateId as adhocSta2_39_0_, this_10_.beanName as beanName40_0_, this_10_.beanMethod as beanMethod40_0_, this_11_.driver as driver41_0_, this_11_.password as password41_0_, this_11_.connectionUrl as connecti4_41_0_, this_11_.username as username41_0_, this_11_.timezone as timezone41_0_, this_12_.catalog as catalog42_0_, this_12_.mondrianConnection as mondrian3_42_0_, this_14_.reportDataSource as reportDa2_44_0_, this_14_.mondrianSchema as mondrian3_44_0_, this_15_.olapClientConnection as olapClie2_45_0_, this_15_.mdx_query as mdx3_45_0_, this_15_.view_options as view4_45_0_, this_16_.catalog as catalog46_0_, this_16_.username as username46_0_, this_16_.password as password46_0_, this_16_.datasource as datasource46_0_, this_16_.uri as uri46_0_, this_17_.dataSource as dataSource47_0_, this_17_.query_language as query3_47_0_, this_17_.sql_query as sql4_47_0_, this_18_.type as type48_0_, this_18_.mandatory as mandatory48_0_, this_18_.readOnly as readOnly48_0_, this_18_.visible as visible48_0_, this_18_.data_type as data6_48_0_, this_18_.list_of_values as list7_48_0_, this_18_.list_query as list8_48_0_, this_18_.query_value_column as query9_48_0_, this_18_.defaultValue as default10_48_0_, decode(this_.id, this_9_.id, 9, this_14_.id, 14, this_16_.id, 16, this_1_.id, 1, this_2_.id, 2, this_3_.id, 3, this_4_.id, 4, this_5_.id, 5, this_6_.id, 6, this_7_.id, 7, this_8_.id, 8, this_10_.id, 10, this_11_.id, 11, this_12_.id, 12, this_13_.id, 13, this_15_.id, 15, this_17_.id, 17, this_18_.id, 18, 0) as clazz_0_ from JIResource this_, JIReportOptions this_1_, JIListOfValues this_2_, JIDataType this_3_, JIContentResource this_4_, JIJNDIJdbcDatasource this_5_, JIFileResource this_6_, JICustomDatasource this_7_, JIReportUnit this_8_, JIAdhocReportUnit this_9_, JIBeanDatasource this_10_, JIJdbcDatasource this_11_, JIMondrianXMLADefinition this_12_, JIOlapClientConnection this_13_, JIMondrianConnection this_14_, JIOlapUnit this_15_, JIXMLAConnection this_16_, JIQuery this_17_, JIInputControl this_18_ where this_.id=this_1_.id(+) and this_.id=this_2_.id(+) and this_.id=this_3_.id(+) and this_.id=this_4_.id(+) and this_.id=this_5_.id(+) and this_.id=this_6_.id(+) and this_.id=this_7_.id(+) and this_.id=this_8_.id(+) and this_.id=this_9_.id(+) and this_.id=this_10_.id(+) and this_.id=this_11_.id(+) and this_.id=this_12_.id(+) and this_.id=this_13_.id(+) and this_.id=this_14_.id(+) and this_.id=this_15_.id(+) and this_.id=this_16_.id(+) and this_.id=this_17_.id(+) and this_.id=this_18_.id(+) and (this_.name=? and this_.parent_folder=?) (substituted "X" and 8 for ? -- doesn't matter, as there are no rows at present) Schuyler L. Southwell Software Engineer, Consulting Maricopa County Attorney's Office tel: 602 506 8107 southwel@mcao.maricopa.gov http://www.stopduiaz.com/ -----Original Message----- From: Jonathan Leffler [mailto:jleffler.iiug@gmail.com] Sent: Thursday, July 05, 2007 11:23 AM To: Southwell Schuyler Subject: Re: Make MySQL syntax work in Informix On 7/5/07, Southwell Schuyler <southwel@mcao.maricopa.gov> 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;> You'll need to create a sequence called hibernate_sequence as well as have the dual table in place. > 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. Of course, you also need a version of IDS that supports sequences - which were added in either 9.40 or 10.00 (and I cannot now remember which and I'm feeling too lazy to look it up). Look in the manual for CREATE SEQUENCE (SQL Syntax). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily rea

Southwell Schuyler wrote: > To all who suggested the CREATE SEQUENCE ... > > Thanks, that solved THAT problem. Works like a charm. (We've been > using Informix since 1993 and I learned something new ... that's always > nice.) > >>> Fernando Nunes >>> By the way, I'm just curious to know if you can solve all the issues > with Hibernate and Informix. > > I will let everyone know if I have any success -- we'll see how much > time I can burn on this before I abandon all hope :-) > > FYI: I now receive a syntax error on the query below: > > Specifically, I believe, the following syntax: > > where this_.id=this_1_.id(+) > > Ah well, onward.... > > > > Complete query with syntax error: > > select this_.id as id2_0_, this_.version as version2_0_, this_.name as > name2_0_, this_.parent_folder as parent4_2_0_, this_.childrenFolder> as > children5_2_0_, this_.label as label2_0_, this_.description as > descript7_2_0_, this_.creation_date as creation8_2_0_, > this_1_.options_name as options2_26_0_, this_1_.report_id as > report3_26_0_, this_3_.type as type30_0_, this_3_.maxLength as > maxLength30_0_, this_3_.decimals as decimals30_0_, > this_3_.regularExpr as > regularE5_30_0_, this_3_.minValue as minValue30_0_, this_3_.maxValue > as > maxValue30_0_, this_3_.strictMin as strictMin30_0_, > this_3_.strictMax as > strictMax30_0_, this_4_.data as data31_0_, this_4_.file_type as > file3_31_0_, this_5_.jndiName as jndiName32_0_, this_5_.timezone as > timezone32_0_, this_6_.data as data33_0_, this_6_.file_type as > file3_33_0_, this_6_.reference as reference33_0_, > this_7_.serviceClass as > serviceC2_34_0_, this_8_.reportDataSource as reportDa2_36_0_, > this_8_.query as query36_0_, this_8_.mainReport as mainReport36_0_, > this_8_.controlrenderer as controlr5_36_0_, this_8_.reportrenderer > as > reportre6_36_0_, this_8_.promptcontrols as promptco7_36_0_, > this_8_.controlslayout as controls8_36_0_, this_9_.adhocStateId as > adhocSta2_39_0_, this_10_.beanName as beanName40_0_, > this_10_.beanMethod > as beanMethod40_0_, this_11_.driver as driver41_0_, > this_11_.password as > password41_0_, this_11_.connectionUrl as connecti4_41_0_, > this_11_.username as username41_0_, this_11_.timezone as > timezone41_0_, > this_12_.catalog as catalog42_0_, this_12_.mondrianConnection as > mondrian3_42_0_, this_14_.reportDataSource as reportDa2_44_0_, > this_14_.mondrianSchema as mondrian3_44_0_, > this_15_.olapClientConnection > as olapClie2_45_0_, this_15_.mdx_query as mdx3_45_0_, > this_15_.view_options as view4_45_0_, this_16_.catalog as > catalog46_0_, > this_16_.username as username46_0_, this_16_.password as > password46_0_, > this_16_.datasource as datasource46_0_, this_16_.uri as uri46_0_, > this_17_.dataSource as dataSource47_0_, this_17_.query_language as > query3_47_0_, this_17_.sql_query as sql4_47_0_, this_18_.type as > type48_0_, this_18_.mandatory as mandatory48_0_, this_18_.readOnly > as > readOnly48_0_, this_18_.visible as visible48_0_, this_18_.data_type > as > data6_48_0_, this_18_.list_of_values as list7_48_0_, > this_18_.list_query > as list8_48_0_, this_18_.query_value_column as query9_48_0_, > this_18_.defaultValue as default10_48_0_, decode(this_.id, > this_9_.id, 9, > this_14_.id, 14, this_16_.id, 16, this_1_.id, 1, this_2_.id, 2, > this_3_.id, 3, this_4_.id, 4, this_5_.id, 5, this_6_.id, 6, > this_7_.id, 7, > this_8_.id, 8, this_10_.id, 10, this_11_.id, 11, this_12_.id, 12, > this_13_.id, 13, this_15_.id, 15, this_17_.id, 17, this_18_.id, 18, > 0) as > clazz_0_ from JIResource this_, JIReportOptions this_1_, > JIListOfValues > this_2_, JIDataType this_3_, JIContentResource this_4_, > JIJNDIJdbcDatasource this_5_, JIFileResource this_6_, > JICustomDatasource > this_7_, JIReportUnit this_8_, JIAdhocReportUnit this_9_, > JIBeanDatasource > this_10_, JIJdbcDatasource this_11_, JIMondrianXMLADefinition > this_12_, > JIOlapClientConnection this_13_, JIMondrianConnection this_14_, > JIOlapUnit > this_15_, JIXMLAConnection this_16_, JIQuery this_17_, > JIInputControl > this_18_ where this_.id=this_1_.id(+) and this_.id=this_2_.id(+) and > this_.id=this_3_.id(+) and this_.id=this_4_.id(+) and > this_.id=this_5_.id(+) and this_.id=this_6_.id(+) and > this_.id=this_7_.id(+) and this_.id=this_8_.id(+) and > this_.id=this_9_.id(+) and this_.id=this_10_.id(+) and > this_.id=this_11_.id(+) and this_.id=this_12_.id(+) and > this_.id=this_13_.id(+) and this_.id=this_14_.id(+) and > this_.id=this_15_.id(+) and this_.id=this_16_.id(+) and > this_.id=this_17_.id(+) and this_.id=this_18_.id(+) and > (this_.name=? and > this_.parent_folder=?) > > (substituted "X" and 8 for ? -- doesn't matter, as there are no rows at > present) > > > Schuyler L. Southwell > Software Engineer, Consulting > Maricopa County Attorney's Office > tel: 602 506 8107 > southwel@mcao.maricopa.gov > > http://www.stopduiaz.com/ > > > > -----Original Message----- > From: Jonathan Leffler [mailto:jleffler.iiug@gmail.com] > Sent: Thursday, July 05, 2007 11:23 AM > To: Southwell Schuyler > Subject: Re: Make MySQL syntax work in Informix > > On 7/5/07, Southwell Schuyler <southwel@mcao.maricopa.gov> 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;>> > > You'll need to create a sequence called hibernate_sequence as well as > have the dual table in place. > >> 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. > > Of course, you also need a version of IDS that supports sequences - > which were added in either 9.40 or 10.00 (and I cannot now remember > which and I'm feeling too lazy to look it u