SQL Anywhere database - processing error messages on a constraint violation

Author

processing error messages on a constraint violation

Douglas M. Deram

2005-05-16, 8:23 pm

i'm trying to put several constraints on tables and columns in my database,
but whenever i try to enter a value that fails i simply get a sqlcode
= -209, with no additional information. is there some way to determine what
constraint was violated, or what table/column the violation occurred for? i
apologize if i'm asking an obvious question, but at a glance i can't figure
out how to get this info.

thanks,
Doug

Glenn Paulley

2005-05-17, 3:23 am

"Douglas M. Deram" <dougderam@yeahright.com> wrote in
news:4289203b@forums
-1-dub:

> i'm trying to put several constraints on tables and columns in my
> database, but whenever i try to enter a value that fails i simply get
> a sqlcode = -209, with no additional information. is there some way
> to determine what constraint was violated, or what table/column the
> violation occurred for? i apologize if i'm asking an obvious
> question, but at a glance i can't figure out how to get this info.
>
> thanks,
> Doug
>
>

9.x software supports ANSI named constraints, which permit you to do
exactly what you describe. What release/build are you using?

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Douglas M. Deram

2005-05-17, 9:23 am

sorry, i'm using 9.0.1 (1751).

i know how to set up the constraints in my table(s), but i just can't figure
out how to determine which constraint caused the UPDATE or INSERT to fail.
i.e. if i have 2 constraints on 2 different columns and i'm trying to INSERT
a row, how do i know which value was invalid? like i said it looks like it
simply says sqlcode = -209, with no indication of which constraint was
violated.

thanks for your prompt response.

Doug

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns9658DFBEAEE1
5paulleyianywherecom
@10.22.241.106...
> "Douglas M. Deram" <dougderam@yeahright.com> wrote in
> news:4289203b@forums
-1-dub:
>
>
> 9.x software supports ANSI named constraints, which permit you to do
> exactly what you describe. What release/build are you using?
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Glenn Paulley

2005-05-17, 11:23 am

The message for the -209 error will indicate the name of the constraint
that was violated.

Named constraints require a 9.0.1 format database file. To see if your
database supports named constraints, execute the query

select db_property( 'NamedConstraints' ) from dummy

and see if the value is ON or OFF. If OFF you have an older database, and
must unload/reload to get the named constraints feature.

Glenn

"Douglas M. Deram" <dougderam@yeahright.com> wrote in
news:4289f9c9$1@foru
ms-2-dub:

> sorry, i'm using 9.0.1 (1751).
>
> i know how to set up the constraints in my table(s), but i just can't
> figure out how to determine which constraint caused the UPDATE or
> INSERT to fail. i.e. if i have 2 constraints on 2 different columns
> and i'm trying to INSERT a row, how do i know which value was invalid?
> like i said it looks like it simply says sqlcode = -209, with no
> indication of which constraint was violated.
>
> thanks for your prompt response.
>
> Doug
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns9658DFBEAEE1
5paulleyianywherecom
@10.22.241.106...
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Breck Carter [TeamSybase]

2005-05-17, 1:23 pm

Try this example from my book:

CREATE TABLE t (
c1 INTEGER,
c2 INTEGER,
c3 INTEGER,
CONSTRAINT "0 < c1 < c2 < c3"
CHECK ( 0 < c1 AND c1 < c2 AND c2 < c3 ) );
INSERT t VALUES ( 1, 2, 3 ); -- OK
INSERT t VALUES ( 2, 2, 2 ); -- fails

You should see this:

Constraint '0 < c1 < c2 < c3' violated: Invalide value for column 'c2'
in table 't'.

Breck

On 17 May 2005 07:03:57 -0700, "Douglas M. Deram"
<dougderam@yeahright.com> wrote:

>sorry, i'm using 9.0.1 (1751).
>
>i know how to set up the constraints in my table(s), but i just can't figure
>out how to determine which constraint caused the UPDATE or INSERT to fail.
>i.e. if i have 2 constraints on 2 different columns and i'm trying to INSERT
>a row, how do i know which value was invalid? like i said it looks like it
>simply says sqlcode = -209, with no indication of which constraint was
>violated.
>
>thanks for your prompt response.
>
>Doug
>
>"Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns9658DFBEAEE1
5paulleyianywherecom
@10.22.241.106...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Douglas M. Deram

2005-05-23, 11:23 am

thanks for all the quick responses.

i'm mostly having trouble using them in an embedded SQL environment
(embedded in C++ code). for some reason the sqlca variable doesn't seem to
have suffcient information to identify the constraint that was violated.

"Douglas M. Deram" <dougderam@yeahright.com> wrote in message
news:4289f9c9$1@foru
ms-2-dub...
> sorry, i'm using 9.0.1 (1751).
>
> i know how to set up the constraints in my table(s), but i just can't
figure
> out how to determine which constraint caused the UPDATE or INSERT to fail.
> i.e. if i have 2 constraints on 2 different columns and i'm trying to
INSERT
> a row, how do i know which value was invalid? like i said it looks like
it
> simply says sqlcode = -209, with no indication of which constraint was
> violated.
>
> thanks for your prompt response.
>
> Doug
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns9658DFBEAEE1
5paulleyianywherecom
@10.22.241.106...
>
>

Sponsored Links