Moving data from SQL Server to Postgres via VFP ODBC Driver

I am attempting to move data from a SQL Server 2008 database to Postgres v8.4. I
am using Visual Fox Pro as an intermediate step in this process. I open a
connection to the SQL tables using the ODBC driver and at the same time open a
connect to the PostGres table using PostgresSQL30. The reason I am doing it this
way is I cannot seem to overcome issues concerning bit fields in SQL Server and
Booleans in Postgres. Using VFP I can do a quick and dirty conversion. However,
the problem is that when I look at the data in Postgres it converted
successfully with the exception of varying character fields which have been
padded to their maximum length. My insert statement is in the form :

Insert into PostgresTableName Values(m.field1, trim(m.field2....)

The m. systax is because I am using memory variables in VFP. The data contained
in the m.fields has all be trimmed if it is character type.

Is there any way to keep varying character fields from being padded out to their
maximum length.

Thanks!


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Moving data from SQL Server to Postgres via VFP ODBC Driver

On Mon, Aug 9, 2010 at 10:13 AM, Calvin wrote:
> I am attempting to move data from a SQL Server 2008 database to Postgres v8.4. I
> am using Visual Fox Pro as an intermediate step in this process. I open a
> connection to the SQL tables using the ODBC driver and at the same time open a
> connect to the PostGres table using PostgresSQL30. The reason I am doing it this
> way is I cannot seem to overcome issues concerning bit fields in SQL Server and
> Booleans in Postgres. Using VFP I can do a quick and dirty conversion. However,
> the problem is that when I look at the data in Postgres it converted
> successfully with the exception of varying character fields which have been
> padded to their maximum length. My insert statement is in the form :

It would seem that the string padding is taking place on the
SQL-Server side. Is there anyway to verify if it is treating the
varchar type as char in its odbc driver?

If you need to you can put the trimming operation in before insert
trigger functions on these columns. But I expect this solution is not
optimal.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Moving data from SQL Server to Postgres via VFP ODBC Driver

On Mon, Aug 9, 2010 at 11:31 AM, calvin smith
wrote:

> Thank you for your reply. Actually I am dumping the data from SQLServer i=
nto
> Visual FoxPro memory variables on a record by record basis=A0 and then
> checking the datatype for each memory variable.. If the data type is VFP
> character I am trimming the value so that I KNOW that what is going into =
my
> Insert statement is trimmed data.

I haven't used VFP. Is it an ETL tool?

Another ETL tool to consider (its open source) that is JDBC based can
be found here:
http://sourceforge.net/projects/pentaho/files/Data%20Integra tion/4.0.0-stab=
le/

Also, I don't know if you seen these migration resources:
http://wiki.postgresql.org/wiki/Converting_from_other_Databa ses_to_PostgreS=
QL#Microsoft_SQL_Server

Hopefully there is something useful there.

Also don't forget to reply-all so that the mailing list is included so
that others can reply.

--=20
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Moving data from SQL Server to Postgres via VFP ODBC Driver

Calvin writes:
> Is there any way to keep varying character fields from being padded out to their
> maximum length.

I suspect that your "varying" fields are in fact declared as just
CHAR(n). Use VARCHAR(n), or if there isn't a particularly good reason
to have a specific length limit, use TEXT.

regards, tom lane

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc