DBI Problem

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

Replies are listed 'Best First'.
Re: DBI Problem by hippo (Archbishop) on Sep 29, 2017 at 08:07 UTC
You are not using placeholders, so without seeing the data who knows what the query is actually doing? Given previous wild goose chases, please provide an SSCCE to avoid such waste this time around. Thanks.	[reply]
Re^2: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 11:38 UTC
You say: You are not using placeholders, so without seeing the data who knows what the query is actually doing? The code for the query: `my $statement = "update $sql_user_table set $passpart $mailpart $secre +tpart $wherepart"; $SQL = qq\| $statement \|; warn ("SQL Statement: '$statement'"); $sth = ExecuteQuery($SQL);` [download] The error log shows: Fri Sep 29 05:59:35 2017 manage_users.cgi: SQL Statement: 'update users set password = 'briddle' where id = '428'' at /home/jalamior/www/httpsdocs/cgi-bin/lib/perl/manageusers.pm line 656. Thar error log entry shows what the data was.	[reply] [d/l]
Re^2: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 12:05 UTC
please provide an SSCCE to avoid such waste this time around I tried that with previous problems. When I tried to reduce to a small block of code the problems disappeared so it was not an example. And IIRC I just struggled through until stumbling on solution. The error message is pretty clear but I don't understand how to solve it. Fri Sep 29 05:59:36 2017 manage_users.cgi: (in cleanup) Can't connect to data source '' because I can't work out what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at /usr/local/lib/perl5/site_perl/5.8.8/CGI/Session/Driver/DBI.pm line 26 I guess that message is coming from DBI.pm.. `# extract dbi:driver prefix from $dsn into $1 $dsn =~ s/^dbi:(\w?)(?:$(.?)$)?://i or '' =~ /()/; # ensure $1 etc are empty if match fails my $driver_attrib_spec = $2 \|\| ''; # Set $driver. Old style driver, if specified, overrides new dsn s +tyle. $driver = $old_driver \|\| $1 \|\| $ENV{DBI_DRIVER} or Carp::croak("Can't connect to data source '$dsn' " ."because I can't work out what driver to use " ."(it doesn't seem to contain a 'dbi:driver:' prefix " ."and the DBI_DRIVER env var is not set)");` [download]	[reply] [d/l]
Re^3: DBI Problem by hippo (Archbishop) on Sep 29, 2017 at 12:46 UTC
When I tried to reduce to a small block of code the problems disappeared so it was not an example. That's precisely the point. If you cannot provide an SSCCE because the problem disappears then the problem is with your code and not with anything else. Your code is somehow (perhaps indirectly) calling DBI->connect without a proper DSN. That is the issue - it's up to you to find out where in your code this is occuring and fix it there.	[reply]
Re^4: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 14:23 UTC
Re: DBI Problem by huck (Prior) on Sep 28, 2017 at 23:28 UTC
There is just so much you are not telling us here In particular, what are you doing inside ExecuteQuery that involves a CGI::Session object? It does not seem to be initialized properly.	[reply]
Re^2: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 00:14 UTC
As I said there are two calls to executequery in the same sub routine. The first returns the correct response while the second fails with the error posted. open connection returns the dbi/dsn correctly `Open Connection-returning database handle: 'DBI::db=HASH(0x1e02880)' d +sn: 'dbi:mysql:database=jalamior_assoc_mgr;host=localhost' at /home/j +alamior/www/httpsdocs/cgi-bin/lib/perl/manageusers.pm line 152.` [download] Makes no sense to me why one should be Ok and the other fail. That is why I am here. Seems like a self contained problem to me. `sub ExecuteQuery { my ($SQL) = @_; my $sth = $dbh->prepare($SQL) \|\| ErrorMessage($SQL); $sth->execute() \|\| ErrorMessage($SQL); return $sth; }` [download]	[reply] [d/l] [select]
Re^3: DBI Problem by thanos1983 (Parson) on Sep 29, 2017 at 08:04 UTC
Hello tultalk, You need to debug step by step your operations. Insert errstr on prepare and execute and see the output. I do not think so the way that you are using it currently is working correctly. Sample of code untested: `my $sth = $dbh->prepare($SQL) or die $dbh->errstr; $sth->execute() or die $dbh->errstr; $dbh->commit or die $dbh->errstr;` [download] See also commit. You need also to check your call fetchrow_array since you are jumping from one note to the other. Sample of code untested: `while (($tsid, $userid) = $sth->fetchrow_array) { print "$tsid: $userid\n"; } # check for problems which may have terminated the fetch early die $sth->errstr if $sth->err;` [download] Hope this helps, BR. Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]
Re^4: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 11:30 UTC
Re^5: DBI Problem by thanos1983 (Parson) on Sep 29, 2017 at 12:05 UTC
Some notes below your chosen depth have not been shown here
Re^3: DBI Problem by huck (Prior) on Sep 29, 2017 at 02:13 UTC
Why are you so sure the error is at "note 2" rather than `$session = OpenSession($dbh,$tsid);`	[reply] [d/l]
Re^4: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 03:42 UTC
Re^3: DBI Problem by poj (Abbot) on Sep 29, 2017 at 10:30 UTC
Seems like a self contained problem to me. It would be if you didn't use a global `$dbh` in your subroutine. Where does the string `"(in cleanup)"` appear in your code. Is that the name of another subroutine ? poj	[reply] [d/l] [select]
Re^4: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 10:45 UTC
Re^5: DBI Problem by poj (Abbot) on Sep 29, 2017 at 19:57 UTC
Re: DBI Problem by huck (Prior) on Sep 29, 2017 at 20:26 UTC
`$session->flush();` https://rt.cpan.org/Public/Bug/Display.html?id=24355 https://sourceforge.net/p/cgi-session/mailman/message/31234011/ first google search found it	[reply] [d/l]
Re^2: DBI Problem by Anonymous Monk on Sep 29, 2017 at 22:40 UTC
Thanks for the links. You knew what to search for. I search for the "i can't work out... and did not find what you found. Sounds just like my problem. I inserted code before each of the two ExecuteQuery calls: `warn("1st call Connection-database handle: '$dbh' dsn: '$dsn'");` [download] and `warn("2nd call Connection-database handle: '$dbh' dsn: '$dsn'");` [download] And got the following in the error log: Fri Sep 29 17:28:23 2017 manage_users.cgi: 1st call Connection-database handle: 'DBI::db=HASH(0x2928270)' dsn: 'dbi:mysql:database=jalamior_assoc_mgr;host=localhost' at /home/jalamior/www/httpsdocs/cgi-bin/lib/perl/manageusers.pm line 625. Fri Sep 29 17:28:23 2017 manage_users.cgi: 2nd call connection-database handle: 'DBI::db=HASH(0x2928270)' dsn: 'dbi:mysql:database=jalamior_assoc_mgr;host=localhost' at /home/jalamior/www/httpsdocs/cgi-bin/lib/perl/manageusers.pm line 666. (in cleanup) Can't connect to data source '' because I can't work out what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at /usr/local/lib/perl5/site_perl/5.8.8/CGI/Session/Driver/DBI.pm line 26 I don't see where "it can't see the driver information.." So this must mean that something happens downstream in the call to executequery. Now back to square one as best I can see. I added $session->flush() with no effect. `sub ExecuteQuery { my ($SQL) = @_; my $sth = $dbh->prepare($SQL) or die $dbh->errstr; $sth->execute() or die $dbh->errstr; # $dbh->commit or die $dbh->errstr; # my $sth = $dbh->prepare($SQL) \|\| ErrorMessage($SQL); # $sth->execute() \|\| ErrorMessage($SQL); return $sth; $session->flush(); }` [download] Session opened for all routines in this .pm `sub OpenSession{ my ($dbh, $sid)= @_; $session = new CGI::Session("driver:MySQL", $sid, {Handle=>$dbh, Lo +ckHandle=>$dbh}); return $session; }` [download] Can you see a problem with these?	[reply] [d/l] [select]
Re^3: DBI Problem by huck (Prior) on Sep 30, 2017 at 00:07 UTC
Putting the flush after the return is less than useless i suspect the flush belongs in OpenSession It seems to be a problem in the destructor, when the database has already been closed but the session has not been flushed	[reply]
Re^4: DBI Problem by tultalk (Monk) on Sep 30, 2017 at 00:19 UTC
Re^5: DBI Problem by huck (Prior) on Sep 30, 2017 at 00:29 UTC
Some notes below your chosen depth have not been shown here
Re^5: DBI Problem by Anonymous Monk on Sep 30, 2017 at 00:27 UTC
Re^5: DBI Problem by tultalk (Monk) on Sep 30, 2017 at 00:25 UTC
Some notes below your chosen depth have not been shown here
Re: DBI Problem by Anonymous Monk on Sep 29, 2017 at 00:18 UTC
Hi, The code you posted has no code that calls CGI::Session But that is an old story Hi	[reply]
Re^2: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 01:45 UTC
By the way what is a Reaped xxxxxxxx	[reply]
Re^3: DBI Problem by davido (Cardinal) on Sep 29, 2017 at 05:36 UTC
You posted the same question in Reaped: DBI Problem, Reaped: DBI Problem, DBI Problem, and Reaped: DBI Problem. This multi-posting causes confusion to readers, and can also cause answers to accumulate in different threads without the benefit of building upon or contrasting with those answers already posted. Because it makes sense to have only one copy of a question alive out there, so that replies come in only within a single thread, the duplicates were "Reaped", which means they were hidden from common view. In the future you can avoid having nodes reaped by posting a question only once. If you need to edit it for some corrections there are ways to do that without creating duplicates (though it's usually best to just follow-up in the same thread). Dave	[reply]
Re^3: DBI Problem by AnomalousMonk (Archbishop) on Sep 29, 2017 at 05:47 UTC
tultalk: Further to davido's comments: Please see How do I change/delete my post? for site etiquette regarding changing one's post. The basic idea: Don't destroy context. Give a man a fish: `<%-{-{-{-<`	[reply] [d/l]
Re^2: DBI Problem by tultalk (Monk) on Sep 29, 2017 at 01:44 UTC
Yeah. I guess it is an old story. The call to the sub passes the session id from the session opened else where. No need to see that. If there was no session open then the first call to executequery at Note 1 would fail.	[reply]