If your PostgreSQL database is going to be running under the Windows OS,
you will need to download and install the PostgreSQL ODBC driver:
http://pgfoundry.org/projects/psqlodbc/
Once you have the PostgreSQL ODBC driver installed, you can use window
applications like VFP or perl under windows to connect to PostgreSQL
over ODBC. If using perl, then you will need to install a couple of
perl modules named DBI and DBD::ODBC. Windows may use a back slash
instead of the double colons to designate the directory tree, so it may
by DBD\ODBC when installing perl modules into the windows perl:
You can visit the CPAN to read how to use DBD::ODBC
http://search.cpan.org/~jurl/DBD-ODBC-1.13/ODBC.pm
In my shopping cart application I have a class property set that tell
the application whether to use ODBC or DBI to connect to the PostgreSQL
database. Other than that, no other changes would be needed in the code
to perform the insert, deletes, updates, inserts, etc. Here is the
procedure in the class where all connection are made:
########################################
## sql_dbh
########################################
sub sql_dbh
{
my $self = shift;
my ( $userlogged ) = @_;
if ( $userlogged eq "t" )
{
eval
{
if ( $self->{DNS} eq "yes" )
{
$self->{DBH} = DBI->connect
(
qq~dbi:$self->{THE_DBI}~,
$self->{PG_USER},
$self->{PG_PASSWORD}
);
}
else
{
$self->{DBH} = DBI->connect
( qq~DBI:$self->{THE_DBI} = $self->{THE_DB};
host = $self->{THE_HOST};
port = $self->{THE_PORT}
~,
$self->{PG_USER}, ## user name
$self->{PG_PASSWORD} ## password
); ## Get connected
}
$self->{DBH}->{RaiseError} = 1; ## Turn on Errrors
$self->{DBH}->{autocommit} = $self->{THE_AUTOCOMMIT};
## set $self{THE_AUTOCOMMIT} to 0 to turn transactions on.
};
}
else
{
eval
{
if ( $self->{DNS} eq "yes" )
{
$self->{DBH} = DBI->connect
(
qq~dbi:$self->{THE_DBI}~,
$self->{SU_PG_USER},
$self->{SU_PG_PASSWORD}
);
}
else
{
$self->{DBH} = DBI->connect
( qq~DBI:$self->{THE_DBI} = $self->{THE_DB};
host = $self->{THE_HOST};
port = $self->{THE_PORT}
~,
$self->{SU_PG_USER}, ## user name
$self->{SU_PG_PASSWORD} ## password
); ## Get connected
}
$self->{DBH}->{RaiseError} = 1; ## Turn on Errrors
$self->{DBH}->{autocommit} = $self->{THE_AUTOCOMMIT};
## set $self{THE_AUTOCOMMIT} to 0 to turn transactions on.
};
}
if ( $@ )
{
$the_error = qq~I was unable to connect to the database
because [EMAIL PROTECTED];
$self->new_error_form( $the_error );
if ( defined( $self->{ DBH } ) )
{
$self->{ DBH }->disconnect();
}
exit;
}
else
{
return $self->{ DBH };
}
}
################## end sql_dbh ################
All SQL in the application is pass the this procedure in the class
module, where it is executed and the results return to the app:
#########################################
## sql_exec
#########################################
sub sql_exec
{
my $self = shift;
my ( $the_sql, $display_sql, $chain_to ) = @_;
eval
{
$self->{ DBH }->quote( $the_sql );
};
if ( $self->{ THE_AUTOCOMMIT } == 0 )
{
$self->{ DBH }->BEGIN; ## begin transaction
}
eval
{
if ( $self->{DNS} eq "yes" )
{
$self->{ DBH }->{LongReadLen} = 65536;
$self->{ DBH }->{LongTruncOk} = 1; ### We're happy
to truncate any excess
}
$self->{ STH } = $self->{ DBH }->prepare( $the_sql );
$self->{ STH }->execute;
if ( $self->{ THE_AUTOCOMMIT } == 0 )
{
$self->{ DBH }->commit() ## end transaction
}
};
if ( $@ )
{
$self->{ DO_SELECT } = "false";
my $the_error =
qq~The database server returned the following
message:<br><br>[EMAIL PROTECTED];
if ( $display_sql ne "false" )
{
$the_error = $the_error
. qq~<br><br>The SQL pass to the database Server
was:<br><br>$the_sql~;
};
$self->new_error_form( $the_error, $chain_to );
if ( $self->{ THE_AUTOCOMMIT } == 0 )
{
eval {
$self->{ DBH }->rollback() ## we have a
problem, so rollback()
};
}
if ( defined( $self->{ DBH } ) )
{
$self->{ DBH }->disconnect();
}
exit;
}
else
{
return $self->{ STH };
}
}
################### end sql_exec ##################
Regards,
LelandJ
Ken McGinnis wrote:
ok, so maybe I could update a Postgres with the values (instead of creating a fox free table), then perl could read and write the
Postgres data. What if I only want to read/write 4 fields (maybe 20,000 records) into a single table? Do you know a URL reference
for
1. install Postgres on Win2000 pro
2. some simple code for vfp9 to read/write 4 fields in Postgres
3. some simple perl code to read/write the same fields in Postgres
Sry if this is something simple, but I have no experience with Postgres.
Thanks
----- Original Message -----
From: "Ted Roche" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Monday, July 17, 2006 11:07 AM
Subject: Re: Need suggestions for Win/apache2 access to native fox data vfp9
On 7/17/06, Ken McGinnis <[EMAIL PROTECTED]> wrote:
On Win2000 pro, you can selet Administrative tools, Data Sources (ODBC), 'add' - and close to the bottom there is a selection
for
Microsoft Visual Foxpro Driver - then in the setup dialog there is a selection:
'Database type' - one of the selections is Visual
FoxPro database (DBC). - wouldn't this work ok?
Yes, I'm quite familar with using VFP via ODBC. However, I don't think
that's what the vast majority of people use when they are running
Perl-based web sites. As a consequence, you're more likely going to
run into challenges getting support. You seemed to indicate that your
eighteen years of FoxPro experience would serve you better than your
newly-acquired Perl skills, so I recommend using FoxPro to read and
write to the popular MySQL (or Postgres) format, rather than depend on
the more obscure ODBC or OLEDB of Perl/W32. I think you'd find more
support and better-tested code that way. Maybe not.
If you want Perl to use ODBC, bear in mind you're limited to VFP 6
versions of the database.
The other option is 'Free Table directory".
It would be easy for me to create a free table with updated values periodically
if perl could access it. (would be nice if perl
could write to it also)
Perl can read and write DBFs. Look for XBase in:
http://www.perl.com/CPAN/authors/Jan_Pazdziora/
I suspect that reads and writes DBFs directly, which may be
undesirable. It's also dated 2003 which either means it's bug-free or
abandoned. On the good side, it may be solid. On the down, it likely
doesn't support any features later than 2003.
Instead, if you want to try ODBC, take a look at:
http://www.roth.net/perl/odbc/
So, there's certainly a lot of options to consider.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
