1

I want to migrate about 10 tables from Postgres to Sybase with a PHP Script.

So far I finished the delete-script, which just empties all of the desired tables on the destination Database.

The migrate-script as of now, is able to connect to both databases and print out the content. Besides that, I am counting the rows for checking if the destination table matches the source table, following the later INSERT (I recently learned of parameter @@ERROR which check for successfull SQL properly and will adjust that).

My problem right now is that I get an ERROR message:

Warning: sybase_query(): Sybase: Server message: Incorrect syntax near '1'. (severity 15, procedure N/A) in .../.../...

when trying to INSERT the selected rows from the Postgres database into the Sybase database. Therefore I used the fetch_assoc method to read the data for each row. The problem of this method, it will return a string instead of an integer, as seen here: http://php.net/manual/de/function.pg-fetch-assoc.php

The specific point in my code lookslike this:

                while ($row = pg_fetch_assoc($result)) {
                $insert_dst = $destDB->query("SET IDENTITY_INSERT " . $dst_sy_tbl . " ON INSERT INTO " . $dst_sy_tbl . " (Integerfield, SOME_TEXT1, SOME_TEXT2) VALUES(". $row['integerfield'] . " '" . $row['some_text1'] . "', '" . $row['sometext2'] . "')"  );
                }

The source tables integer is formatted as int4, the destination table integer is numeric(8,0). The mentioned method now tries to insert a string into the column "integerfield" which expects an integer formatted in numeric(8,0).

Is there a better method in PHP to receive the data from Postgres, so that they are stored "as is", without first being formatted to a string and the need convert the string back to integer afterwards?

Note: I also tried to get the wished result with plain SQL as seen on the board here: Insert into ... values ( SELECT ... FROM ... )

But on executing this on my databasetool it lacked the permission to access the SRC_Table because it is located on a different database. - Maybe within the php-script this will work, as both connections are already estabalished and I was able to print the content of both tables.

SET IDENTITY_INSERT DST_Table ON INSERT INTO db_name.of.DST_Table(Integer, SOME_TEXT1, SOME_TEXT2) SELECT integer, some_text1, some_text2 FROM db_name.of.SRC_Table;
Community
  • 1
  • 1
Christoph Disaster Dude
  • 11
  • 3
  • I totally misunderstood the ERROR message and thought it is bad if every line of the resultset is given as a string. In the end I am putting an SQL-insert command, which is made of strings anyhow. My mistakes were, that I handed the integers as '1' instead of just 1 . Another mistake was a false comparing method, I copied blind from the GO direction Sybase to Postgres Script, for the RETURN direction Postgres to Sybase I had to adjust "string" to "varchar" and "datetime" to "timestamp" – Christoph Disaster Dude Mar 16 '17 at 14:50

1 Answers1

0

If you can use import/export tools...

You should look at using the BCP tool for importing data into Sybase.

This similar SO Post will give you some help on the syntax to use. If you export from Postgres into CSV format for example, you will be able to BCP that data into Sybase ASE

Community
  • 1
  • 1
Brad
  • 13,946
  • 10
  • 56
  • 71