problem with ODBC 1

[ferm]

Hello,

I'm using perl + win32::ODBC and my application freezes sometimes blocking the data base(SQL Server)!

Somebody knows how to prevent these freezes or detect when the application is frozen?

I tried to use alarm but doesn't work with windows. Or to put a timeout to the odbc handler, but I don't found out how to do this!

Thank you for your help!

Ferm

 

[Rieekan]

Personally, I'd suggest switching the application over to the DBI module and it's DBD::ODBC port. It's ODBC support is much superior to Win32::ODBC in most aspects.

If you'd rather not make the change, in the short term, it might be necessary to add significant logging to your script so you can see where it's "freezing". Also, verify the queries passing through it to ensure they're not trying to join tables together with no relationships. If your database is large enough, this could tie up the connection for a while due to it returning a ton of bad data.

- George

 

[1DMF]

I use win32:ODBC without any problems, although i do get a timeout now and again, but this is more the ODBC driver killing the connection rather than the script.

I use a file DSN and pass it the connection timeout and command timeout, but it doesn't seem to make any difference.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[ferm]

Rieekan: I'm going to try the DBD::ODBC to evaluate if it will easy to replace the win32 version!

But which is the problem of joining tables with no relationships??? the tables that I use have sometimes a primary key...but the concept of foreign key I think was unknown to the person who creates the database!

1DMFo you know how to set a timeout? did you set the timeout via the data source creation or the perl code? any way can you explain me how to set it?

Thank you for your help!

Ferm

 

[1DMF]

I set the timeout via the DSN (I use a file DSN to connect)

Here is the file

[ODBC]
DRIVER=SQL Server
CommandTimeout=900
ConnectionTimeout=900
ReadTimeout=900
DATABASE=Database_Name
SERVER=Server_IP
DESCRIPTION=Database_Derscription
UID=UserID
PWD=Password

Hope it helps

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[ferm]

The use of a File DSN is the same that the user DSN? because
I created the File DSN but from perl was impossible to open
it! (and the test connection of the ODBC dialog was ok)

 

[1DMF]

? don't know why you can't open a file DSN with PERL, that's how I do it.

here's the code...

# Open DB Connection
my $DSN = "/full path to file dsn/filename.dsn";
my $db = new Win32::ODBC("FILEDSN=$DSN;") || die "getSQL Error Connecting: " . Win32::ODBC::Error();

but remember PERL must have permissions to access the folder where the file DSN resides as it needs to create internal buffers and temporary files for it to work.


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[ferm]

Thank you for your quick answer! in fact my problem was that I used 'dsn=' instead of 'filedsn='

 

[1DMF]

glad I could help - have fun

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.