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
pg_copy_from() not working
Moderator: General Moderators
Re: pg_copy_from() not working
Are you sure the postgres database has the same number of rows in the customers table?
Re: pg_copy_from() not working
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
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
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
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
Remember the PHP manual is your friend! http://www.php.net/manual/en/function.p ... -array.php
Re: pg_copy_from() not working
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
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