Solved

problems with autoincrement

Posted on 1998-01-07
10
522 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:michael_a
  • 8
  • 2
10 Comments
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098198
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
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098199
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
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098200
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 2

Expert Comment

by:jbiswas
ID: 1098201
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
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098202
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
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098203
sorry for the multiple postings. It felt like my browser was not saving stuff.
0
 

Author Comment

by:michael_a
ID: 1098204
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
 
LVL 2

Accepted Solution

by:
jbiswas earned 100 total points
ID: 1098205
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
 

Author Comment

by:michael_a
ID: 1098206
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
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098207
Why didn't you guys go to v5.5.0.3 directly, It is definitely a much nicer version.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SEO can be a real minefield to navigate, but there are three simple ways to up your SEO game just be re-assessing your content output.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question