Basis Errors and Solutions

Symptom

Problems with the odbc based DbSl:
(1)
On attempts to write NULL values to NOT NULL columns without default erroneously a duplicate key error is returned.
Correct would be a DB error with SQL error code 515 and message “Cannot insert the value NULL into column ‘…’, table ‘…’; column does not allow nulls.”
(2)
OpenSQL selects with up to clause and subselects return wrong (too few) results.
OpenSQL single selects with subselects return wrong (too few) results.
The cause is that the restriction (up to n rows) is used within the subselects, too.
Example:
the OpenSQL statement
SELECT SINGLE * FROM /smb/bb_lib_i
INTO lds_bb_lib_i
WHERE bbid = ‘XXXX’ AND
bbid IN (
SELECT DISTINCT bbid FROM /smb/bbpr_i
WHERE prjid IN (
SELECT DISTINCT prjid
FROM /smb/bb_prj_et01
WHERE scope = ‘XXX’
)
).
is translated to the (TransAct) SQL statement
SELECT
TOP 1 T_00 .”MANDT” AS c ,T_00 .”BBID” AS c ,T_00 .”SEQNUM” AS c ,
T_00 .”OBJTY” AS c ,T_00 .”OBJID” AS c ,T_00 .”FILENAME” AS c ,
T_00 .”INTERACT” AS c ,T_00 .”MANDAT” AS c ,T_00 .”LONGTX” AS c ,
T_00 .”PARENT” AS c ,T_00 .”INSTRUCTION_ID” AS c ,
T_00 .”DOC_ID” AS c , T_00 .”DOC_POPUP” AS c ,T_00 .”TCODE” AS c ,
T_00 .”SYSDATA” AS c , T_00 .”TESTSYSTEM” AS c ,
T_00 .”ECAT_VAR” AS c ,T_00 .”DEMO” AS c , T_00 .”FIELDNAME” AS c
FROM
“/SMB/BB_LIB_I” T_00
WHERE
T_00 .”MANDT” = ? AND
T_00 .”BBID” = ? AND
T_00 .”BBID” IN (
SELECT TOP 1 T_100 .”BBID” FROM “/SMB/BBPR_I” T_100
WHERE
T_100 .”MANDT” = ? AND
T_100 .”PRJID” IN (
SELECT TOP 1 T_200 .”PRJID” FROM “/SMB/BB_PRJ_ET01″ T_200
WHERE
T_200 .”MANDT” = ? AND
T_200 .”SCOPE” = ? )
)
which is wrong since it restricts the result sets returned by the subselects to 1 and therefore falsifies the overall result. Correct would be
SELECT
TOP 1 T_00 .”MANDT” AS c ,T_00 .”BBID” AS c ,T_00 .”SEQNUM” AS c ,
T_00 .”OBJTY” AS c ,T_00 .”OBJID” AS c ,T_00 .”FILENAME” AS c ,
T_00 .”INTERACT” AS c ,T_00 .”MANDAT” AS c ,T_00 .”LONGTX” AS c ,
T_00 .”PARENT” AS c ,T_00 .”INSTRUCTION_ID” AS c ,
T_00 .”DOC_ID” AS c , T_00 .”DOC_POPUP” AS c ,T_00 .”TCODE” AS c ,
T_00 .”SYSDATA” AS c , T_00 .”TESTSYSTEM” AS c ,
T_00 .”ECAT_VAR” AS c ,T_00 .”DEMO” AS c , T_00 .”FIELDNAME” AS c
FROM
“/SMB/BB_LIB_I” T_00
WHERE
T_00 .”MANDT” = ? AND
T_00 .”BBID” = ? AND
T_00 .”BBID” IN (
SELECT DISTINCT T_100 .”BBID” FROM “/SMB/BBPR_I” T_100
WHERE
T_100 .”MANDT” = ? AND
T_100 .”PRJID” IN (
SELECT DISTINCT T_200 .”PRJID” FROM “/SMB/BB_PRJ_ET01″ T_200
WHERE
T_200 .”MANDT” = ? AND
T_200 .”SCOPE” = ? )
)

Other terms

SAPSQL_ARRAY_INSERT_DUPREC, SAPLM61U, LM61UF1T, INSERT_MDSBI_IN_MDSB, SUBSELECT, SINGLE, UPTO, MD50, MD02
Reason and Prerequisites

program errors in odbc DbSl.
Solution

DbSl correction will be provided
SAP KERNEL 7.00 *: “lib_dbsl” (Patch-level #209)
Patch text: DbSl: SQL errors erroneously translated to ‘duplicate key’
SAP KERNEL 7.01 *: “lib_dbsl” (Patch-level #45)
Patch text: DbSl: SQL errors erroneously translated to ‘duplicate key’
SAP KERNEL 7.10 *: “lib_dbsl” (Patch-level #155)
Patch text: DbSl: SQL errors erroneously translated to ‘duplicate key’
SAP KERNEL 7.11 *: “lib_dbsl” (Patch-level #41)
Patch text: DbSl: SQL errors erroneously translated to ‘duplicate key’
SAP KERNEL 7.20 *: “lib_dbsl” (Patch-level #2)
Patch text: DbSl: SQL errors erroneously translated to ‘duplicate key’
P.S.
The first problem (1) was detected when array inserts to view MDSB failed with SAPSQL_ARRAY_INSERT_DUPREC. The underlying table RESB had a number of fields on the database NOT NULL but without default. Some of these fields were missing in the projection view MDSB so each insert via this view resulted in a SQL515 erroneously displayed as duplicate key error.
Now this new dbsl will correct only the misleading error message. The inserts will still fail.
If you encounter such a problem you should run a forced conversion via SE14 (SE14, edit table, Extras->Forced Conversion). This will recreate the table and all the NOT NULL columns will be defined with a default bound. So the SQL515 won’t occur anymore.
Please be aware: if the table is huge then the conversion will take a longer time.