Page 1 of 1

pg_copy_from() not working

Posted: Tue Jul 06, 2010 10:09 am
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

Re: pg_copy_from() not working

Posted: Tue Jul 06, 2010 11:05 am
by Jade
Are you sure the postgres database has the same number of rows in the customers table?

Re: pg_copy_from() not working

Posted: Tue Jul 06, 2010 10:37 pm
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

Re: pg_copy_from() not working

Posted: Wed Jul 07, 2010 1:25 am
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

Re: pg_copy_from() not working

Posted: Wed Jul 07, 2010 8:53 am
by Jade
Remember the PHP manual is your friend! http://www.php.net/manual/en/function.p ... -array.php

Re: pg_copy_from() not working

Posted: Wed Jul 07, 2010 10:40 am
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