There seem to be a missing or incomplete consistency checking of
INTEGER values from SEQUENCE objects in the version I have in my
computer. I have not been so good in browsing the mailing list the last
months, so this may be old or obsolete news for the readers of the list.
The environment and SAPDB version are:
OS: Windows XP Professional.
SAPDB: Kernel : Kernel 7.3.0 Build 020-000-000-000
Runtime Environment : NT/INTEL 7.3.0 Build
020-000-000-000
If I use INSERT to set an integer collumn to 2147483648 using REPMCLI,
then
the expected constraint [-2147483648,2147483647] implied by the type seem
to be used to reject my request.
But if the same value is set using NEXTVAL on a SEQUENCE object then the
insert will be successful but giving a less consistent value in the
INTEGER
collumn.
If you later select for that value, with a tool, like SQL Studio,
that try to really get and display the value, you may se a message like:
"[SAP AG][SQLOD32 DLL][SAP DB]Numeric value out of range;-811 Numeric
output parameter"
If you want the insert to fail in this case, then you may insert
" MAXVALUE 2147483647" into the declaration of the sequence object,
which seems to be a good workaround.
Standard output from repmcli:
"c:\Program\sapdb\depend\bin\repmcli" -d <dbname> -u
<username>,<password> -E 0 -b <sql_file>
=============================================================
Opened connection to REPLICATION SERVER at node local host.
REPLICATION SERVER Log File:
'C:\Program\Sapdb\indep_data\wrk\repserver.log'
User test connected to database DBALLAN2 on local host.
CREATE TABLE br21017_kv (k INTEGER PRIMARY KEY, v INTEGER NOT NULL)
Successfully executed
CREATE SEQUENCE br21017_s1 INCREMENT BY 1 START WITH 2147483646 NOCYCLE
NOCACHE
Successfully executed
CREATE SEQUENCE br21017_s2 INCREMENT BY 1 START WITH 2147483646 MAXVALUE
2147483647 NOCYCLE NOCACHE
Successfully executed
INSERT INTO br21017_kv(k, v) values (1046, br21017_s1.nextval)
Successfully executed
INSERT INTO br21017_kv(k, v) values (1047, br21017_s1.nextval)
Successfully executed
INSERT INTO br21017_kv(k, v) values (1048, br21017_s1.nextval)
Successfully executed
INSERT INTO br21017_kv(k, v) values (2046, br21017_s2.nextval)
Successfully executed
INSERT INTO br21017_kv(k, v) values (2047, br21017_s2.nextval)
Successfully executed
INSERT INTO br21017_kv(k, v) values (2048, br21017_s2.nextval)
Error during execution
-->-25010
SQL error -2028 = Sequence BR21017_S2 exhausted (error position: 1)
INSERT INTO br21017_kv(k, v) values (3047, 2147483647)
Successfully executed
INSERT INTO br21017_kv(k, v) values (3048, 2147483648)
Error during execution
-->-25010
SQL error 300 = Integrity violation:V (error position: 44)
select k from br21017_kv
Successfully executed
select * from br21017_kv where k = 1047
Successfully executed
select * from br21017_kv where k = 1048
Successfully executed
=============================================================
The 3 queries was then reexecuted from SQL Studio:
=============================================================
select k from br21017_kv
K
====
1 1046
2 1047
3 1048
4 2046
5 2047
6 3047
Statement successfully executed
select * from br21017_kv where k = 1047
K V
=====================
1 1047 2147483647
Statement successfully executed
select * from br21017_kv where k = 1048
[SAP AG][SQLOD32 DLL][SAP DB]Numeric value out of range;-811
Numeric output parameter
=============================================================
Then i wanted to display the current value of the sequence object
that inserted the inconsistent value, but I had to use NEXTVAL instead
of CURRVAL because SQL Studio had its one session with the database.
=============================================================
SELECT br21017_s1.NEXTVAL FROM DUAL
EXPRESSION1
=============
1 2147483649
Statement successfully executed
=============================================================
Best regards !
Hans Davidson <[EMAIL PROTECTED]>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
