Informix Database - Getting Error for the MATCHES Operator

[Matthew Martin]

Hello All,

This question is for Cisco EIM's Informix Database.

I couldn't find the version of the Informix Database being used for EIM but I was able to find a command to run if I SSH'ed to UCCX for example, which would get me the Informix version info for our other CallCenter servers like UCCX, CUCM, Unity, etc...

Which is:

     admin: run sql select unique dbinfo('version', 'full') from systables
     (constant)                                        
     ================================================= 
     IBM Informix Dynamic Server Version 11.50.UC5XA   

So I was assuming that EIM's Database was probably pretty close, if not the same version...

And I found on IBM's site for Informix Version 11.5 that the MATCHES operator is available for this version.

     http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_1386.htm

I'm also using the SQuirreL SQL App to access/browse and query the database, which all seem to be working just fine. Except for the MATCHES operator gives me the error below:

     *Here is the simpliest test I could think of with the MATCHES operator so I could rule out other syntax issues...

      It just gives me any QUEUE_NAME that has an "E" followed by 0 or more characters...

SELECT * FROM eGActiveDB.dbo.EGMLR_SMY_QUEUE WHERE QUEUE_NAME MATCHES "E.*"

Error Output:

Error: An expression of non-boolean type specified in a context where a condition is expected, near 'MATCHES'.
SQLState:  S1000
ErrorCode: 4145
Error: The cursor was not declared.
SQLState:  S1000
ErrorCode: 16945
Error: The executeQuery method must return a result set.
SQLState:  24000
ErrorCode: 0

The strange part is the LIKE operator works just fine, so I'm a bit confused why this MATCHES one wouldn't work too..?

Any ideas for why this could not be wokring...? Any thoughts would be very much appreciated!

Thanks in Advance,

Matt

[Gergely Szabo]

Hi,

er... Informix? Are you sure it's Informix?

G.

[Matthew Martin]

Hey Gergely, thanks for the reply...

I'm pretty sure it is... I know the Database for UCCX is for sure, because of the output from the command on the CLI in my original post. And EIM is considered CallCenter type software so I figured it would be the same...

THIS COMMAND from the UCCX CLI was:

     admin: run sql select unique dbinfo('version', 'full') from systables
     (constant)                                        
     ================================================= 
     IBM Informix Dynamic Server Version 11.50.UC5XA 

I'm also using the Informix Driver to access the Database with RazorSQL and SQuirreL SQL, and I assume that wouldn't work if it wasn't Informix, right..?

And I tried the MATCHES operator within both of those "Database Explorers / SQL Database Query Tools"  and I get pretty much the same error within both programs.

Thanks Again,

Matt

[Gergely Szabo]

OK, can you run the following query for me please:

SELECT @@VERSION AS VERSION

Thanks.

G.

[Matthew Martin]

Hey, thanks again for the reply!

Nice... I was looking for a command to give me that info..!! Thanks!

SELECT @@VERSION AS VERSION

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
    Oct 14 2005 00:33:37 
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Is it the "@@VERSION" keyword there that does that..?

I was looking for commands like that to get info on the DB, do you know of any good sites with this type of information in it... i.e. Information like Keywords, Operators and Special Keywords like "@@VERSION"?

Looks like this one is NOT Informix... Is it strange that the Informix Driver works to get me access to the DB...?

Thanks Again for the reply!

-Matt

[Gergely Szabo · Accepted solution]

OK, so we finally got there ;-)

Apparently, SQL Server's Transact-SQL syntax does not know about the MATCHES operator. LIKE is the closest. You may want to take a look at these docs:

http://technet.microsoft.com/en-us/library/ms189822%28v=sql.105%29.aspx

http://technet.microsoft.com/en-us/library/ms187489%28v=sql.105%29.aspx

About that Squirrel: can you please post the JDBC URL?

G.

[Matthew Martin]

Awesome, thanks for the links. I'll check them out!

Yea, I've been using the LIKE operator for most of the queries I was attempting, but I really like using REGEX's and have gotten pretty good with them, which is why I was trying to use the MATCHES operator... But thanks, that's good to know.

Ughh... I'm wrong again.... Now looking at the Datasource I setup a WHILE back I now see it's NOT using the driver I thought it was.

EIM JDBC Datasource Info:

DRIVER:
     JTDS
URL:
     jdbc:jtds:sqlserver://192.168.x.xx:1433/eGActiveDB;appName=squirrel-sql;useCursors=true

Humm.... I was wondering what the Error meant in my OP, when it said: "Error: The cursor was not declared."

Does that have anything to do with the URL where it says "useCursors=true", and what do they mean by "Cursor"..?

Sorry for all the questions, you just seem like you know what your talking about.

Thanks AGAIN,

Matt

[Gergely Szabo]

Hi,

about the @@VERSION - it's a proprietary language construct, I don't remember having seen it anywhere else but SQL Server. Other databases: well, these the ones I use:

Oracle DB: select * from v$version

MySQL: STATUS;

IBM DB2: select * from SYSIBM.SYSVERSIONS

Apache Derby: values syscs_util.syscs_get_database_property( 'DataDictionaryVersion' );

About the cursors in general - a cursor is a language construct that allows you to fetch one row at a time from a result set (iterator pattern). There are certain situations when the use of cursors is necessary, but if there's a better option, one should not touch them. Take a look at this, for instance: http://www.sqlteam.com/article/cursors-an-overview

The author of this post has been writing SQL queries for call center reporting purposes since 2003 and there was only 1 (one) situation when the use of cursors was inevitable.

About the useCursors parameter in the JDBC URL: http://jtds.sourceforge.net/faq.html#urlFormat

G.

[Matthew Martin]

Perfect, thanks for the info,  very much appreciated!

Ok, that makes sense. As you can tell I'm fairly new with Database'ing, other then what I learned in college...

But anyway, thanks again for all the info, it was very helpful. And the links you posted for the MS SQL Server docs, that was exactly what I was looking for...

Thanks Again,

Matt

[Matthew Martin]

Here is something I found in my Searching Google...

This is a table I found on IBM's website for SQL Portability in terms of the MATCHES and LIKE Operators:

     *SQL Portability - Matches and Like

In that link you'll see this info:

Table 1. Database server support for MATCHES operator

Database Server Type	Support for SQL MATCHES operator?
Genero db	Yes
IBM® DB2® UDB	No
IBM Informix® (1)	Yes
Microsoft SQL Server (2)	No
MySQL	No
Oracle Database Server	No
PostgreSQL	No
Sybase ASE	No
SQLite	No

And as you had said before, there is "MS SQL Server" and it says "no" for whether it supports MATCHES

Thanks,

Matt

[Gergely Szabo]

Hi,

well, SQL may be an ANSI standard, as we can see, vendor implementations may be quite different.

As we can see, the MATCHES operator is an Informix-specific construct.

G.

[Matthew Martin]

Yea of course lol...

Yup, looks that way. Thanks again for your help with this... Very much appreciated!

Thanks,

Matt