sql INSERT INTO

[cdnadmin]

This document was generated from CDN thread

Created by: Gionata Navarra on 01-07-2010 02:45:44 PM
Hi all,
 
I'm working with CUCM 7.0 and I need to fill by Axl two fields (Directory and Services) in Device --> Phone --> External Data Locations Information.
If fields are not empty I run a SQL UPDATE, if they are empty I use following INSERT INTO:
 
updateQuery ="INSERT INTO Telecaster (directoryservicesurl2, voicemailurl2, fkDevice) VALUES ('pippo', 'pluto', '1bbefe83-3de6-478a-bd20-b547ff48f69c') WHERE NOT EXISTS (SELECT * FROM Telecaster t WHERE t.fkDevice='1bbefe83-3de6-478a-bd20-b547ff48f69c');";

ExecuteSQLUpdateReq update = new ExecuteSQLUpdateReq();
update.setSql(updateQuery);
ExecuteSQLUpdateRes updateRes = null;

try {
   updateRes = port.executeSQLUpdate(update);
} catch (Exception e){
   e.printStackTrace();
}
 
but I have this exception: A syntax error has occurred.
 
My idea is to insert new record only if it isn't already in the table.
 
What is the problem?
 
Thank you
Gionata Navarra

Subject: RE: sql INSERT INTO
Replied by: David Staudt on 01-07-2010 05:49:59 PM
The underlying database for UC Manager is IBM Informix Dynamic Server (IDS).  This DB engine is tuned for 'embedded' applications - more for speed/real-time operations - and I've seen that sometimes its support of extended ANSI SQL features is incomplete. 
 
You may find some details on IDS SQL here: http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp.  I suspect that selecting by a sub-query is the root of the problem in this example, and you will need to just do two queries: one to determine if the record exists, and then do either INSERT or UPDATE as necessary.

Subject: RE: sql INSERT INTO
Replied by: Gionata Navarra on 02-07-2010 01:30:26 PM
You are right, the problem is that INSERT INTO statement doesn't support WHERE clause.
 
Do you know where can I find IDS version in each CUCM?
I need IDS version for CUCM 7.0 and 7.1.
 
Thank you
Gionata

Subject: RE: sql INSERT INTO
Replied by: David Staudt on 02-07-2010 03:58:36 PM
Looks like the systables table has the info, from 7.0(1):
 
admin:run sql select DBINFO('version','major') from systables WHERE tabid=1
(constant)
==========
10

Subject: RE: sql INSERT INTO
Replied by: Gionata Navarra on 05-07-2010 01:35:15 PM

Looks like the systables table has the info, from 7.0(1):
 
admin:run sql select DBINFO('version','major') from systables WHERE tabid=1
(constant)
==========
10

 
Thanks
Gionata