?
Solved

problems with autoincrement

Posted on 1998-01-07
10
Medium Priority
?
527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 200 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

764 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