problems with autoincrement

hello!

i've got a prob with sybase 5.01 + Visual Age C++ for win95

i made a database where the unique keys are generated automatically from sybase.
then i wanted to set an insert in my C++ program using the SQLExecDirect commando.
i generated the insert-statement without unique-key.
(insert into table (f2,f3,...)values(v2,v3,...)
now sybase thinks that the acual key (generated from sybase) is not unique
error message:Sybase ODBC Driver Integrity constraint violation: primary key for table ... is not unique.

i tried to fix this problem by changing the SQLExecDirect with SQLPrepare and SQLExecute - the same.
so my question: is there someone out there who can help me out of the mess?

thx forward
mikel
michael_aAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jbiswasCommented:
I don't know of any sybase version called 5.01. Please tell me what version of sybase are you using. Also what is the datatype of the identity column you have applied?
0
jbiswasCommented:
I don't know of any sybase version called 5.01. Please tell me what version of sybase are you using.
Also what is the datatype of the identity column you have applied?
0
jbiswasCommented:
Mikel,

What version of sybase are you running. I do not know of any version called 5.01.
You can find out the version by logging into the server and executing
select @@version

Also sybase has a identity column feature(has to be always numeric datatype. Are you using an identity column for your table?
You cannot insert data to an identity column unless someone with a 'sa' role turns on the
set identity insert on
However there is no way to default a column to autoincrement in Sybase SQL server, you can do it in SQL Anywhere though.

0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

jbiswasCommented:
Mikel,

What version of sybase are you running. I do not know of any version called 5.01.
You can find out the version by logging into the server and executing
select @@version

Also sybase has a identity column feature(has to be always numeric datatype. Are you using an identity column for your table?
You cannot insert data to an identity column unless someone with a 'sa' role turns on the
set identity insert on
However there is no way to default a column to autoincrement in Sybase SQL server, you can do it in SQL Anywhere though.

0
jbiswasCommented:
Mikel,

What version of sybase are you running. I do not know of any version called 5.01.
You can find out the version by logging into the server and executing
select @@version

Also sybase has a identity column feature(has to be always numeric datatype. Are you using an identity column for your table?
You cannot insert data to an identity column unless someone with a 'sa' role turns on the
set identity insert on
However there is no way to default a column to autoincrement in Sybase SQL server, you can do it in SQL Anywhere though.

0
jbiswasCommented:
sorry for the multiple postings. It felt like my browser was not saving stuff.
0
michael_aAuthor Commented:
hi again
you're right that's sybaseSQL anywhere 5.0, sorry.
i've got in every table of my database an identity column. the type of it is numeric and the values are unique. and i use the default-value function autoincrement. the crasiest thing of the mistake is, when i try to insert data with ISQL (also without a value for the identity column) it also doesn't work till i alter the properties of the identity column (set autoincrement off, say ok, then set autoincrement on again). then i'm able to insert data from isql an my program too.
where can i set identity insert on?

mikel
0
jbiswasCommented:
You can use a integer datatype and default it to autoincrement in the following manner
field_name integer NOT NULL DEFAULT autoincrement

In SQL Anywhere the identity column is identical to the AUTOINCREMENT default setting for a column.

In SQL Server, each table in a database can have one identity column. The data type must be numeric with scale zero, and the identity column should not allow nulls.

In SQL Anywhere, the identity column is implemented as a column default setting. Values that are not part of the sequence may be explicitly inserted into the column with an INSERT statement. SQL Server does not allow INSERTs into identity columns unless the identity_insert option is set to on. In SQL Anywhere, you need to set the NOT NULL property yourself and ensure that no more than one column is an identity column. SQL Anywhere allows any numeric data type to be used as an identity column.

The first time you insert a row into the table, SQL Anywhere assigns a value of 1 to an identity column. On each subsequent insert, the value of the column is incremented by one. The value most recently inserted into an identity column is available in the @@identity global variable.



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
michael_aAuthor Commented:
we've now updated the sybase sql on databaseversion 5.5.02 and now we have no longer this problem.
however, thx for your help


0
jbiswasCommented:
Why didn't you guys go to v5.5.0.3 directly, It is definitely a much nicer version.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.