pg_copy_from() not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ocset
Forum Newbie
Posts: 4
Joined: Tue Jul 06, 2010 9:25 am

pg_copy_from() not working

Post by ocset »

Hi All

I have an ODBC and a postgres database with identical schemas. I am trying to write a simple program that reads data from an ODBC table and inserts the values into a Postgres table. I have tested the tables by using an ETL tool that does a straight copy from the ODBC table to to the Postgres table, which works perfectly.

Here is my code:

<?php
$odbc_conn=odbc_connect("odbc_db", "Administrator", "password");

$pg_conn=pg_connect('host=localhost dbname=pg_db user=postgres password=postgres');

$sql="SELECT * FROM Customers";

$a=odbc_exec($odbc_conn, $sql);

$b = odbc_fetch_into($a, $rows);

pg_copy_from($pg_conn, "Customers", $rows);

odbc_close($odbc_conn);
pg_close($pg_conn);
?>

I keep getting an error - Warning: pg_copy_from(): Copy command failed: ERROR: missing data for column "Col1".

This is the first column in the table and a print_r of the $rows variable shows the correct values have been fetched from the ODBC database. Even though this is only a warning, no data gets loaded into the postgres database.

I have tried to manually create an array rather than fetching the data from the ODBC source, just to see if I can get the pg_copy_from() to work but also no luck.

Please help
Thanks
O
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: pg_copy_from() not working

Post by Jade »

Are you sure the postgres database has the same number of rows in the customers table?
ocset
Forum Newbie
Posts: 4
Joined: Tue Jul 06, 2010 9:25 am

Re: pg_copy_from() not working

Post by ocset »

Yes, I do a "print_r" of the array which is created when I fetch the data from the ODBC database and the array has 20 entries and the table in Postgres has 20 columns.

I just had a look at the error message again and I was wrong in my original post, the error is referring to the second column in the table not the first? Does that make a difference to solving the problem?

Thanks
O
ocset
Forum Newbie
Posts: 4
Joined: Tue Jul 06, 2010 9:25 am

Re: pg_copy_from() not working

Post by ocset »

I have found the problem.

When I fetch the data from the ODBC source, it creates an array where each element of the array represents a column. pg_copy_from() expects each element of the array to be a complete row for inserting.

What php function should I be using instead to ensure the data in loaded into the array correctly when I read it from the ODBC source?

Thanks
O
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: pg_copy_from() not working

Post by Jade »

Remember the PHP manual is your friend! http://www.php.net/manual/en/function.p ... -array.php
ocset
Forum Newbie
Posts: 4
Joined: Tue Jul 06, 2010 9:25 am

Re: pg_copy_from() not working

Post by ocset »

Thanks Jade - only problem is that is a postgres function. I am looking for the ODBC function to do the same thing as pg_copy_to(), as I am reading the data from an ODBC source.

I solved the problem as follows

$i = 0;
while ($rows = odbc_fetch_array($rs))
{
$tmp=implode(' ',$rows);
$result_array[$i]=$tmp;
$i += 1;
}

This creates an array where each item is a row and pg_copy_from() is happy. Pity there is no odbc_copy_to() function.

Thanks for your help
O
Post Reply