(new to Perl) How do I build a conn.string to SQL Server 7.0 2

[logo]

I am new to Perl and need to build a connection to SQL Server 7. Can I do this through the DBI module or do I need to go about it some other way?

 

[goBoating]

not very easily....

If you are running your Perl on a Win box, you can setup an ODBC
data source on that box and then use the Perl DBI::ODBC approach.

If you are running your Perl on a *nix box, then you might try
FreeTDS. See -

http://www.freetds.org

They have a faqs page that will
give something to start on. We did it here. It is something of
a pain, but, when you get it working you get basic SQL abilities.

There are some commercial products available to do this trick, but
they are expensive. You can use google or other to search for
those.

Good Luck Please use descriptive titles and check the FAQs.
And, beware the evil typo.

 

[logo]

The link,

http://www.freetds.org,

is not working....perhaps they are having server issues I will be running my script from a unix box.

Have you ever used myODBC. I have now been notified that I need to build a connection to both SQL Server 7 and Access 2000. I'm assuming that these connections will be very similiar.

 

[goBoating]

I have never tried myODBC so I can't comment on it.

About connecting to SQL SRVR7 and Access........ What a pain.
You can get into an Access file using OLE from Perl, but, it is also a pain. ??Why use Access at all?? If you have SQL Server available, I would just create the structure you need in the SQL Server and blow the Access content into it.

I just tried

http://www.freetds.org

and it worked. Like you said, maybe they were having a temporary problem. Good luck with this stuff.

Please use descriptive titles and check the FAQs.
And, beware the evil typo.

 

[Tve]

Hi,

I've been looking into this lately, and the Win32::OLE module works fine for me: This is an example of a sub that builds a SQL statement for an Access 97 database and builds a hash:

[tt]
use Win32::OLE ('in');
use Win32::OLE::Const ('Microsoft ActiveX Data Objects 2.5');

%hash = &List_Alias("Whatever"


sub List_Alias {

# Get the passed value
my $arg = shift;

# Define the connection string
my $dbfile = 'U:\batch.mdb';
my $rsActiveConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$dbfile;";

# Concatenate the sql
my $sql = "SELECT tblFields.strAlias,tblFields.strName FROM tblDef INNER JOIN tblFields ON tblDef.ID = tblFields.intTable WHERE (tblDef.strName='$arg');";

# Open the recordset
$rst->Open($sql,$rsActiveConn,adOpenForwardOnly,adLockReadOnly,adCmdText) || scanForErrors();

# Get the complete table as a string
my $string = $rst->GetString(adClipString, undef, " ", " "

# Save the file in a hash
my %ref = split(/ /, $string);

# Close recordset
$rst->Close();

# Return the hash
return %ref;

}
[/tt]

I've done some cutting and pasting from my codes and since I don't have Perl on my current station, I haven't tested it but it should work.

If you're interested in this method, give me a shout and I'll give some more explanations/examples (looping on records,...).

Thierry

 

[logo]

Thanks Thierry,

You've got me headed in a good direction! I'm new to perl, so every useful example I can work my way through sure helps. I've taken your example and adapted it a bit (although not much at all):

#!/usr/bin/perl

use Win32::OLE ('in');
use Win32::OLE::Const ('Microsoft ActiveX Data Objects 2.5');

%hash = &List_Alias("Whatever"


sub List_Alias {

# Get the passed value
my $arg = shift;

# Define the connection string
my $dbfile = 'C:\log\Firm57 Phone Log\Phone_Log_Data.mdb';
my $rsActiveConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$dbfile;";

# Concatenate the sql
my $sql = "SELECT * from Phone_Log_Data";

# Open the recordset
$rst->Open($sql,$rsActiveConn,adOpenForwardOnly,adLockReadOnly,adCmdText);

# Get the complete table as a string
my $string = $rst->GetString(adClipString, undef, " ", " "

# Save the file in a hash
my %ref = split(/ /, $string);

# Close recordset
$rst->Close();

# Return the hash
return %ref;

}
exit;



I'm getting an error trying to open the recordset:

C:\dev\Perl Script\mySQL>perl test.pl
Can't call method "Open" on an undefined value at test.pl line 22.

Any idea(s) as to which part of this statement is undefined?

Thanks again,

 

[logo]

Thierry,

I just have to say thanks again...I'm still working on building direct connection to SQL Server 7 and ACCESS 2000, but you sure pointed me in the right direction. As I mentioned before I'm new to Perl, but I have a good bit of experience with VB and I just realized that I can use the Win32::OLE to access my VB classes. You made my day!!

 

[Tve]

Logo,

Sorry, about the connection problem. As I said, I couldn't test it. You're missing the connection to the database.

Add this line before the ->Open()

# Create the recordset object
$rst = Win32::OLE->new("ADODB.Recordset"

I'll do some testing later today...

Thierry

 

[logo]

Thanks Thierry,

It works great! You've really helped me out! If you have any other examples that you'd like to send my way. . . I'd love to look through them.

 

[Tve]

Here's on a last example: it returns you a list recordset of tables.

What's interesting is to note the OpenShema which lets you extract metadata such as tables, fields, ... as well as the loop which can be used with as recordsets.
[tt]

# Create connection object
$conn = Win32::OLE->new("ADODB.Connection"

# Define the database to connec to
$dbfile = 'U:\batch.mdb';
$rsActiveConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$dbfile;";

# Open a connection
$conn->Open($rsActiveConn);

# Open a record set available tables
$rst = $conn->OpenSchema(adSchemaTables,[ undef, undef,undef,'TABLE']);

# Declare variable
my %hash = ();

while (! $rst->{EOF} ) {

# Get the table name and type
my $name = $rst->Fields('TABLE_NAME')->{Value};
my $type = $rst->Fields('TABLE_TYPE')->{Value};

# Let's filter out system tables, ....
$hash{$name} = $type;

# Move to the next
$rst->MoveNext;
}

# Close the recorset
$rst->Close();

[/tt]

There's also a perlscript newsgroup (I don't have it at home, but if you're interested, I'll send you the link tomorrow). Perlscript basically uses the same syntax as Perl, but is meant for ASP.

You could also look here for a few examples (the group is longer active, but it does contain some good examples):

http://groups.google.com/groups?hl=en&group=deja.comm.perlscript

Thierry