Insert into failed
The poster tried an INSERT ... VALUES ... WHERE NOT EXISTS on IDS to add a row only if a key wasn't already present, and got an error. Replies explained Informix has no WHERE clause on INSERT: use MERGE if on 11.50.xC5 or later (example given), otherwise do the INSERT and catch the unique/primary key violation, or try the UPDATE first and INSERT when zero rows are updated; alternatively INSERT ... SELECT from a temp table with NOT EXISTS. The poster was on IDS 10 behind Cisco CUCM/AXL (no MERGE, no temp tables); Art Kagel suggested the slowness came from AXL sending single statements and recommended bulk insert cursors (ESQL/C, 4GL, ODBC block inserts). No confirmed fix was reported.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
I've tried the following query on IDS:
INSERT INTO Telecaster (directoryservicesurl2, voicemailurl2, fkDevice) VALUES
('pippo', 'pluto', 'myDevice') WHERE NOT EXISTS (SELECT * FROM Telecaster t
WHERE t.fkDevice='myDevice');";
My idea is to insert into Telecaster table a new record (unique id is
'myDevice') only if that record is not yet in the table, but I obtain this
exception:
I miss something? maybe subquery select is not supported? I've checked insert
into statement on
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.s
qls.doc/ids_sqs_2030.htm&resultof=%22merge%22 and doesn't seems so.
Thank you
Gionata Navarra
GIONATA NAVARRA wrote:
> Hi all,
> I've tried the following query on IDS:
>
> INSERT INTO Telecaster (directoryservicesurl2, voicemailurl2, fkDevice)
VALUES
> ('pippo', 'pluto', 'myDevice') WHERE NOT EXISTS (SELECT * FROM Telecaster t
> WHERE t.fkDevice='myDevice');";>
> My idea is to insert into Telecaster table a new record (unique id is
> 'myDevice') only if that record is not yet in the table, but I obtain this
> exception:
>
> I miss something? maybe subquery select is not supported? I've checked insert
> into statement on
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.s
qls.doc/ids_sqs_2030.htm&resultof=%22merge%22
> and doesn't seems so.
No, what you're trying to do is not supported, there is no concept of a
WHERE clause on an INSERT. What you should do is INSERT the row and then
check if you get a "duplicate row" error.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Thanks for your reply. What I need is to update records into table Telecaster or to add them if they aren't already in the table. Because of I handle a large amount of records (more then 30k) I strictly need to minimize accesses number to DB. Do you think is it possible to realize it with only one query? Isnt't there any statement similar to WHERE NOT EXISTS? regards
GIONATA NAVARRA wrote: > Thanks for your reply. > > What I need is to update records into table Telecaster or to add them if they > aren't already in the table. Because of I handle a large amount of records > (more then 30k) I strictly need to minimize accesses number to DB. > Do you think is it possible to realize it with only one query? Isnt't there > any statement similar to WHERE NOT EXISTS? Ugh ... I vaguely remember someone talking about an UPSERT equivalent at some point, but I don't know if anything came of it. 30K inserts isn't a lot though. If that's all you're doing, it shouldn't take more than a couple of seconds on a reasonably modern Linux box. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
GIONATA NAVARRA schrieb:
> Hi all,
> I've tried the following query on IDS:
>
> INSERT INTO Telecaster (directoryservicesurl2, voicemailurl2, fkDevice)
VALUES
> ('pippo', 'pluto', 'myDevice') WHERE NOT EXISTS (SELECT * FROM Telecaster t
> WHERE t.fkDevice='myDevice');";>
> My idea is to insert into Telecaster table a new record (unique id is
> 'myDevice') only if that record is not yet in the table, but I obtain this
> exception:
>
> I miss something? maybe subquery select is not supported? I've checked insert
> into statement on
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.s
qls.doc/ids_sqs_2030.htm&resultof=%22merge%22
> and doesn't seems so.
>
> Thank you
> Gionata Navarra
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Hi Gionata,
Pleas always specify OS and IDS exact version numers!
Depending on the version numer of IDS:
IF < 11.50.xC5
In a search engine or at the IIUG (iiug.org) search for
'the ever elusive upsert' AND 'informix'
IF >= 11.50.xC5
Look at the MERGE Statement in the docs, Guide to SQL: Syntax
----- with credits to the German INFORMIX user group &
their nice monthly newsletter, I copy from newletter of
Sep/2009 this example, which describes a similar action
and try to translate some German text, which is between the
Statements ------
create table restaurant(
name char(42),str char(18),plz char(6),ort char(18),summe dec(7,2)
);
insert into restaurant values ("Gauchos","Taubenberg3","88131","Bodolz",46.90);
insert into restaurant values ("Barcelona","In der Grub32","88131","Lindau",42.60);
insert into restaurant values ("Shano","In der Grub28","88131","Lindau",32.30);
insert into restaurant values ("Alte Schule","Schulplatz2","88131","Lindau",18.20);
insert into restaurant values ("La Perla","Friedrichstr.71","88045","FN",38.80);
select name, summe from restaurant order by 1;name summe
Alte Schule 18.20
Barcelona 42.60
Gauchos 46.90
La Perla 38.80
Shano 32.30
New documents of payment will be inserted into a helper table
create table mx42 (
loc char(42),str char(18),plz char(6),ort char(18),rechnung dec(7,2)
);New bill, location is already known
insert
into mx42 values ("Gauchos","Taubenberg 3","88131","Bodolz",16.90);
New bill, new location
insert
into mx42 values ("Thai House","Reichsplatz 7","88131","Lindau",42.40);
merge into restaurant
using mx42 as s
on restaurant.name = s.loc
when matched
then
update set restaurant.summe = restaurant.summe + s.rechnung
when not matched
then
insert (name,str,plz,ort,summe)
values (s.loc,s.str,s.plz,s.ort,s.rechnung);
select name, summe from restaurant order by 1;
name summe
Alte Schule 18.20
Barcelona 42.60
Gauchos 63.80
La Perla 38.80
Shano 32.30
Thai House 42.40
The amount of the bill for "Gauchos" was added by "Merge" to the existing
entry.
The bill for "Thai House" was inserted into the table, builing
a new entry for a new inn.
Triggers for Insert or Update will be executed on the target table, according
to
the respective event.
Limitations:
No Violation Tables on the target table of MERGE.
Target table can not be a remote table or an external table.
Target table can not be a table of the system catalog.
------ end of copy of the example created by GIUG ------------
dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe
Informix does NOT support WHERE clauses in INSERT statements. I don't think
that it's even permitted in the SQL standard, but I'd have to look that up.
You have two choices: If you are using 11.50xC5 or later you can use the
new MERGE statement, otherwise, you have to either try the insert first and
then update if it fails due to a unique/primary key constraint violation (or
unique index) error or you can try the update first and insert the row if
the update results in zero rows updated (it is not an error to update no
rows). If you can't use MERGE it is usually more efficient to try the
insert first only if 70% or more of the operations will result in an insert
and try the update first if more than about 30% of the operations will
result in an update.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Fri, Jul 2, 2010 at 5:02 AM, GIONATA NAVARRA <gnavarra00@gmail.com>wrote:
> Hi all,
> I've tried the following query on IDS:
>
> INSERT INTO Telecaster (directoryservicesurl2, voicemailurl2, fkDevice)
> VALUES
> ('pippo', 'pluto', 'myDevice') WHERE NOT EXISTS (SELECT * FROM Telecaster t
> WHERE t.fkDevice='myDevice');";>
> My idea is to insert into Telecaster table a new record (unique id is
> 'myDevice') only if that record is not yet in the table, but I obtain this
> exception:
>
> I miss something? maybe subquery select is not supported? I've checked
> insert
> into statement on
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.s
qls.doc/ids_sqs_2030.htm&resultof=%22merge%22
> and doesn't seems so.
>
> Thank you
> Gionata Navarra
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016361e8960affd00048a65dd02
Gionata
What may work is :-
Build a temp table using the required insert information,
INSERT INTO Telecaster
SELECT * FROM 'temp table'
WHERE 1 = 1
AND NOT EXISTS (SELECT * FROM Telecaster t
WHERE t.fkDevice='myDevice');";
On 2 July 2010 12:27, Art Kagel <art.kagel@gmail.com> wrote:
> Informix does NOT support WHERE clauses in INSERT statements. I don't think
> that it's even permitted in the SQL standard, but I'd have to look that up.
>
> You have two choices: If you are using 11.50xC5 or later you can use the
> new MERGE statement, otherwise, you have to either try the insert first and
> then update if it fails due to a unique/primary key constraint violation (or
> unique index) error or you can try the update first and insert the row if
> the update results in zero rows updated (it is not an error to update no
> rows). If you can't use MERGE it is usually more efficient to try the
> insert first only if 70% or more of the operations will result in an insert
> and try the update first if more than about 30% of the operations will
> result in an update.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Fri, Jul 2, 2010 at 5:02 AM, GIONATA NAVARRA <gnavarra00@gmail.com>wrote:
>
>> Hi all,
>> I've tried the following query on IDS:
>>
>> INSERT INTO Telecaster (directoryservicesurl2, voicemailurl2, fkDevice)
>> VALUES
>> ('pippo', 'pluto', 'myDevice') WHERE NOT EXISTS (SELECT * FROM Telecaster t
>> WHERE t.fkDevice='myDevice');";>>
>> My idea is to insert into Telecaster table a new record (unique id is
>> 'myDevice') only if that record is not yet in the table, but I obtain this
>> exception:
>>
>> I miss something? maybe subquery select is not supported? I've checked
>> insert
>> into statement on
>>
>>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.s
qls.doc/ids_sqs_2030.htm&resultof=%22merge%22
>> and doesn't seems so.
>>
>> Thank you
>> Gionata Navarra
>>
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
> --0016361e8960affd00048a65dd02
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Try to answer each one.
Right now I'm handling a Cisco Unified Call Manager that is based on Informix
DB.
My SO is Windows XP Pro, and checking on the CUCM (v 7.1) IDS version seems to
be version 10.
So I think I can't use MERGE statement, and unfortunately I'm not able to
create table on CUCM (I suspect possibility to create table or tmp table isdisable by cisco).
Performance problems are due to the fact that each query is send to db by axl
protocol, with a stronger delay than a direct query.
Thanks all.
Gionata
The performance problem is because AXL is sending each insert as a single
statement. For best performance in bulk inserts under Informix you have to
use an INSERT CURSOR. You should program your load application using
ESQL/C, 4GL, or ODBC (using block inserts) to take advantage of this
technology. In ESQL/C the pseudo code looks like:
EXEC SQL DECLARE insert_curs CURSOR FOR
INSERT INTO sometable VALUES ( ?, ?, ?, ?, ...);
EXEC SQL BEGIN WORK;
EXEC SQL OPEN insert_curs;
while (<more rows to insert>) {
EXEC SQL PUT insert_curs USING :hostvar1, :hostvar2, :hostvar3,
:hostvar4, ...;
if (sqlca.sqlcode < 0) {
fprintf( stderr, "Insert failed. SQLCode: %d, ISAN Code: %d.
Keyval: %d.\\
", sqlca.sqlcode, sqlca.sqlerrd[1], hostvar1 );
}
}
EXEC SQL COMMIT WORK;
EXEC SQL FLUSH insert_curs;
EXEC SQL CLOSE insert_curs;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Jul 5, 2010 at 5:25 AM, GIONATA NAVARRA <gnavarra00@gmail.com>wrote:
> Try to answer each one.
>
> Right now I'm handling a Cisco Unified Call Manager that is based on
> Informix
> DB.
> My SO is Windows XP Pro, and checking on the CUCM (v 7.1) IDS version seems
> to
> be version 10.
> So I think I can't use MERGE statement, and unfortunately I'm not able to
> create table on CUCM (I suspect possibility to create table or tmp table is> disable by cisco).
>
> Performance problems are due to the fact that each query is send to db by
> axl
> protocol, with a stronger delay than a direct query.
>
> Thanks all.
> Gionata
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd32822dd6142048aa55aab
Thanks all. Gionata Navarra