WIN32::ODBC - Can't Open Connection

[1DMF]

Hi,

I am trying to connect to our remote SQL Server, via our webspace (i.e. not on the same machine), I am able to create the DSN but when trying to open the DB connection I get a strange message about Microsoft Access.

Error connecting: [-1023] [] "[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."

? i'm not trying to connect to MS Access, any ideas on why this is hapenning, I can connect from our webhost to the same server using ASP fine, just not with PERL, am I missing an attribute somewhere? Thanks 1DMF,

...code is below.....

#!/usr/bin/perl

# Show Errors to Browser
use CGI::Carp qw(fatalsToBrowser);

# Use Win32
use Win32::ODBC;

# Set Variables
my $DSN = "Website";
my $Driver = "SQL Server";
my $Desc = "DSN DESCRIPTION";
my $Server = "SERVER NAME";
my $Dbase = "DATABASE NAME";
my $Network = "DBMSSOCN";
my $UID = "USERID";
my $PWD = "PASSWORD";
my $IP = "IP ADDRESS OF REMOTE SQL SERVER";

# Create DataSource
if (Win32::ODBC::ConfigDSN(
ODBC_ADD_DSN,
"$Driver",
("DSN=$DSN", # DSN Name
"DESCRIPTION=$Desc", # Description
"SERVER=$Server", # server name
"ADDRESS=$IP", # server IP addr
"DATABASE=$Dbase", # database
"NETWORK=$Network" # TCP/IP Socket Lib
))){print "DSN Created\n";}
else { die "Unable to create DSN:" . Win32::ODBC::Error( ) . "\n"; exit(); }

# Open DB Connection
$db = new Win32::ODBC("DSN=$DSN;UID=$UID;PWD=$PWD;") || die "Error connecting: " . Win32::ODBC::Error();

exit();

 

[PaulTEG]

Have you got a DSN Website on the local machine that's pointing to an Access database?
--Paul

cigless ...

 

[1DMF]

upon checking there is already a "website", i asume as it's windows hosting it isn't "Case Sensetive" so mine being "Website" doesn't work.

This was obviously a coincidence, I have changed the DSN to something unique and get this error

Error connecting: [911] [] "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Now what is confusing me is if i run the create DSN part i get the message back saying DSN Created - BUT .....

when i use

# Check for DSN and delete if exists
my (%dsn,$key) = Win32::ODBC:ataSources();

foreach $key (sort keys %dsn) {
print "\n$key<br>";
}

to check the new DSN does not show - does this mean our web host are blocking the creation of DSN's and if so is there another way to get round this, and why is the ADD DSN returning an OK flag ?

How about a file DSN I upload to the web area, would that work and can you help in the syntax etc...

Regards,

1DMF

 

[PaulTEG]

It depends on what sort of DSN is being created, and how it can be accessed. Try setting up a File DSN to point to your SQL Server, and point to that instead

Let us know how you get on

--Paul

cigless ...

 

[1DMF]

Man it's taken me all day to research but I got there in the end with the following code...

Firstly a FILE DSN (FILENAME.dsn) uploaded to a 'private' area of the website so it cannot be viewed on it's own.....
=====================================================
[ODBC]
DRIVER=SQL Server
DATABASE=NAME OF SQL DATABASE
SERVER=I.P. OF REMOTE SQL SERVER
DESCRIPTION=DATABASE DESCRIPTION
======================================================

Then the following code to connect and display the data .....

#!/usr/bin/perl

# Show Errors to Browser
use CGI::Carp qw(fatalsToBrowser);

# Use Win32
use Win32::ODBC;

# Set Variables
my $DSN = "ABSOLUTE PATH TO FILE ON SERVER / FILE NAME.dsn";
my $UID = "USER ID";
my $PWD = "PASSWORD";

# Open DB Connection
$db = new Win32::ODBC("FILEDSN=$DSN;UID=$UID;PWD=$PWD;") || die "Error connecting: " . Win32::ODBC::Error();

# Display SQL Data
if( ! $db->Sql( "SELECT * FROM [TABLE NAME]" ) ) {
while( $db->FetchRow() ) {
%Data = $db->DataHash();
@key_entries = keys(%Data);
$Row++;
print "\n$Row)";
foreach $key ( keys( %Data ) )
{
print "\t'$key' = '$Data{$key}'\n";
}
}
}
else
{
print "\nUnable to execute query: " . $db->Error() . "\n";
}

# Close DB Connection
$db->Close();

##########################

Phew I got there in the end and it works like a dream!, well for reading the SQL, any ideas on how you record lock for updates? - 1DMF

 

[mlibeson]

I would look into using the DBI and DBD::ODBC modules for your data handling.


Michael Libeson

 

[PaulTEG]

Are you sure you need to lock, how many updates to existing records are you expecting?

IIRC the table/database has to created with locking options, or at least have them enabled on the database table.

You can have some fun if you're not sure exactly what you're doing, pessimistic locking crashing cascading transactions

Google, or I think there might be a SQLServer forum here

HTH
--Paul

cigless ...

 

[1DMF]

I cannot use DBI because my host refuses to install it, hence using, WIN32::ODBC, any way using SQL is better than the current flat files we use

Hey PaulTEG , thanks for the encouragement, i spend all day with what felt like an impossible mission, to finally succeed and you have to pee on my bonfire

Oh what fun i'm about to have by the sounds of it.... Look out for more desperate posts in the near future. lol

Regards,

1DMF

 

[PaulTEG]

You can look into the use lib directive to install DBI locally to your installation

HTH
--Paul

Sorry about the bonfire ;-)

cigless ...

 

[1DMF]

I'm a bit concerned why everyone keeps saying use DBI, is there a problem with win32::ODBC, my understanding was it was a faster more lightweight API for ODBC conectivity.

It is also rolled out as STANDARD with ActiveState PERL not DBI, if Win32::ODBC works and is a standard PERL module, whats all the fuss about ?

 

[tchatzi]

You can go to from London to the US through Japan. Nobody said you never gonna get there.(although you might not!!!)

But it takes less time and less fuss if you go straight.

You will understand what i'm talking about if you deside to use Perl with DBI. Especialy when it comes to Apache(or any other) and CGI or ModPerl.

TIMTOWTDI

 

[PaulTEG]

The benefit is yours, because you're not just locking yourself into MS Access (which costs money), but you can migrate to MySQL which is free (unsupported), but well enough supported in many forums, as well as any other (at least most other) DBMS.

Usually cheaper to host with MySQL that SQLServer, or MSAccess, and dependability of Access has been a issue in the past.

No one is saying you have to, they're just giving you a heads up on how to best improve your perl armory with respect to databases

The choice as they say is yours, but it's not something you need to do starightaway

Cheers
--Paul

cigless ...

 

[1DMF]

Cool, I appreciate the advice, as I say I'm using this method purely because DBI isn't installed and we are about to move (one of the reasons out of many), to a host that does the whole shooting match, even ColdFusion support!

I'm a bit confused over the "locking yourself into MS-Access" comment, I'm not using MS-Access, I thought ODBC was a standadised interface to most database types including Informix, Oracle & Sysbase.

Can anyone help with the syntax for the coding, a swift move from Win32::ODBC to DBI:BD would really be appreciated. The code I posted in the above posts work great for Win32, is it much the same just changing the "use" statement and other references to win32, or is that wishful thinking.

I look forward to your comments.

 

[PaulTEG]

ODBC is just a layer similar to DBI, but I'd be thinking that there's more development, and testing, in the DBI paradigm, because of the amount of use.

I made the "Lock-In" comment based on what I saw, and apologise wholeheartedly if this is not the case, and that's just because I'm a lazy fecker, and didn't double check before posting
;-)

The DBI is fairly straightforward, and not dissimilar to the Win32::ODBC

--Paul


cigless ...

 

[1DMF]

Hey Paul, I dunno "look before you leap" - I spend all that time writing my posts, researching the subject and then you don't bother to read it before posting - lol

Any chance you could help with my other post ?

http://www.tek-tips.com/viewthread.cfm?qid=1029737&page=1

Regards,

1DMF