sql INSERT INTO

Version 1
    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