Consider following:
qry2 = L"INSERT INTO abcattbl SELECT ?, ?, (SELECT object_id FROM sys.objects o, sys.schemas s WHERE s.schema_id = o.schema_id AND o.name = ? AND s.name = ?), ?, 8, 400, \'N\', \'N\', 0, 1, 0, \'MS Sans Serif\', 8, 400, \'N\', \'N\', 0, 1, 0, \'MS Sans Serif\', 8, 400, \'N\', \'N\', 0, 1, 0, \'MS Sans Serif\', \'\' WHERE NOT EXISTS(SELECT * FROM abcattbl WHERE abt_tnam=? AND abt_ownr=? AND abt_os=?);";
INSERT INTO abcattbl
SELECT
?,
?,
(
SELECT
object_id
FROM
sys.objects o,
sys.schemas s
WHERE
s.schema_id = o.schema_id AND
o.name = ? AND
s.name = ?
),
?, 8, 400,
'N', 'N', 0,
1, 0, 'MS Sans Serif',
8, 400, 'N',
'N', 0, 1,
0, 'MS Sans Serif', 8,
400, 'N', 'N',
0, 1, 0,
'MS Sans Serif', ''
WHERE
NOT EXISTS( SELECT * FROM abcattbl WHERE abt_tnam = ? AND abt_ownr = ? AND abt_os = ? );
After running this statement thru SQLPrepare()/SQLBindParameter()/SQLExecute() no record is inserted in the table. All functions return SQL_SUCCESS and then COMMIT is issued.
The table is created with the following:
queries.push_back( L"IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='abcattbl' AND xtype='U') CREATE TABLE \"abcattbl\"(abt_os tinyint, abt_tnam nchar(129) NOT NULL, abt_tid integer, abt_ownr nchar(129) NOT NULL, abd_fhgt smallint, abd_fwgt smallint, abd_fitl char(1), abd_funl char(1), abd_strke smallint, abd_fchr smallint, abd_fptc smallint, abd_ffce char(18), abh_fhgt smallint, abh_fwgt smallint, abh_fitl char(1), abh_funl char(1), abh_strke smallint, abh_fchr smallint, abh_fptc smallint, abh_ffce char(18), abl_fhgt smallint, abl_fwgt smallint, abl_fitl char(1), abl_funl char(1), abl_strke smallint, abl_fchr smallint, abl_fptc smallint, abl_ffce char(18), abt_cmnt nchar(254) );" );
queries.push_back( L"IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = \'abcatt_x\' AND object_id = OBJECT_ID( \'abcattbl\' )) CREATE UNIQUE INDEX abcatt_x ON abcattbl( abt_os ASC, abt_tnam ASC, abt_ownr ASC ) WITH (IGNORE_DUP_KEY = ON);");
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='abcattbl' AND xtype='U')
CREATE TABLE "abcattbl" (
abt_os tinyint,
abt_tnam nchar(129) NOT NULL,
abt_tid integer,
abt_ownr nchar(129) NOT NULL,
abd_fhgt smallint,
abd_fwgt smallint,
abd_fitl char(1),
abd_funl char(1),
abd_strke smallint,
abd_fchr smallint,
abd_fptc smallint,
abd_ffce char(18),
abh_fhgt smallint,
abh_fwgt smallint,
abh_fitl char(1),
abh_funl char(1),
abh_strke smallint,
abh_fchr smallint,
abh_fptc smallint,
abh_ffce char(18),
abl_fhgt smallint,
abl_fwgt smallint,
abl_fitl char(1),
abl_funl char(1),
abl_strke smallint,
abl_fchr smallint,
abl_fptc smallint,
abl_ffce char(18),
abt_cmnt nchar(254)
);
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'abcatt_x' AND object_id = OBJECT_ID( 'abcattbl' ))
CREATE UNIQUE INDEX abcatt_x ON abcattbl( abt_os ASC, abt_tnam ASC, abt_ownr ASC ) WITH (IGNORE_DUP_KEY = ON);
Is there an easy way to see what is server receives and tries to execute? I suspect some SQLBindParameter() is not binding the right data, but I can't see what is executed.
EDIT:
Parameters I passed in was a NULL- terminated strings. However the last parameter to SQLBindParameter() was not SQL_NTS. When I used the proper parameter everything was working as expected.
IGNORE_DUP_KEY = ONon aUNIQUE INDEX abcatt_xreally defeats the point of creating a unique index...INSERTwithout a destination column list? Having all-nullable columns? Not having a clustering index defined? Not having a PK defined? Using a non-unique "unique" index? Not using schema-qualified names? Using the deprecated compatibility-viewsysobjects? Using cartesian-product joins? Using positional-parameters instead of named-parameters? Using impenetrable and cryptic column names? UsingCHECK-lesschar(1)instead ofbit? Using fixed-length columns when the values you're inserting a much smaller (thus wasting space)? Etc. Etc.