First things first. When someone suggests something to you on this
list, especially
when they suggest it more than once, you should try it. I missed this
before, but I'm
remembering now an earlier suggestion by Michael Chase - when you
notice an error
in a dbi statement, print out $DBI::errstr - something like this:
my($sth) = $::dbh->prepare($sql) or &Error("Unable to prepare
statement: $DBI::errstr.");
and you should be sure to do that on every dbi statement (connect,
prepare, execute, etc.).
That will go a long way towards helping to figure out what is wrong.
Along the lines of trying suggestions made to you, did you try my
suggestion of doing
your UPDATE using a command line client of your database? I don't
remember you
saying which database you are using.
I have *no* experience with stored procedures so I can't help debug
that part - I have
no idea why the code would be executed twice.
Hardy Merrill
>>> "Moreno, Javier" <[EMAIL PROTECTED]> 03/11/05 12:11 PM >>>
Ok. So I moved away from the UPDATE and now I am running a stored
procedure. When I look at the trace, the stored procedure runs exactly
as expected, however, it then runs a second time and screws things up.
You can see on the below trace what I mean. I believe this is what is
clobbering the value on the database. But why would it happen? Please
see code below and then trace.
DBI->trace(1,"log_file.log");
$sql = "EXEC [OEEMGR].[dbo].[_Update_Global_Settings] '$lang_code',
'$old_lang_code'";
my($sth) = $::dbh->prepare($sql) or &Error("Unable to prepare
statement.");
$ref = $sth->execute or &Error("Unable to execute statement", $sql);
######## End Code
DBI 1.46-ithread default trace level set to 0x0/1 (pid 3352)
<- prepare('EXEC [OEEMGR].[dbo].[_Update_Global_Settings] 'SP',
'EN'')= ( DBI::st=HASH(0x19c71d4) ) [1 items] at OEEMGR_Lang.pl line
128
<- execute= 1 at OEEMGR_Lang.pl line 129
<- disconnect= 1 at OEEMGR_Lang.pl line 171
<- DESTROY(DBI::st=HASH(19c71d4))= undef
<- disconnect_all= '' at DBI.pm line 671
! <- DESTROY(DBI::db=HASH(1988be0))= undef during global destruction
! <- DESTROY(DBI::dr=HASH(18f3ae0))= undef during global destruction
DBI 1.46-ithread default trace level set to 0x0/1 (pid 2872)
<- prepare('EXEC [OEEMGR].[dbo].[_Update_Global_Settings] '',
'SP'')= ( DBI::st=HASH(0x19c57c0) ) [1 items] at OEEMGR_Lang.pl line
128
<- execute= 1 at OEEMGR_Lang.pl line 129
<- disconnect= 1 at OEEMGR_Lang.pl line 171
<- DESTROY(DBI::st=HASH(19c57c0))= undef
<- disconnect_all= '' at DBI.pm line 671
! <- DESTROY(DBI::db=HASH(17cae1c))= undef during global destruction
! <- DESTROY(DBI::dr=HASH(18f5680))= undef during global destruction
-----Original Message-----
From: Hardy Merrill [mailto:[EMAIL PROTECTED]
Sent: Friday, March 11, 2005 8:34 AM
To: Moreno, Javier
Cc: dbi-users@perl.org
Subject: RE: Value deletion
I'm not familiar enough with trace output to know exactly what's
happening there, but I'm guessing that the UPDATE is the problem.
When you do an UPDATE, don't you *have* to have a WHERE
clause?
I see you're using ODBC - not sure if you've done this yet, or if you
have an ODBC client (or client for your database) you can use to
execute SQL at a client command prompt, but if I were using Oracle I
would use the "sqlplus" Oracle client to try doing
UPDATE GLOBALSettings SET Lang = 'EN'
at the client command prompt.
If that works, then you know your problem is with DBI/DBD::ODBC. If
that doesn't work, the client will give you some kind of SQL error, so
you
know your problem is with the SQL.
I'm wondering if the UPDATE is actually happening. See if you can
find
a way to test that UPDATE not using Perl / DBI / DBD::ODBC.
HTH.
Hardy Merrill
>>> "Moreno, Javier" <[EMAIL PROTECTED]> 03/10/05 5:16 PM >>>
The trace was a good idea. However I do not understand the log very
much. There is an error there but there's also an error on my next
select which basically was to test the update, see if it fetched.
DBI 1.46-ithread default trace level set to 0x0/4 (pid 2804)
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x19a40d8)~0x19c5694
'UPDATE GLOBALSettings SET Lang = ?') thr#15d4374
New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x19c5694),
id=)
dbih_setup_handle(DBI::st=HASH(0x19c5784)=>DBI::st=HASH(0x1974888),
DBD::ODBC::st, 19c5790, Null!)
dbih_make_com(DBI::db=HASH(0x19c5694), 19c5c54, DBD::ODBC::st,
208,
0) thr#15d4374
dbd_preparse scanned 1 distinct placeholders
SQLPrepare returned 0
dbd_st_prepare'd sql f28514864, ExecDirect=0
UPDATE GLOBALSettings SET Lang = ?
<- prepare= DBI::st=HASH(0x19c5784) at OEEMGR_Lang.pl line 116
-> execute for DBD::ODBC::st (DBI::st=HASH(0x19c5784)~0x1974888
'EN') thr#15d4374
bind 1 <== 'EN' (attribs: ), type 0
SQLDescribeParam idx = 1.
dbd_st_execute (outparams = 0)...
bind 1 <== 'EN' (size 2/3/0, ptype 4, otype 1, sqltype 1)
bind 1 <== 'EN' (len 2/2, null 0)
bind 1: CTy=1, STy=CHAR, CD=2, Sc=2, VM=2.
dbd_st_execute (for hstmt 28514864 before)...
dbd_error: err_rc=0 rc=0 s/d/e: 28514864/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/0/28512928
dbd_describe sql 28514864: num_fields=0
dbd_describe skipped (no result cols) (sql f28514864)
dbd_st_execute got no rows: resetting ACTIVE, moreResults
<- execute= 1 at OEEMGR_Lang.pl line 117
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x19a40d8)~0x19c5694
'SELECT Lang FROM GLOBALSettings') thr#15d4374
New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x19c5694),
id=)
dbih_setup_handle(DBI::st=HASH(0x19c582c)=>DBI::st=HASH(0x19c585c),
DBD::ODBC::st, 19c5838, Null!)
dbih_make_com(DBI::db=HASH(0x19c5694), 19c5c54, DBD::ODBC::st,
208,
0) thr#15d4374
SQLPrepare returned 0
dbd_st_prepare'd sql f28516032, ExecDirect=0
SELECT Lang FROM GLOBALSettings
<- prepare= DBI::st=HASH(0x19c582c) at Functions.pl line 182 via
C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/OEEMGR/OEEMGR_Lang.pl
line
121
-> execute for DBD::ODBC::st (DBI::st=HASH(0x19c582c)~0x19c585c)
thr#15d4374
dbd_st_execute (outparams = 0)...
dbd_st_execute (for hstmt 28516032 before)...
dbd_error: err_rc=0 rc=0 s/d/e: 28516032/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/0/28512928
dbd_describe sql 28516032: num_fields=1
col 1: CHAR (1) len= 2 disp= 3, prec= 2 scale=0
col 1: 'Lang' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=3, (dp =
0,
cp = 0)
<- execute= -1 at Functions.pl line 191 via
C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/OEEMGR/OEEMGR_Lang.pl
line
121
-> fetchall_arrayref for DBD::ODBC::st
(DBI::st=HASH(0x19c582c)~0x19c585c) thr#15d4374
SQLFetch rc 0
dbih_setup_fbav for 1 fields => 0x19c88a0
fetch num_fields=1
fetch col#0 Lang datalen=2 displ=3
SQLFetch rc 100
<- fetchall_arrayref= [ ARRAY(0x19c8900) ] row1 at Functions.pl
line 196 via
C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/OEEMGR/OEEMGR_Lang.pl
line
121
<> DESTROY(DBI::st=HASH(0x19c582c)) ignored for outer handle
(inner
DBI::st=HASH(0x19c585c) has ref cnt 1)
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x19c585c)~INNER)
thr#15d4374
<- DESTROY= undef at OEEMGR_Lang.pl line 121
dbih_clearcom 0x19c582c (com 0x19c941c, type 3) done.
-> disconnect for DBD::ODBC::db
(DBI::db=HASH(0x19a40d8)~0x19c5694)
thr#15d4374
<- disconnect= 1 at OEEMGR_Lang.pl line 163
-- DBI::END
-> disconnect_all for DBD::ODBC::dr
(DBI::dr=HASH(0x18f3a70)~0x19a4108) thr#15d4374
<- disconnect_all= '' at DBI.pm line 671 via
C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/OEEMGR/OEEMGR_Lang.pl
line
0
! -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x19c5694)~INNER)
thr#15d4374
! <- DESTROY= undef during global destruction
dbih_clearcom 0x19a40d8 (com 0x19c5c54, type 2) done.
! -> DESTROY in DBD::_::common for DBD::ODBC::dr
(DBI::dr=HASH(0x19a4108)~INNER) thr#15d4374
! <- DESTROY= undef during global destruction
dbih_clearcom 0x18f3a70 (com 0x19a5054, type 1) done.
! -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1974888)~INNER)
thr#15d4374
! <- DESTROY= undef during global destruction
dbih_clearcom 0x19c5784 (com 0x19c8794, type 3) done.
! <> DESTROY for DBI::st=HASH(0x19c5784) ignored (inner handle gone)
! <> DESTROY for DBI::db=HASH(0x19a40d8) ignored (inner handle gone)
! <> DESTROY for DBI::dr=HASH(0x18f3a70) ignored (inner handle gone)
-----Original Message-----
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 10, 2005 1:52 PM
To: Moreno, Javier
Cc: dbi-users@perl.org
Subject: RE: Value deletion
Quoting "Moreno, Javier" <[EMAIL PROTECTED]>:
> Actually it is now getting worse. It has AutoCommit set to 0 so I am
doing
> this:
>
> $::sql = "UPDATE GLOBALSettings SET Lang = ?";
> $::crt = $::lang_code;
> &Debug("About to prepare: $::sql with criteria: $::crt");
> $::sth = $::dbh->prepare($::sql) or &Error("Unable to prepare
statement.");
> $::rec = $::sth->execute ($::crt) or &Error("Unable to execute
statement");
> $::dbh->commit;
As I recommended before, please add $DBI::errstr to your error
messages,
otherwise you get no clues about what went wrong.
> But it is now writing a <NULL> value on the DB. That &Debug line
shows:
>
> About to prepare: UPDATE GLOBALSettings SET Lang = ? with criteria:
SP
>
> So the value is fine but it is not updating right. What I found was
that the
> second field on the SQL table was set to no nulls so the update was
failing
> on the webserver log because of that. BTW responding to the "app
bombs"
> ambiguity, I use the value for an auto META REFRESH value to find
out
the
> directory of the language I am running.
The problem may be in placeholder handling in the driver. Add
`DBI->trace( 4,
"log_file.log");` (without the outer quotes) before prepare() to get
more
information about what is happening. Higher numbers up to about 9
give
you
more details.
http://search.cpan.org/~timb/DBI/DBI.pm#TRACING
http://search.cpan.org/~timb/DBI/DBI.pm#trace
http://search.cpan.org/~timb/DBI/DBI.pm#Why_doesn't_my_CGI_script_work_right?
--
Mac :})
