Primary Key Or Constraints Problem

[sridevi]

I had used the sqlyog's migration toolkit's evaluation of version 5.23 and 5.56 as well to copy data from sybase 12.0 to mysql 5.0.but i could get only tables without any primary keys and indexes not the ones with primary key or constraints. pl help me out.

sjasessionfile contents

–

–

ERROR: 1072, Key column '' doesn't exist in table Table:Assoc Sql:CREATE TABLE `cco3`.`Assoc`( `CCTrackNumber` smallint(5) NOT NULL , `GivenToAdjCC` tinyint(3) UNSIGNED NOT NULL , `AdjCCTrackNumber` smallint(5) NOT NULL , `CtLocOneLineFmt` char(15) NOT NULL , `ctLocLatitude` char(8) NOT NULL , `ctLocLongitude` char(8) NOT NULL , `CtLocElevation` smallint(5) NOT NULL , `CTRefineFlag` tinyint(3) UNSIGNED NOT NULL , `CTRefLocPosXCoord` int(10) NOT NULL , `CTRefLocPosYCoord` int(10) NOT NULL , `CTRefLocElevation` smallint(5) NOT NULL , `CTMovementField` tinyint(3) UNSIGNED NOT NULL , `VelocityVectorX` int(10) NOT NULL , `VelocityVectorY` int(10) NOT NULL , `CTAssocConfidence` tinyint(3) UNSIGNED NOT NULL , `CTThreatValue` tinyint(3) UNSIGNED NOT NULL , `CTTHAnalPriority` tinyint(3) UNSIGNED NOT NULL , `CTAnalysisField` tinyint(3) UNSIGNED NOT NULL , `CTActivePassive` tinyint(3) UNSIGNED NOT NULL , PRIMARY KEY ( `CCTrackNumber` ) , UNIQUE KEY `Assoc_pk` (`CCTrackNumber` ASC ,“ DESC ) )Engine=InnoDB

ERROR: 1072, Key column '' doesn't exist in table Table:CMCCModeInfo Sql:CREATE TABLE `cco3`.`CMCCModeInfo`( `WorkStn1Mode` tinyint(3) UNSIGNED NOT NULL , `WorkStn2Mode` tinyint(3) UNSIGNED NOT NULL , `TimeStamp` timestamp(23) NOT NULL , PRIMARY KEY ( `TimeStamp` ) , UNIQUE KEY `CMCCModeInfo_pk` (`TimeStamp` ASC ,“ DESC ) )Engine=InnoDB

sp_help assoc

2>

Name Owner

Type

---

---

---

Assoc dbo

user table

(1 row affected)

Data_located_on_segment When_created

---

---

default Feb 21 2007 11:14AM

Column_name Type Length Prec

Scale Nulls Default_name

Rule_name Identity

---

---

---

—-

---

---

---

---

---

CCTrackNumber smallint 2 NULL

NULL 0 NULL

Assoc_TrackNumber_check 0

GivenToAdjCC tinyint 1 NULL

NULL 0 NULL

NULL 0

AdjCCTrackNumber smallint 2 NULL

NULL 0 NULL

Assoc_AdjCC_Trk_No_check 0

CtLocOneLineFmt char 15 NULL

NULL 0 NULL

NULL 0

ctLocLatitude char 8 NULL

NULL 0 NULL

NULL 0

ctLocLongitude char 8 NULL

NULL 0 NULL

NULL 0

CtLocElevation smallint 2 NULL

NULL 0 NULL

Assoc_CtLocElevation_check 0

CTRefineFlag tinyint 1 NULL

NULL 0 NULL

Assoc_CTRefineFlag_check 0

CTRefLocPosXCoord int 4 NULL

NULL 0 NULL

NULL 0

CTRefLocPosYCoord int 4 NULL

NULL 0 NULL

NULL 0

CTRefLocElevation smallint 2 NULL

NULL 0 NULL

Assoc_CTRefLocElevation_check 0

CTMovementField tinyint 1 NULL

NULL 0 NULL

Assoc_CTMovementField_check 0

VelocityVectorX int 4 NULL

NULL 0 NULL

NULL 0

VelocityVectorY int 4 NULL

NULL 0 NULL

NULL 0

CTAssocConfidence tinyint 1 NULL

NULL 0 NULL

Assoc_CTAssocConfidence_check 0

CTThreatValue tinyint 1 NULL

NULL 0 NULL

Assoc_CTThreatValue_check 0

CTTHAnalPriority tinyint 1 NULL

NULL 0 Assoc_CTTHAn_32003145

Assoc_CTTHAnalPriority_check 0

CTAnalysisField tinyint 1 NULL

NULL 0 NULL

Assoc_CTAnalysisField_check 0

CTActivePassive tinyint 1 NULL

NULL 0 NULL

NULL 0

index_name index_description

index_keys

index_max_rows_per_page index_fillfactor index_reservepagegap

---

---

---

---

---

---

Assoc_pk clustered, unique located on default

CCTrackNumber

0 0 0

(1 row affected)

keytype object related_object

object_keys

related_keys

---

---

---

---

---

primary Assoc — none —

CCTrackNumber, *, *, *, *, *, *, *

*, *, *, *, *, *, *, *

(1 row affected)

Object is not partitioned.

Lock scheme Allpages

The attribute 'exp_row_size' is not applicable to tables with allpages lock

scheme.

The attribute 'concurrency_opt_threshold' is not applicable to tables with

allpages lock scheme.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap

---

---

---

---

---

1 0 0 0 0

concurrency_opt_threshold

---

0

(return status = 0)

1> sp_help cmccmodeingo

2>

Msg 17461, Level 16, State 1:

Server 'WS2SYB', Procedure 'sp_help', Line 197:

Object does not exist in this database.

(return status = 1)

1> sp_help cmccmodeinfo

2>

Name Owner

Type

---

---

---

CMCCModeInfo dbo

user table

(1 row affected)

Data_located_on_segment When_created

---

---

default Feb 21 2007 11:14AM

Column_name Type Length Prec Scale Nulls Default_name

Rule_name Identity

---

---

---

—-

---

---

---

---

---

WorkStn1Mode tinyint 1 NULL NULL 0 NULL

NULL 0

WorkStn2Mode tinyint 1 NULL NULL 0 NULL

NULL 0

TimeStamp datetime 8 NULL NULL 0 NULL

NULL 0

index_name index_description

index_keys

index_max_rows_per_page index_fillfactor index_reservepagegap

---

---

---

---

---

---

---

CMCCModeInfo_pk clustered, unique located on default

TimeStamp

0 0 0

(1 row affected)

keytype object related_object

object_keys

related_keys

---

---

---

---

---

primary CMCCModeInfo — none —

TimeStamp, *, *, *, *, *, *, *

*, *, *, *, *, *, *, *

(1 row affected)

Object is not partitioned.

Lock scheme Allpages

The attribute 'exp_row_size' is not applicable to tables with allpages lock

scheme.

The attribute 'concurrency_opt_threshold' is not applicable to tables with

allpages lock scheme.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap

---

---

---

---

---

1 0 0 0 0

concurrency_opt_threshold

---

0

(return status = 0)

1>

Here is the sample data of the two tables

1> select * from assoc

2>

CCTrackNumber GivenToAdjCC AdjCCTrackNumber CtLocOneLineFmt ctLocLatitude

ctLocLongitude CtLocElevation CTRefineFlag CTRefLocPosXCoord

CTRefLocPosYCoord CTRefLocElevation CTMovementField VelocityVectorX

VelocityVectorY CTAssocConfidence CTThreatValue CTTHAnalPriority

CTAnalysisField CTActivePassive

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

1 0 0 56 K/6 597732 0174253

0781604 0 0 0

0 0 0 0

0 9 7 0

1 6

2 0 0 56 K/9 904769 0174501

0783328 0 0 0

0 0 0 0

0 9 5 0

1 6

3 0 0 56 K/13 157870 0175038

0784744 0 0 0

0 0 0 0

0 9 2 0

1 6

4 0 0 56 K/6 755672 0173941

0782504 0 0 0

0 0 0 0

0 9 4 0

0 6

5 0 0 56 K/9 026799 0174643

0784021 0 0 0

0 0 0 0

0 9 6 0

1 6

(5 rows affected)

1> select * from cmccmodeinfo

2>

WorkStn1Mode WorkStn2Mode TimeStamp

---

---

---

255 112 Mar 14 2007 11:39AM

255 112 Mar 14 2007 11:44AM

255 112 Mar 14 2007 11:47AM

255 112 Mar 14 2007 11:51AM

255 112 Mar 15 2007 10:04AM

255 112 Mar 16 2007 9:04AM

255 112 Mar 16 2007 12:28PM

255 112 Mar 16 2007 12:30PM

255 112 Mar 16 2007 2:19PM

255 112 Mar 20 2007 8:57AM

255 112 Mar 21 2007 9:05AM

(11 rows affected)

1>

[peterlaursen]

This was the correct place to post.  so I'll delete th other post.

It is most practical to discuss only in one place.

We had quite a lot of new hardware recently and reisntalled most computers.

We therefore do not have our old Sybase install functional at the moment.

I'll have to ask you to have a little patience while we are setting it up again.

[sridevi]

' wrote on 'Mar:

This was the correct place to post.  so I'll delete th other post.

It is most practical to discuss only in one place.

We had quite a lot of new hardware recently and reisntalled most computers.

We therefore do not have our old Sybase install functional at the moment.

I'll have to ask you to have a little patience while we are setting it up again.

[/quote

Dear Mr peterlaursen,

thank u

any help is appriciated

[peterlaursen]

we will try our best.

But it probably will not be before Monday that we have everything ready for trying to reproduce the issue

[sridevi]

somebody please solve this problem.

its urgent.

[adarsh]

We have now installed Adaptive Enterprise Server 15.0 and the ODBC driver that ships with the installer. A fast setup with two small tables gave:

Code:

SQLyog Job Agent Version 5.26
Copyright (c) Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Job started at Wed Mar 28 19:24:26 2007

DBMS Information: SQL SERVER
Importing table schema: Dept… Successful…
Importing table schema: Employees… Successful…
Importing table foreign keys: Dept… Successful…
Importing table foreign keys: Employees… Successful…
Importing table data: Dept…

3 rows transferred!
Successful…

Importing table data: Employees…

3 rows transferred!

Successful…

Total time taken – 1 sec(s)

There are PK's, Unique indexes in both tables and a Foreign Key.

We cannot reproduce, as you see. Everything imports.

It is not the same server version as yours. this is the one that we could get.

A google search tells about a lot of issues with ODBC drivers.

There are a lot of unoriginal drivers. And the original driver does not install as default. You will have to select from the 'custom install' option of the installer.

Are you perfectly sure that you can retrieve data with your ODBC driver to some other program (MS Access / Open Office Base for instance) ?

If you can give us a dump with a test case (schema, a few tables with a few rows of data) we will try to reproduce.

[sridevi]

Dear Adarsh,

Actually I was trying to import data from sybase adaptive server 12.0 to Mysql 5. and I was using DATA DIRECT 'S SYBASE WIRE PROTOCOL DRIVERS which is freely available on net for evaluation as the odbc driver for sybase. I need to see if the odbc drivers for sybase are available with sybase adaptive server 12.0 or not.

the data of the two tables of sybase whose data i could not import is already mentioned in my first post. the data is not properly aligned. this is the output the isql editor generates.

is that suffficient or u r looking for some more information.

with regards

sridevi

[sridevi]

Dear Adarsh,

Actually I was trying to import data from sybase adaptive server 12.0 to Mysql 5. and I was using DATA DIRECT 'S SYBASE WIRE PROTOCOL DRIVERS which is freely available on net for evaluation as the odbc driver for sybase. I need to see if the odbc drivers for sybase are available with sybase adaptive server 12.0 or not.

the data of the two tables of sybase whose data i could not import is already mentioned in my first post. the data is not properly aligned. this is the output the isql editor generates.

is that suffficient or u r looking for some more information.

with regards

sridevi

[adarsh]

Yes we are looking for more information.

Can you export that 2 tables as SQL dump and zip it and attach here don't paste so that we can import easily. This would be the best way to ensure that we don't do any mistakes.

Without this information we cannot do anything.

[adarsh]

We installed the driver which you mentioned in your last post “DATA DIRECT 'S SYBASE WIRE PROTOCOL DRIVERS” and we tried to import we got same problem what you are facing. This is a driver issue.

Please try to install the driver from “Adaptive Server” (to install this you need to select custom install) and try it will work properly.

[sridevi]

Dear Adarsh,

Do you mean Adaptive Server Enterprise ODBC Driver by Sybase?

Where can I down load the Adaptive Server driver. We had been using Data Direct's driver.

thanks.

regards

sridevi

[adarsh]

While Installing the Adaptive Server select custom install and choose ODBC driver from the components and install.

This will install a driver called “Adaptive Server Enterprise” use this driver this will work.

[sridevi]

actually my requirement is that I have an application with sybase 12.0 as the database on solaris 2.7 running on sun sparc workstation.

my new application is on windows with mysql and needs to replicate the sybase databse. so I need sybase drivers on the pc with wondows 2000 as the os. so i think i cannot install ase on pc nor the drivers. hence could you suggest me some other drivers. Are ase odbc drivers for windows available.

regards

sridevi

[adarsh]

From here you can download Sybase (Adaptive Server 15.0) for windows, while installing select custom install and choose ODBC driver from components.

This will install a driver called “Adaptive Server Enterprise” with this ODBC driver you should able connect and it will wiok perfectly please try this.

http://www.sybase.com/detail?id=1037439

[sridevi]

thank you.

I will try

sridevi

adarsh wrote on Mar 30 2007, 03:47 PM:

From here you can download Sybase (Adaptive Server 15.0) for windows, while installing select custom install and choose ODBC driver from components.

This will install a driver called “Adaptive Server Enterprise” with this ODBC driver you should able connect and it will wiok perfectly please try this.

http://www.sybase.com/detail?id=1037439

[sridevi]

Dear Adarsh,

1. I installed Adaptive Server Enterprise 15 by custom install and also the Adaptive Server Enterprise odbc driver. I created a datasource called system121 and tested the connection. which gave the message “connection succeeded”. but when i used the driver in the migration tool then no tables from sybase database were listed.

2. I had also downloaded pcclient1252 from the net. the sybase driver tat comes with it is Sybase Ase odbc driver . with this driver the tables from the source sybase databse are listed. but I am able to import the tables without any primary key or indexes. I am sending u the listing of the table schema as well as data as an attachment

Could it be because my source database is sybase 12 and the ase driver is fron sybase ase 15?

[adarsh]

Hi,

After installing that ODBC driver from ASE 15.0 installer did you find any driver like “Adaptive Enterprise Server” in control panel–>Administrator tool–>DataSource(ODBC)–>Add. because i just tried to import a table with PK and Unique key using this driver it is working properly.

Please use this driver.

While creating the DSN you have to specify the correct Server Name(host server) and correct Logon ID.

If you create a table using Logon id “sa” default user, then you have to specify that Login ID in create DSN window.

Make sure that all the details (Server Name, Logon ID, Server Port, Database Name) you are entering while creating the DSN are correct.

Adarsh

[sridevi]

hi adarsh,

Yes, AFter installing ASE 15 , i found a driver like Adaotive enterprise server and I entered the syabse work station 1p, port no, log in id and the database name . i did not mention the server name. i think it doesnot ask for server name. also i gort a mesage that log in succesful.

is this problem because of the difference in versions.?

[adarsh]

Hi,

Tell me are you installed more than one version of ASE?

When we install a ASE it will create a four types of servers 1) Sybaes BCKServer 2) SybaseMONServer 3)Sybase SQLServer 4) Sybase XP Server

by default it will take your system name as server name and it will create the server names like this. you need to use the SQL server name in Server Name field in create DSN window.

1) ADARSH_BS for Backup Server

2) ADARSH_MS for MOnitoring Server

3) ADARSH for SQL Server

4) ADARSH_XP for XP Server

If you install the ASE server of another version then it will create name like this ADARSH_BS1,ADARSH_MS1,ADARSH1,ADARSH_XP1 so if you installed more than one verion of server please specify server name.

I tried to create a DSN without specifying the server name i am getting error.

Please verify the server name in which you created that database and also please verify the login id you used to create that database and table and also please specify the port of that correct server in which you have created the database and table.

You need to use that Server Name and Logon Id and correct port.

[sridevi]

Hi adarsh,

I installed only one version of ASE. and as u said the four servers were installed

But installation of ase was required on my part on the pc was esential for getting the ASE ODBC driver as the datadirect driver was giving the problem mentioned in initial post.

The source database on sybase with ASE 12 is on a sun workstation with ip address 191.0.0.121. so using this ASE driver from ase 15 on the pc I was assessing the sybase databse.

datasource name = system121

server = 191.0.0.121

port =5555

login = dbh

database = ncdb

The login successful message also popped up.

But in the migration tool when i chose copy tables from datasource(sybase datasource) no tables from sybase 12( on sun workstation) were selected.

then I tried the other option i.e copy data by using a query. In this case, the query on the sybase table emp2 was successful but for the result table in the Map tab did not hav any primary key by default. but I made the columb 'ssn' as the primary key and the data was transferred successfuly BUT WITHOUT ANY PRIMARY KEYS.

so as u see it is quite puzzling .

please help me out.

adarsh wrote on Apr 5 2007, 03:52 PM:

Hi,

Tell me are you installed more than one version of ASE?

When we install a ASE it will create a four types of servers 1) Sybaes BCKServer 2) SybaseMONServer 3)Sybase SQLServer 4) Sybase XP Server

by default it will take your system name as server name and it will create the server names like this. you need to use the SQL server name in Server Name field in create DSN window.

1) ADARSH_BS for Backup Server

2) ADARSH_MS for MOnitoring Server

3) ADARSH for SQL Server

4) ADARSH_XP for XP Server

If you install the ASE server of another version then it will create name like this ADARSH_BS1,ADARSH_MS1,ADARSH1,ADARSH_XP1 so if you installed more than one verion of server please specify server name.

I tried to create a DSN without specifying the server name i am getting error.

Please verify the server name in which you created that database and also please verify the login id you used to create that database and table and also please specify the port of that correct server in which you have created the database and table.

You need to use that Server Name and Logon Id and correct port.

[adarsh]

Are you using ASE 12.0 server with ASE 15.0 driver?

If you don't want to uninstall that ASE 12.0 server then please install ASE 15.0 server with driver in another machine and then try. In windows i tried it is working fine.

[sridevi]

HI Adarsh,

Let me explain the requirement to u.

I have an old application running in a SUN SPARC workstation with Sybase ASE 12 as the DATABASE and Solaris 2. 7 as the OS. The new project is supposed to develop an application running on windows 2k with MYSQL 5 as the database. The application has to replicate data from sybase to mysql. For That i was running sqlyog on the windows pc with mysql and using datadirect drivers to access the sybase database on the workststion with ip 191.0.0.121. with this tables without any primary keys or constraints i was able to access. I had problems in accessing the tables with primary keys. please refer to the attachment in my fprevious message. then i installed ase 15 on the pc so that i can use the ase driver that comes along with it to access the ase12 sybase database on the workstation through the ethernet lan as suggested by u. u said that the problem is due to datadirect drivers. so u see there is no requirement to install ase 15 on another system.

did u try with this kind of setup ?

please suggest a solution as early as possible.

I would like to tell u that I tried using deatadirect drivers with the CRecordset classes and i wall able access all the tables with primary keys and without primary keys.

[Author]

Posts