Board index » delphi » debug stored procedures

debug stored procedures


2008-05-20 05:29:50 AM
delphi47
Hello,
How I can debug stored procedures called by a trigger from inside my
application ?
Thanks
 
 

Re:debug stored procedures

Insert debugging information into some table.
Isaac writes:
Quote
Hello,
How I can debug stored procedures called by a trigger from inside my
application ?

Thanks
 

Re:debug stored procedures

another question please,
why when I sum a value with a variable hold a null value the result
evaluate to null not the first value ??
this mess a lot with my calculations when a select return <null>all my
calculation evaluate also to null how I can avoid this ???
Thanks
 

Re:debug stored procedures

sql server has isnull() function but interbase did not has it , I really
did not want to use alot of if to test for null I hope also not to us UDF!!
 

Re:debug stored procedures

update:
I get this error:
Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
when I try to execute this SQL
select COALESCE(sum(QTY),0), COALESCE(sum(QTY * COST_PRICE),0) from
purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
into :pur_sum_qty, :pur_sum_cost;
I successfully compiled the procedure but I get the error at run time !!
I also tried to make like this sum(COALESCE(QTY,0)) but I was not able
to even compile
please advice
 

Re:debug stored procedures

Isaac writes:
Quote
why when I sum a value with a variable hold a null value the result
evaluate to null not the first value ??
Because the SQL standard says that null + anything is null. Null is not
a value. Null is a state indicating that there is no value. All SQL
compliant databases work this way.
--
Bill Todd (TeamB)
 

Re:debug stored procedures

InterBase has NULLIF, CASE and COALESCE.
--
Bill Todd (TeamB)
 

Re:debug stored procedures

Bill Todd [TeamB] writes:
Quote
InterBase has NULLIF, CASE and COALESCE.

well I tried COALESCE but I get error message
Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
please see my last post in this thread
Thanks alot
 

Re:debug stored procedures

Your last post in this thread is:
"sql server has isnull() function but interbase did not has it , I
really did not want to use alot of if to test for null I hope also not
to us UDF!!"
which I understood to mean that you are looking for a way to do what
ISNULL does in Transact SQL. In my reply I provided the alternatives
that are available in IB. Now you are telling us that COALESCE gave you
an error but you do not show us the code that caused the error. No one
here can read your mind. If you want help you must provide enough
information.
--
Bill Todd (TeamB)
 

Re:debug stored procedures

Bill Todd [TeamB] writes:
Quote
Your last post in this thread is:

"sql server has isnull() function but interbase did not has it , I
really did not want to use alot of if to test for null I hope also not
to us UDF!!"

which I understood to mean that you are looking for a way to do what
ISNULL does in Transact SQL. In my reply I provided the alternatives
that are available in IB. Now you are telling us that COALESCE gave you
an error but you do not show us the code that caused the error. No one
here can read your mind. If you want help you must provide enough
information.

I am crying here, always misunderstand me
here is my last post again:
update:
I get this error:
Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
when I try to execute this SQL
select COALESCE(sum(QTY),0), COALESCE(sum(QTY * COST_PRICE),0) from
purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
into :pur_sum_qty, :pur_sum_cost;
I successfully compiled the procedure but I get the error at run time !!
I also tried to make like this sum(COALESCE(QTY,0)) but I was not able
to even compile
please advice
 

Re:debug stored procedures

Sorry. I did not scroll down far enough to see that you had replied to
one of your earlier messages.
Apparently you cannot use COALESCE in two SUM aggregates in the same
SELECT. Try running
select COALESCE(sum(QTY),0) from purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
in isql after replacing the parameters with literal values and see if
you still get an error.
--
Bill Todd (TeamB)
 

Re:debug stored procedures

Bill Todd [TeamB] writes:
Quote
Sorry. I did not scroll down far enough to see that you had replied to
one of your earlier messages.

Apparently you cannot use COALESCE in two SUM aggregates in the same
SELECT. Try running

select COALESCE(sum(QTY),0) from purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)

in isql after replacing the parameters with literal values and see if
you still get an error.

news sql
select COALESCE(sum(QTY),0) from purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
into :pur_sum_qty;
result: same error -902 !! the error also appear without using sum at
all !!! is that a bug ?
 

Re:debug stored procedures

Isaac writes:
Quote
is that a bug ?
I do not know since I do not know what caused the error. Can you post
the actual SQL statement that you ran in isql?
--
Bill Todd (TeamB)
 

Re:debug stored procedures

Isaac writes:
Quote
select COALESCE(sum(QTY),0) from purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code) where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
into :pur_sum_qty;
There's not really a need to use COALESCE here. Since you're doing
this in a context of a stored procedure, you can check against NULL
after the query executes.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Borland newsgroup denizen Sergio González has a new CD of
Irish music out, and it is good: tinyurl.com/7hgfr
 

Re:debug stored procedures

Isaac writes:
Quote
select COALESCE(sum(QTY),0) from purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code) where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
I posted the wrong SQL. The SQL should have been:
select SUM(COALESCE(QTY),0)) from purchase_inv_item
left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
where (purchase_inv.purch_return <>1)
and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
Again, make sure that you replace the two parameters with actual values
before you try to execute it using isql.
--
Bill Todd (TeamB)