-3

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.

19
  • 3
    ...what on earth is the backstory here? Your SQL feels like it suddenly timewarped-in from a Sybase project from the early 1990s... from the machine of someone desperate for job-security... Commented Dec 12 at 5:35
  • 5
    BTW, using IGNORE_DUP_KEY = ON on a UNIQUE INDEX abcatt_x really defeats the point of creating a unique index... Commented Dec 12 at 5:36
  • 4
    @Igor You don't see any problem with using INSERT without 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-view sysobjects? Using cartesian-product joins? Using positional-parameters instead of named-parameters? Using impenetrable and cryptic column names? Using CHECK-less char(1) instead of bit? Using fixed-length columns when the values you're inserting a much smaller (thus wasting space)? Etc. Etc. Commented Dec 12 at 6:46
  • 2
    You can use SQL server profiler to see the queries. Just watch out and don't leave it on since it uses a lot of resources. You could just try without the where exist, which should guarantee you something should be inserted. But once again, I really think you could benefit from creating a minimum reproducible example. The c odbc code can get a bit long-winded but you can use functions for most stuff Commented Dec 12 at 7:05
  • 1
    @siggemannen Once again, more direct evidence that Larry Ellison enjoys watching people suffer. Commented Dec 12 at 8:12

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.