Solved

problems with autoincrement

Posted on 1998-01-07
10
525 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
To form a query 10 413
Cannot create ODBC connection over the network 3 1,253
Using Select Top (Select From ) 5 494
Sybase sample database 13 739
Determining the an SCCM package name from the Package ID
This article is a collection of issues that people face from time to time and possible solutions to those issues. I hope you enjoy reading it.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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