Stephen Toney has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks:

I am having trouble with a new installation of MyODBC and MySQL 5.0 under Windows, used with two different Perl CGI apps (using CGI.pm, of course). Every ODBC database request hangs the server until it times out.

I think the MySQL is set up correctly, since it tests correctly from the ODBC Admin, and I can perform queries on it using Foxpro as a frontend using ODBC. I can also logon to mysql.exe as the 'ODBC' user and perform queries.

So it boils down to something in the Perl apps. It's not the specific queries, because I've tested them with mysql.exe and also using SQL Server and Foxpro. Also, I've studied the SQL.LOG after running the Perl CGI app, and it appears that all connections and queries performed correctly. It's just that no results appear in the browser, and then the server times out.

Here's my connection string: 
$::dbh = DBI->connect ("dbi:ODBC:$dsn");

[download]
I've tried every combination of the connection I can think of, including adding the user "ODBC", the password "", NULL, etc.

Any ideas would be greatly appreciated!

Stephen

Replies are listed 'Best First'.
Re: MySQL ODBC under Windows hangs
by marto (Cardinal) on Nov 16, 2005 at 15:31 UTC
    Hi Stephen Toney,

    Should you not provide the username and password when connecting?
    Like $dbh = DBI->connect('dbi:ODBC:DSN', 'user', 'password');
    If when you examine the SQL log queries are running as you would expect, please post your code so that someone may point out why it is not displaying the results.

    Cheers.

    Martin
[reply]
[d/l]
      Sorry I wasn't clear about that. The "ODBC" user has no password, and I have tried a connection strings like 
      $::dbh = DBI->connect ("dbi:ODBC:$dsn", "ODBC");
$::dbh = DBI->connect ("dbi:ODBC:$dsn", "ODBC", "");
$::dbh = DBI->connect ("dbi:ODBC:$dsn", "ODBC", NULL);

      [download]
      My impression was that if the user "ODBC" is set in the DSN, which it is, then it is not required in the connection string. If it is required, then maybe my problem is that I'm not sure how to enter the password when no password is required. Maybe I should try assigning the ODBC user a password and then I would know the syntax.

      Thanks for replying!

      Stephen
[reply]
[d/l]
Re: MySQL ODBC under Windows hangs
by vek (Prior) on Nov 17, 2005 at 06:22 UTC
    Also, I've studied the SQL.LOG after running the Perl CGI app, and it appears that all connections and queries performed correctly. It's just that no results appear in the browser, and then the server times out.

    If you've verified in the logs that the connections and queries from Perl are ok then I don't think your connection string is the problem. Have you tried dumping the query results to a file to verify that the query is indeed working and you are getting the expected data returned? Could you post some more Perl code so we can see how you are gathering the query results?

    -- vek --
[reply]
      vek -- thanks for the reply.

      Here's a tiny program the shows the problem. This snippet works with SQL Server and Foxpro db's using ODBC. 
      #! /usr/local/bin/perl -w

use strict;

use CGI qw(:standard escapeHTML);

$::DSN = "MWEBMYSQL";

use DBI;
$::dbh = DBI->connect ("dbi:ODBC:$::DSN", "ODBC") || die ("Cannot conn
+ect to Data Source '$::DSN'");

my ($count) = $::dbh->selectrow_array ("select count(*) from logon");

print header ('text/html', '200 OK');

print "<html><body>Found $count records in LOGON</body></html>";

      [download]
      When I run this from the command line (instead of as a web app), the correct results are shown, but the cursor stays right after </html> and I never get back to a command prompt.



      "marto" suggested (thanks!) that I should use DBD::MySQL. However, the latest on the ActiveState website is version 2.1004, November 16, 2001. Has anyone tried this with MySQL 5.0 yet?

      Thanks a million for any ideas!

      Stephen
[reply]
[d/l]

Back to Seekers of Perl Wisdom