Is Sql-Workbench support informix database?

[MarcInUse]

Hi Thomas:
I am using IBM Informix Dynamic Server Version 11.50.FC1, Is Sql-
Workbench support informix database?
Currently I can connect to the database with Sql-Workbench, and do the
query. However, the database explorer can not get triggers and fetch
data with system table object.

[MarcInUse]

Those are the workbench.log.


INFO 02.10.2009 10:00:21 DbMetadata - Using
DBID=informix_dynamic_server
INFO 02.10.2009 10:00:21 ConnectionMgr.getConnection() - Connected
to: [Informix Dynamic Server], Database version: [10.02.FC3], Driver
version: [3.00.JC3], ID: [WbExp-4]
WARN 02.10.2009 10:00:37 TriggerReader.getTriggers() - getTriggers()
called but no SQL configured
ERROR 02.10.2009 10:02:42 TableDataPanel.showRowCount() - Error
retrieving rowcount for event_demo.informix.systriggers: A syntax
error has occurred. [SQL State=42000, DB Errorcode=-201]

[MarcInUse]

Change the jodbc driver to Informix version 11.50.FC3:

INFO 02.10.2009 10:10:34 DbMetadata - Using
DBID=informix_dynamic_server
INFO 02.10.2009 10:10:34 ConnectionMgr.getConnection() - Connected
to: [Informix Dynamic Server], Database version: [11.50.FC3], Driver
version: [3.50.JC3DE], ID: [WbExp-1]
WARN 02.10.2009 10:10:35 TriggerReader.getTriggers() - getTriggers()

called but no SQL configured

ERROR 02.10.2009 10:10:47 TableDataPanel.showRowCount() - Error

[Thomas Kellerer]

Hi,

I know that some people are using Informix.

I have not configured the necessary statements to retrieve triggers, view source or stored procedures as I don't have access to an Informix installation.

What happens when you run a

SELECT count(*) FROM event_demo.informix.systriggers

manually?

Regards
Thomas

MarcInUse, 02.10.2009 04:13:

[MarcInUse]

When I pressed Trigger in Database Explorer the log is:

>WARN 02.10.2009 10:10:35 TriggerReader.getTriggers() - getTriggers() called but no SQL configured

Iin Database Explorer ,I choice Objects tab with SYSTEM TABLE list
value. When I Pressed the object named 'systriggers' with data
explorer,the log is:

>ERROR 02.10.2009 10:10:47 TableDataPanel.showRowCount() - Error retrieving rowcount for event_demo.informix.systriggers: A syntax error has occurred. [SQL State=42000, DB Errorcode=-201]

However I use manual sql below to query, it return data:
select * from systriggers;

[Thomas Kellerer]

MarcInUse, 02.10.2009 09:34:

> When I pressed Trigger in Database Explorer the log is:
>> WARN 02.10.2009 10:10:35 TriggerReader.getTriggers() - getTriggers() called but no SQL configured

Yes, that is expected.

I don't have the SQL to retrieve the triggers in Informix. There is no JDBC API for that.

> Iin Database Explorer ,I choice Objects tab with SYSTEM TABLE list
> value. When I Pressed the object named 'systriggers' with data
> explorer,the log is:
>> ERROR 02.10.2009 10:10:47 TableDataPanel.showRowCount() - Error retrieving rowcount for event_demo.informix.systriggers: A syntax error has occurred. [SQL State=42000, DB Errorcode=-201]
>
> However I use manual sql below to query, it return data:
> select * from systriggers;

Please try

SELECT count(*) FROM event_demo.informix.systriggers

and

SELECT * FROM event_demo.informix.systriggers

do include the prefix as shown above, because that is the SQL generated to retrieve the rowcount in the DbExplorer

Thomas

[MarcInUse]

> Please try
>
> SELECT count(*) FROM event_demo.informix.systriggers
> and
> SELECT * FROM event_demo.informix.systriggers
>
> do include the prefix as shown above, because that is the SQL generated to retrieve the rowcount in the DbExplorer

I test the sqls below, current database is event_demo

SELECT count(*) FROM event_demo.informix.systriggers; --failed
SELECT * FROM event_demo.informix.systriggers; --failed

Error Log:

A syntax error has occurred. [SQL State=42000, DB Errorcode=-201]

SELECT count(*) FROM event_demo.systriggers; --failed
SELECT * FROM event_demo.systriggers; --failed

Error Log:
The specified table (event_demo.systriggers) is not in the
database. [SQL State=42000, DB Errorcode=-206]
Next: ISAM error: no record found. [SQL State=IX000, DB
Errorcode=-111]


SELECT count(*) FROM informix.systriggers; --it works
SELECT * FROM informix.systriggers; --it works

[MarcInUse]

The Informix, System Catalog Table information:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqlr.doc/ids_sqr_076.htm

[Thomas Kellerer]

MarcInUse, 05.10.2009 04:28:

> I test the sqls below, current database is event_demo
>
> SELECT count(*) FROM event_demo.informix.systriggers; --failed
> SELECT * FROM event_demo.informix.systriggers; --failed
>
> Error Log:
> A syntax error has occurred. [SQL State=42000, DB Errorcode=-201]
>
>
> SELECT count(*) FROM event_demo.systriggers; --failed
> SELECT * FROM event_demo.systriggers; --failed
>
> Error Log:
> The specified table (event_demo.systriggers) is not in the
> database. [SQL State=42000, DB Errorcode=-206]
> Next: ISAM error: no record found. [SQL State=IX000, DB
> Errorcode=-111]
>
>
> SELECT count(*) FROM informix.systriggers; --it works
> SELECT * FROM informix.systriggers; --it works

So it seems that Informix doesn't like the database as part of an object name.

I will add a property that surpresses the usage of the database in that case.

Thanks for the feedback.

Regards
Thomas

[Roberto]

Hi Thomas,

this same error

A syntax error has occurred. [SQL State=42000, DB Errorcode=-201]

is coming up in Build 114.10 while trying to see the triggers' body under Informix.
Trigger names are showing fine, same for procedures name and body (all OK).

This sort of query is working fine too:

    select data
     FROM
     (
       SELECT b.data||'\n' as data, -1 as seqno, t.trigname
       FROM informix.systriggers t, informix.systrigbody b
       WHERE t.trigid=b.trigid
         AND b.datakey = 'D'
       union all
       SELECT b.data||'\n' as data, b.seqno, t.trigname
       FROM informix.systriggers t, informix.systrigbody b
       WHERE t.trigid=b.trigid
         AND b.datakey = 'A'
     )
     where trigname = ?
     order by seqno


Any parameter I need to change?
Thanks in advance,
Roberto

[Thomas Kellerer]

Hi Roberto,

the statement you included is exactly the statement that is currently configured in SQL Workbench.

Can you paste the complete error message?
Or send me the *complete* logfile (with DEBUG log level)?

Regards
Thomas


Roberto, 11.06.2013 16:15:

[Roberto]

Pasted below

Regards,
Roberto

2013-06-11 16:42 DEBUG ObjectCache.addTable() Added table definition for mytestdb:mytestuser.testtable
2013-06-11 16:42 ERROR DbMetadata.getTriggerSource() Error reading trigger source A syntax error has occurred. [SQL State=42000, DB Errorcode=-201]
java.sql.SQLException: A syntax error has occurred.
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3461)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3774)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2580)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2496)
    at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2384)
    at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2359)
    at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:390)
    at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1322)
    at com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:1292)
    at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1006)
    at com.informix.jdbc.IfxStatement.execute(IfxStatement.java:892)
    at workbench.db.DefaultTriggerReader.getTriggerSource(DefaultTriggerReader.java:264)
    at workbench.gui.dbobjects.TriggerListPanel.retrieveTriggerSource(TriggerListPanel.java:445)
    at workbench.gui.dbobjects.TriggerListPanel$5.run(TriggerListPanel.java:417)
    at java.awt.event.InvocationEvent.dispatch(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$200(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.sql.SQLException
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3779)
    ... 26 more

[Roberto]

Hi again,

problem seems to be in the query below shown in the log file:

2013-06-11 17:35 INFO  DbMetadata.getTriggerSource() Using query=

        select data
        FROM
        (
          SELECT b.data||'\n' as data, -1 as seqno, t.trigname
          FROM informix.systriggers t, informix.systrigbody b
          WHERE t.trigid=b.trigid
            AND b.datakey = 'D'
          union all
          SELECT b.data||'\n' as data, b.seqno, t.trigname
          FROM informix.systriggers t, informix.systrigbody b
          WHERE t.trigid=b.trigid
            AND b.datakey = 'A'
        )

       AND trigname = 'd_test123' ORDER BY seqno

it is blindly adding "AND" whereas in this case it should add WHERE... instead.

as a workaround, I've changed the TriggerSourceStatements.xml to read:

  <void method="put">
   <string>Informix Dynamic Server</string>
   <object id="list_informix_trg" class="workbench.db.

GetMetaDataSql">
    <void property="baseSql">
     <string>

        select data
        FROM
        (
          SELECT b.data||'\n' as data, -1 as seqno, t.trigname
          FROM informix.systriggers t, informix.systrigbody b
          WHERE t.trigid=b.trigid
            AND b.datakey = 'D'
          union all
          SELECT b.data||'\n' as data, b.seqno, t.trigname
          FROM informix.systriggers t, informix.systrigbody b
          WHERE t.trigid=b.trigid
            AND b.datakey = 'A'

        ) WHERE 1=1
      </string>
    </void>
    <void property="objectNameField">
     <string>trigname</string>
    </void>
    <void property="orderBy">
     <string>ORDER BY seqno</string>
    </void>
   </object>
  </void>

 

and that is fixing the problem for me.

Thanks a lot for your help in getting to the bottom of this one.

Best Regards,
Roberto

[Thomas Kellerer]

Hi Roberto,

thanks for finding this.

I will change the SQL generation so that this "hack" is no longer necessary.

Regards
Thomas

Roberto wrote on 11.06.2013 19:19:
> Hi again,
>
> problem seems to be in the query below shown in the log file:
>
> 2013-06-11 17:35 INFO DbMetadata.getTriggerSource() Using query=
>
> select data
> FROM
> (
> SELECT b.data||'\n' as data, -1 as seqno, t.trigname
> FROM informix.systriggers t, informix.systrigbody b
> WHERE t.trigid=b.trigid
> AND b.datakey = 'D'
> union all
> SELECT b.data||'\n' as data, b.seqno, t.trigname
> FROM informix.systriggers t, informix.systrigbody b
> WHERE t.trigid=b.trigid
> AND b.datakey = 'A'
> )

> *AND *trigname = 'd_test123' ORDER BY seqno

>
> it is blindly adding "AND" whereas in this case it should add WHERE... instead.
>
> as a workaround, I've changed the TriggerSourceStatements.xml to read:
>
> <void method="put">
> <string>Informix Dynamic Server</string>
> <object id="list_informix_trg" class="workbench.db.
> GetMetaDataSql">
> <void property="baseSql">
> <string>
> select data
> FROM
> (
> SELECT b.data||'\n' as data, -1 as seqno, t.trigname
> FROM informix.systriggers t, informix.systrigbody b
> WHERE t.trigid=b.trigid
> AND b.datakey = 'D'
> union all
> SELECT b.data||'\n' as data, b.seqno, t.trigname
> FROM informix.systriggers t, informix.systrigbody b
> WHERE t.trigid=b.trigid
> AND b.datakey = 'A'

> ) *WHERE 1=1*

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>