Re: VFP 9 and OLE DB Connection String

Thanks for the info Steve.
I have tried the connection you suggested as well as the one
suggested at 
connectionstrings.com so I now have:
unless ($do = DBI->connect("Provider=vfpoledb.1;Data
Source=C:\OperaIIv5
\data\adata\Comp_A.dbc;Collating
Sequence=machine",$user,$password)) {
   print "Content-type: text/htmlnn";
   print "Error: couldn't connect to Opera II... Error
no:" . $DBI::err . " -
 " . $DBI::errstr . "<br>n";
   exit;
}
but get the error: Can't connect(Provider=vfpoledb.1;Data
Source=C:OperaIIv5
dataadataComp_A.dbc;Collating Sequence=machine  ), no
database driver 
specified and DBI_DSN env var not set
It took me a long time to get the ODBC connection working
and I found quite a 
bit of documentation on that but I find the documentation on
OLE DB to be a lot 
scarcer.

Phil

> ----- Original Message ----- 
> From: "Steve Howard (PFE)" <sthowardmicrosoft.com>
> To: <pbrewerukonline.co.uk>;
<perl-win32-databaselistserv.ActiveState.com>
> Sent: Wednesday, February 21, 2007 4:34 PM
> Subject: RE: VFP 9 and OLE DB Connection String
> 
> 
> Copying from http://www.connecti
onstrings.com/ (an excellent resource for 
> such things), the connection string would follow the
pattern of:
> 
> Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:folder;Extended 
> Properties=dBASE IV;User ID=Admin;Password=;
> 
> Hope this helps
> 
> -----Original Message-----
> From: perl-win32-database-bounceslistserv.ActiveState.com 
> [mailto:perl-win32-database-bounceslistserv.ActiveState.com] On Behalf Of 
> pbrewerukonline.co.uk
> Sent: Wednesday, February 21, 2007 3:17 AM
> To: perl-win32-databaselistserv.ActiveState.com
> Subject: VFP 9 and OLE DB Connection String
> 
> Hi
> 
> I have been using Perl to read/write databases/tables
in a third party
> application using DBD::ODBC and the Visual Foxpro 6
ODBC driver.
> 
> Everything works OK most of the time but I am running
into problems 
> (documented
> by Microsoft) with the VFP ODBC driver throwing up
connection errors every 
> so
> often. While a browser refresh or two overcomes the
problem this is not 
> really
> satisfactory. The connection string I was using:
> 
> $user = "";  $password = "";
> $dsno2 = "driver=Microsoft Visual FoxPro
> 
Driver;SourceDB=C:\OperaII\data\adata\Comp_A.dbc;SourceT
ype=DBC;Exclusive=No
> ";
> unless ($do =
DBI->connect("dbi:ODBC:$dsno2",$user,$password,
{ 
> odbc_cursortype
> => 2, ChopBlanks => true, LongReadLen =>
65536, LongTruncOk => 0 })) {
>    print "Content-type: text/htmlnn";
>    print "Error: couldn't connect to Opera II...
Error no:" . $DBI::err . 
> " -
>  " . $DBI::errstr . "<br>n";
>    exit;
> }
> 
> I have upgraded the third party application to the
latest version which now
> uses VFP 9, primarily to overcome the known problem
with the ODBC driver. 
> Can
> anyone help with an OLE DB connection string, please?
> 
> Any assistance would be greatly appreciated.
> 
> Regards
> Phil Brewer
> 
> 
> ----------------------------------------------
> This mail sent through http://www.ukonline.net
> _______________________________________________
> Perl-Win32-Database mailing list
> Perl-Win32-Databaselistserv.ActiveState.com
> To unsubscribe: http:/
/listserv.ActiveState.com/mailman/mysubs
> 


----------------------------------------------
This mail sent through http://www.ukonline.net

----- End forwarded message -----




----------------------------------------------
This mail sent through http://www.ukonline.net
_______________________________________________
Perl-Win32-Database mailing list
Perl-Win32-Databaselistserv.ActiveState.com
To unsubscribe: http:/
/listserv.ActiveState.com/mailman/mysubs

I might have misunderstood what you were asking ... I
actually thought you wanted to start using Win32::ADO.

To connect and use DBI as you wanted, you need to be sure
DBD::ADO is installed. Then, I think your connection string
should be modified to add the DBI:ADO: in the front so that
the connect method knows what libraries to use. In that cse
it should be:

$do = DBI->Connect("DBI:ADO:Provider=vfpoledb.1;Data
Source=C:\OperaIIv5\data\adata\Comp_A.dbc;Collating
Sequence=machine",$user,$password)

I don't have foxpro here to test that, but that should be
the format. The error you received is telling you the
DBI:ADO: is missing.

If you want to use ADO (which is built on OLEDB or SQL
Native Client, but is much easier to use), then here is a
basic script to give you the idea of how to use Win32::OLE
with ADO to make a quick query. This example is using SQL
2005, but you can change the connection string putting the
provider, and the rest of the connection string where they
should go, and after that, it should work the same with
foxpro as it does with SQL server. So change the lines:

$conn-> = "SQLNCLI";

To

$conn-> = " vfpoledb.1";

And change

$conn->Open("Data Source=.;Initial
Catalog=SQLRAP;Integrated Security=SSPI");

To

$conn->Open("Data
Source=C:\OperaIIv5\data\adata\Comp_A.dbc;Collating
Sequence=machine;User ID=$user;Password=$password");

(or change the providers, etc. to match what is in
connectionstrings.com).

Anyway, here is the working SQL 2005 example to give you an
idea of how to use it. It's not quite as easy as DBI, but
it's not really difficult:

#!perl

use Win32;
use Win32::OLE qw( in with);

*error = *Win32::OLE::LastError;

# Create a connection object
our $conn = Win32::OLE->new('ADODB.Connection');
die error() if error();

# Create a command object to use for queries.
our $cmd = Win32::OLE->new('ADODB.Command');
die error() if error();

# set the provider for the connection
$conn-> = "SQLNCLI";

# open the connection. This is where all but the provider
should go.
# sometimes, the provider can go in this string instead of
above.
# this is the area where the connection string goes.

$conn->Open("Data Source=.;Initial
Catalog=SQLRAP;Integrated Security=SSPI");
die error() if error();


# we have to associate the command object with a connection
# in order to be able to use it.
$cmd-> = $conn;

# set the sql query text.
$cmd-> = "select * from
sysobjects";
die error() if error();

# execute the command object and get the result set to work
with.
our $rs = $cmd->execute();
die error() if error();

# prime to the first row in the result set.
$rs->MoveFirst();

while (! $rs->eof())
{
        # get the value stored in the 'name' column
        print $rs->fields('name')-> .
"n";
        $rs->MoveNext();
}





-----Original Message-----
From: pbrewerukonline.co.uk [mailto:pbrewerukonline.co.uk]
Sent: Thursday, February 22, 2007 7:34 AM
To: perl-win32-databaselistserv.ActiveState.com
Cc: Steve Howard (PFE)
Subject: Re: VFP 9 and OLE DB Connection String

Thanks for the info Steve.
I have tried the connection you suggested as well as the one
suggested at
connectionstrings.com so I now have:
unless ($do = DBI->connect("Provider=vfpoledb.1;Data
Source=C:\OperaIIv5
\data\adata\Comp_A.dbc;Collating
Sequence=machine",$user,$password)) {
   print "Content-type: text/htmlnn";
   print "Error: couldn't connect to Opera II... Error
no:" . $DBI::err . " -
 " . $DBI::errstr . "<br>n";
   exit;
}
but get the error: Can't connect(Provider=vfpoledb.1;Data
Source=C:OperaIIv5
dataadataComp_A.dbc;Collating Sequence=machine  ), no
database driver
specified and DBI_DSN env var not set
It took me a long time to get the ODBC connection working
and I found quite a
bit of documentation on that but I find the documentation on
OLE DB to be a lot
scarcer.

Phil

> ----- Original Message -----
> From: "Steve Howard (PFE)" <sthowardmicrosoft.com>
> To: <pbrewerukonline.co.uk>;
<perl-win32-databaselistserv.ActiveState.com>
> Sent: Wednesday, February 21, 2007 4:34 PM
> Subject: RE: VFP 9 and OLE DB Connection String
>
>
> Copying from http://www.connecti
onstrings.com/ (an excellent resource for
> such things), the connection string would follow the
pattern of:
>
> Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:folder;Extended
> Properties=dBASE IV;User ID=Admin;Password=;
>
> Hope this helps
>
> -----Original Message-----
> From: perl-win32-database-bounceslistserv.ActiveState.com
> [mailto:perl-win32-database-bounceslistserv.ActiveState.com] On Behalf Of
> pbrewerukonline.co.uk
> Sent: Wednesday, February 21, 2007 3:17 AM
> To: perl-win32-databaselistserv.ActiveState.com
> Subject: VFP 9 and OLE DB Connection String
>
> Hi
>
> I have been using Perl to read/write databases/tables
in a third party
> application using DBD::ODBC and the Visual Foxpro 6
ODBC driver.
>
> Everything works OK most of the time but I am running
into problems
> (documented
> by Microsoft) with the VFP ODBC driver throwing up
connection errors every
> so
> often. While a browser refresh or two overcomes the
problem this is not
> really
> satisfactory. The connection string I was using:
>
> $user = "";  $password = "";
> $dsno2 = "driver=Microsoft Visual FoxPro
>
Driver;SourceDB=C:\OperaII\data\adata\Comp_A.dbc;SourceT
ype=DBC;Exclusive=No
> ";
> unless ($do =
DBI->connect("dbi:ODBC:$dsno2",$user,$password,
{
> odbc_cursortype
> => 2, ChopBlanks => true, LongReadLen =>
65536, LongTruncOk => 0 })) {
>    print "Content-type: text/htmlnn";
>    print "Error: couldn't connect to Opera II...
Error no:" . $DBI::err .
> " -
>  " . $DBI::errstr . "<br>n";
>    exit;
> }
>
> I have upgraded the third party application to the
latest version which now
> uses VFP 9, primarily to overcome the known problem
with the ODBC driver.
> Can
> anyone help with an OLE DB connection string, please?
>
> Any assistance would be greatly appreciated.
>
> Regards
> Phil Brewer
>
>
> ----------------------------------------------
> This mail sent through http://www.ukonline.net
> _______________________________________________
> Perl-Win32-Database mailing list
> Perl-Win32-Databaselistserv.ActiveState.com
> To unsubscribe: http:/
/listserv.ActiveState.com/mailman/mysubs
>


----------------------------------------------
This mail sent through http://www.ukonline.net

----- End forwarded message -----




----------------------------------------------
This mail sent through http://www.ukonline.net
_______________________________________________
Perl-Win32-Database mailing list
Perl-Win32-Databaselistserv.ActiveState.com
To unsubscribe: http:/
/listserv.ActiveState.com/mailman/mysubs

Thanks very much for all your help. Had a few niggly
problems but everything 
seems to be working now.

Phil

----- Original Message ----- 
From: "Steve Howard (PFE)" <sthowardmicrosoft.com>
To: <pbrewerukonline.co.uk>;
<perl-win32-databaselistserv.ActiveState.com>
Sent: Thursday, February 22, 2007 5:42 PM
Subject: RE: VFP 9 and OLE DB Connection String


I might have misunderstood what you were asking ... I
actually thought you 
wanted to start using Win32::ADO.

To connect and use DBI as you wanted, you need to be sure
DBD::ADO is 
installed. Then, I think your connection string should be
modified to add 
the DBI:ADO: in the front so that the connect method knows
what libraries to 
use. In that cse it should be:

$do = DBI->Connect("DBI:ADO:Provider=vfpoledb.1;Data

Source=C:\OperaIIv5\data\adata\Comp_A.dbc;Collating 
Sequence=machine",$user,$password)

I don't have foxpro here to test that, but that should be
the format. The 
error you received is telling you the DBI:ADO: is missing.

If you want to use ADO (which is built on OLEDB or SQL
Native Client, but is 
much easier to use), then here is a basic script to give you
the idea of how 
to use Win32::OLE with ADO to make a quick query. This
example is using SQL 
2005, but you can change the connection string putting the
provider, and the 
rest of the connection string where they should go, and
after that, it 
should work the same with foxpro as it does with SQL server.
So change the 
lines:

$conn-> = "SQLNCLI";

To

$conn-> = " vfpoledb.1";

And change

$conn->Open("Data Source=.;Initial
Catalog=SQLRAP;Integrated 
Security=SSPI");

To

$conn->Open("Data
Source=C:\OperaIIv5\data\adata\Comp_A.dbc;Collating 
Sequence=machine;User ID=$user;Password=$password");

(or change the providers, etc. to match what is in
connectionstrings.com).

Anyway, here is the working SQL 2005 example to give you an
idea of how to 
use it. It's not quite as easy as DBI, but it's not really
difficult:

#!perl

use Win32;
use Win32::OLE qw( in with);

*error = *Win32::OLE::LastError;

# Create a connection object
our $conn = Win32::OLE->new('ADODB.Connection');
die error() if error();

# Create a command object to use for queries.
our $cmd = Win32::OLE->new('ADODB.Command');
die error() if error();

# set the provider for the connection
$conn-> = "SQLNCLI";

# open the connection. This is where all but the provider
should go.
# sometimes, the provider can go in this string instead of
above.
# this is the area where the connection string goes.

$conn->Open("Data Source=.;Initial
Catalog=SQLRAP;Integrated 
Security=SSPI");
die error() if error();


# we have to associate the command object with a connection
# in order to be able to use it.
$cmd-> = $conn;

# set the sql query text.
$cmd-> = "select * from
sysobjects";
die error() if error();

# execute the command object and get the result set to work
with.
our $rs = $cmd->execute();
die error() if error();

# prime to the first row in the result set.
$rs->MoveFirst();

while (! $rs->eof())
{
        # get the value stored in the 'name' column
        print $rs->fields('name')-> .
"n";
        $rs->MoveNext();
}





-----Original Message-----
From: pbrewerukonline.co.uk [mailto:pbrewerukonline.co.uk]
Sent: Thursday, February 22, 2007 7:34 AM
To: perl-win32-databaselistserv.ActiveState.com
Cc: Steve Howard (PFE)
Subject: Re: VFP 9 and OLE DB Connection String

Thanks for the info Steve.
I have tried the connection you suggested as well as the one
suggested at
connectionstrings.com so I now have:
unless ($do = DBI->connect("Provider=vfpoledb.1;Data
Source=C:\OperaIIv5
\data\adata\Comp_A.dbc;Collating
Sequence=machine",$user,$password)) {
   print "Content-type: text/htmlnn";
   print "Error: couldn't connect to Opera II... Error
no:" . $DBI::err . 
" -
 " . $DBI::errstr . "<br>n";
   exit;
}
but get the error: Can't connect(Provider=vfpoledb.1;Data 
Source=C:OperaIIv5
dataadataComp_A.dbc;Collating Sequence=machine  ), no
database driver
specified and DBI_DSN env var not set
It took me a long time to get the ODBC connection working
and I found quite 
a
bit of documentation on that but I find the documentation on
OLE DB to be a 
lot
scarcer.

Phil

> ----- Original Message -----
> From: "Steve Howard (PFE)" <sthowardmicrosoft.com>
> To: <pbrewerukonline.co.uk>; 
> <perl-win32-databaselistserv.ActiveState.com>
> Sent: Wednesday, February 21, 2007 4:34 PM
> Subject: RE: VFP 9 and OLE DB Connection String
>
>
> Copying from http://www.connecti
onstrings.com/ (an excellent resource for
> such things), the connection string would follow the
pattern of:
>
> Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:folder;Extended
> Properties=dBASE IV;User ID=Admin;Password=;
>
> Hope this helps
>
> -----Original Message-----
> From: perl-win32-database-bounceslistserv.ActiveState.com
> [mailto:perl-win32-database-bounceslistserv.ActiveState.com] On Behalf Of
> pbrewerukonline.co.uk
> Sent: Wednesday, February 21, 2007 3:17 AM
> To: perl-win32-databaselistserv.ActiveState.com
> Subject: VFP 9 and OLE DB Connection String
>
> Hi
>
> I have been using Perl to read/write databases/tables
in a third party
> application using DBD::ODBC and the Visual Foxpro 6
ODBC driver.
>
> Everything works OK most of the time but I am running
into problems
> (documented
> by Microsoft) with the VFP ODBC driver throwing up
connection errors every
> so
> often. While a browser refresh or two overcomes the
problem this is not
> really
> satisfactory. The connection string I was using:
>
> $user = "";  $password = "";
> $dsno2 = "driver=Microsoft Visual FoxPro
>
Driver;SourceDB=C:\OperaII\data\adata\Comp_A.dbc;SourceT
ype=DBC;Exclusive=No
> ";
> unless ($do =
DBI->connect("dbi:ODBC:$dsno2",$user,$password,
{
> odbc_cursortype
> => 2, ChopBlanks => true, LongReadLen =>
65536, LongTruncOk => 0 })) {
>    print "Content-type: text/htmlnn";
>    print "Error: couldn't connect to Opera II...
Error no:" . $DBI::err .
> " -
>  " . $DBI::errstr . "<br>n";
>    exit;
> }
>
> I have upgraded the third party application to the
latest version which 
> now
> uses VFP 9, primarily to overcome the known problem
with the ODBC driver.
> Can
> anyone help with an OLE DB connection string, please?
>
> Any assistance would be greatly appreciated.
>
> Regards
> Phil Brewer
>
>
> ----------------------------------------------
> This mail sent through http://www.ukonline.net
> _______________________________________________
> Perl-Win32-Database mailing list
> Perl-Win32-Databaselistserv.ActiveState.com
> To unsubscribe: http:/
/listserv.ActiveState.com/mailman/mysubs
>


----------------------------------------------
This mail sent through http://www.ukonline.net

----- End forwarded message -----




----------------------------------------------
This mail sent through http://www.ukonline.net

_______________________________________________
Perl-Win32-Database mailing list
Perl-Win32-Databaselistserv.ActiveState.com
To unsubscribe: http:/
/listserv.ActiveState.com/mailman/mysubs