Re: [AOLSERVER] turn database verbosity on/off?
Here is my db_exec function. The subst happens after the database call so
the database sees the bind variables but developer support sees the values.
It's kludgy and is easily fooled but works 99+% of the time. I'm sure it
could be done better but I think this runs fast ( I use this in production
). Picking up the dbms_output is helpfull also since you can output from
large plsql functions and see the stuff at the bottom of your page.
Otherwise debuging large plsql blocks is nearly impossible.
barry
ad_proc -private db_exec { type db statement_name sql args } {
A helper procedure to execute a SQL statement, potentially binding
depending on the value of the $bind variable in the calling environment
(if set).
} {
set start_time [clock clicks]
set out
catch {
set out [ns_ora exec_plsql $db {
declare
status number;
linevarchar2(4000);
ret varchar2(32000);
begin
status := 0;
ret := ' ';
while status = 0 loop
dbms_output.get_line(line,status);
if ( status = 0 ) then
ret := ret || line || 'br';
end if;
end loop;
:1 := ret;
end;
} ]
} err
if { $out != } {
ds_comment dbmsOutput: $out
}
ns_ora dml $db {
begin
dbms_output.enable(10);
end;
}
set vars test
set errno [catch {
upvar bind bind
if { [info exists bind] [llength $bind] != 0 } {
if { [llength $bind] == 1 } {
append vars $bind
return [eval [list ns_ora $type $db -bind $bind $sql] $args]
} else {
set bind_vars [ns_set create]
foreach { name value } $bind {
ns_set put $bind_vars $name $value
append vars $name=$value
}
return [eval [list ns_ora $type $db -bind $bind_vars $sql]
$args]
}
} else {
append vars none
return [uplevel 2 [list ns_ora $type $db $sql] $args]
}
} error]
regsub -all {:([a-zA-z])} $sql {$\1} sql
catch {
set sql [uplevel 2 [list subst $sql]]
} err
ad_call_proc_if_exists ds_collect_db_call $db $type $statement_name $sql
$start_time $errno $error
if { $errno == 2 } {
return $error
}
global errorInfo errorCode
return -code $errno -errorinfo $errorInfo -errorcode $errorCode $error
}
-Original Message-
From: Andrew Piskorski [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 29, 2002 8:53 AM
To: [EMAIL PROTECTED]
Subject: Re: [AOLSERVER] turn database verbosity on/off?
On Tue, May 28, 2002 at 03:59:55AM -0500, Barry Books wrote:
I patched the ACS database routines somthing like
catch {
regsub {:} $sql {$} sql
set sql [subst $sql]
} err
The regsub makes the bind variables TCL variables and subst replaces them
with the value. The catch fixes dumb problems like where a = ':abc'. This
goes right before the developer support call in the db routines. I also
patched them so you can do dbms_output and capture it. I can dig up the
exact patch later if you need it.
Barry, so you effectively eliminated all use of bind variables? That
doesn't seem like a good idea - I WANT to use bind variables. I'd
just like some convenient way of including the values of the bind vars
in any error message. Perhaps there's a simpler way, but I suspect
I'd need to add that to the database driver (Oracle in this case).
--
Andrew Piskorski [EMAIL PROTECTED]
http://www.piskorski.com
Re: [AOLSERVER] turn database verbosity on/off?
I patched the ACS database routines somthing like
catch {
regsub {:} $sql {$} sql
set sql [subst $sql]
} err
The regsub makes the bind variables TCL variables and subst replaces them
with the value. The catch fixes dumb problems like where a = ':abc'. This
goes right before the developer support call in the db routines. I also
patched them so you can do dbms_output and capture it. I can dig up the
exact patch later if you need it.
barry
-Original Message-
From: Andrew Piskorski [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 27, 2002 6:36 PM
To: [EMAIL PROTECTED]
Subject: Re: [AOLSERVER] turn database verbosity on/off?
On Mon, May 27, 2002 at 06:49:58PM -0400, Peter M. Jansson wrote:
ns_db verbose usually works for me, on a per-pool basis.
Oh, right, I forgot aobout that. No per statement setting though,
bummer.
Also, keep in mind that the LogSQLErrors parameter tells the
database pool manager to log an SQL statement that generates an
error return, even of verbose is off. In production, I'd usually
recommend that you run with verbose off and LogSQLErrors on.
Yes, that's definitely an improvement, thanks. Unfortunately, when
using the ACS db-api the values of the bind variables do not show up
in the error message, and with verbose turned off, they of course
never show up in the log at all. Maybe there's some way to get the
bind variable stuff into the error message, but I haven't looked into
it.
On Mon, 27 May 2002, Andrew Piskorski wrote:
Is there any way to turn the Verbose parameter setting for a
database pool on or off on a per-query or per handle basis? Or to
change it on the fly after server startup in any fashion?
--
Andrew Piskorski [EMAIL PROTECTED]
http://www.piskorski.com
Re: [AOLSERVER] turn database verbosity on/off?
ns_db verbose usually works for me, on a per-pool basis. Also, keep in mind that the LogSQLErrors parameter tells the database pool manager to log an SQL statement that generates an error return, even of verbose is off. In production, I'd usually recommend that you run with verbose off and LogSQLErrors on. On Mon, 27 May 2002, Andrew Piskorski wrote: Is there any way to turn the Verbose parameter setting for a database pool on or off on a per-query or per handle basis? Or to change it on the fly after server startup in any fashion?
Re: [AOLSERVER] turn database verbosity on/off?
On Mon, May 27, 2002 at 06:49:58PM -0400, Peter M. Jansson wrote: ns_db verbose usually works for me, on a per-pool basis. Oh, right, I forgot aobout that. No per statement setting though, bummer. Also, keep in mind that the LogSQLErrors parameter tells the database pool manager to log an SQL statement that generates an error return, even of verbose is off. In production, I'd usually recommend that you run with verbose off and LogSQLErrors on. Yes, that's definitely an improvement, thanks. Unfortunately, when using the ACS db-api the values of the bind variables do not show up in the error message, and with verbose turned off, they of course never show up in the log at all. Maybe there's some way to get the bind variable stuff into the error message, but I haven't looked into it. On Mon, 27 May 2002, Andrew Piskorski wrote: Is there any way to turn the Verbose parameter setting for a database pool on or off on a per-query or per handle basis? Or to change it on the fly after server startup in any fashion? -- Andrew Piskorski [EMAIL PROTECTED] http://www.piskorski.com
