Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

Informix Database - Getting Error for the MATCHES Operator

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

1 ACCEPTED SOLUTION

Accepted Solutions

Informix Database - Getting Error for the MATCHES Operator

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.

11 REPLIES

Informix Database - Getting Error for the MATCHES Operator

Hi,

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

G.

New Member

Informix Database - Getting Error for the MATCHES Operator

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

Re: Informix Database - Getting Error for the MATCHES Operator

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

SELECT @@VERSION AS VERSION

Thanks.

G.

New Member

Informix Database - Getting Error for the MATCHES Operator

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

Informix Database - Getting Error for the MATCHES Operator

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.

New Member

Re: Informix Database - Getting Error for the MATCHES Operator

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

Re: Informix Database - Getting Error for the MATCHES Operator

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.

New Member

Re: Informix Database - Getting Error for the MATCHES Operator

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

New Member

Re: Informix Database - Getting Error for the MATCHES Operator

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 dbYes
IBM® DB2® UDBNo
IBM Informix® (1)Yes
Microsoft SQL Server (2)No
MySQLNo
Oracle Database ServerNo
PostgreSQLNo
Sybase ASENo
SQLiteNo

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

Thanks,

Matt

Informix Database - Getting Error for the MATCHES Operator

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.

New Member

Re: Informix Database - Getting Error for the MATCHES Operator

Yea of course lol...

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

Thanks,

Matt

591
Views
5
Helpful
11
Replies
CreatePlease to create content