Re: App stuck on executeStoredProcedure()
Re: App stuck on executeStoredProcedure()
- Subject: Re: App stuck on executeStoredProcedure()
- From: David Avendasora <email@hidden>
- Date: Thu, 31 Jan 2008 14:33:52 -0500
On Jan 31, 2008, at 1:38 PM, Chuck Hill wrote:
On Jan 31, 2008, at 8:58 AM, David Avendasora wrote:
Okay, I got it working by ripping out all the WO code and just
directly calling the DB.
I don't want to leave it this way, I want it to go through WO, use
the Stored Procedure defined in the model. Can anyone point me to
where I might be able to dig into the code and get it to work
correctly?
I don't use SP, but... I think that JDBCChannel is where this
happens. I don't see anything in the JDBCPlugin to custimize
this. I'd go back to where it hangs, get a thread dump, and see
what the stack trace for the hung thread is. That might point to
the problem. Or it might not. :-)
Chuck
How do I get a thread dump?
On Jan 31, 2008, at 8:00 AM, David Avendasora wrote:
Okay, under further investigation into SQL Server JDBC drivers, I
found this about the SQL 2005 JDBC Driver on Microsoft's website:
http://msdn2.microsoft.com/en-us/library/ms378108.aspx
So, now I know how this _should_ work, but it doesn't look like
WO is doing it right. Can I and if so, how do I override what WO
is doing here?
Dave
On Jan 31, 2008, at 6:29 AM, David Avendasora wrote:
No, I get an error, and it's a pure Java SQL client that uses
the same JDBC driver as SQL Server (In /Library/Java/Extensions).
MS Query Analyzer doesn't see it as a valid command but the
Online Books for SQL Server has the following about CALL:
How to call stored procedures (ODBC)
When a SQL statement calls a stored procedure using the ODBC
CALL escape clause, the Microsoft® SQL Server™ driver sends the
procedure to SQL Server using the remote stored procedure call
(RPC) mechanism. RPC requests bypass much of the statement
parsing and parameter processing in SQL Server and are faster
than using the Transact-SQL EXECUTE statement.
To run a procedure as an RPC
Construct a SQL statement that uses the ODBC CALL escape
sequence. The statement uses parameter markers for each input,
input/output, and output parameter, and for the procedure return
value (if any):
{? = CALL procname (?,?)}
Call SQLBindParameter for each input, input/output, and output
parameter, and for the procedure return value (if any).
Execute the statement with SQLExecDirect.
Note If an application submits a procedure using the Transact-
SQL EXECUTE syntax (as opposed to the ODBC CALL escape
sequence), the SQL Server ODBC driver passes the procedure call
to SQL Server as a SQL statement rather than as an RPC. Also,
output parameters are not returned if the Transact-SQL EXECUTE
statement is used.
Which seems to say that even if CALL could work, the statement
is still wrong because the output parameters should be before
the CALL.
Dave
On Jan 30, 2008, at 6:44 PM, Chuck Hill wrote:
Can you do a call from that tool?
On Jan 30, 2008, at 3:39 PM, David Avendasora wrote:
Here's what run, and get back in Aqua Data Studio:
DECLARE @1 char(11),
@2 char(255),
@3 int
EXEC [10.10.10.8].TEST.dbo.ott_spInventoryAdjustment
@BatchID = '31041',
@ItemNumber = '02070000000011',
@QTY = 286.000000,
@SiteID = 'WAREHOUSE',
@Classification = 'Production',
@outIVDocNum = @1 OUTPUT,
@outErrMsg = @2 OUTPUT,
@outErrStatus = @3 OUTPUT
SELECT @1, @2, @3
column1
column2
column3
----------
-----------------------------------------------------------------
-----------------------------------------------------------------
-----------------------------------------------------------------
------------------------------------------------------------
----------
(null) Classification was passed in but has not been
setup in the ott_ItemClassification
table
1
1 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms]
[Executed: 1/30/08 5:24:18 PM EST ] [Execution: 61/ms]
On Jan 30, 2008, at 6:22 PM, David Avendasora wrote:
Yep, it runs fine. In the log it says "call" instead of
"EXEC" or "EXECUTE" but I don't know if the log is supposed
to reflect the exact SQL sent to the server or not.
Dave
On Jan 30, 2008, at 6:03 PM, Chuck Hill wrote:
Does the SP run outside of WO (e.g. from one of the MS
tools)? Can you get any information out of the MS tools?
Does it get to the database? Does it complete?
Chuck
On Jan 30, 2008, at 2:31 PM, David Avendasora wrote:
Hi all,
I'm still having a problem with calling a Stored Procedure
on a MS SQL DB. I have some additional information since I
turned on SQL Logging. It just freezes after calling the
Stored Procedure. No errors.
Here's the method:
public void sendToInventoryManagement() {
log.debug("sendToInventoryManagement() called");
NSLog.allowDebugLoggingForGroups(0x10000L);
EOEditingContext ec = this.editingContext();
if (componentPart().partType() == PartType.fetchOneRaw
(ec)) {
/*
@BatchID CHAR(21),
@ItemNumber CHAR(31), -- GP Item Number
@QTY NUMERIC(19,5), -- Item Decrease Quantity
@SiteID CHAR(11), -- Location Code/Site for the item.
Ex. WAREHOUSE
@Classification CHAR(15) = '', -- Optional Item
Classification. When these are passed in it looks at
the ott_ItemClassficiation table to see what the
offset account number should be
@outIVDocNum CHAR(17) OUTPUT, -- Inventory Adjustment
document number generated from GP. THis field is outputted
to the caller.
@outErrStatus int = 0 output,
@outErrMsg varchar(255) = '' output
*/
String siteId = "WAREHOUSE";
String batchId;
if (usedInManufacturedBatch() != null) {
batchId = EOUtilities.primaryKeyForObject
(ec,usedInManufacturedBatch()).valueForKey
("lotCodeId").toString();
} else {
batchId = "BMPartUsage";
}
NSMutableDictionary inputArgs = new NSMutableDictionary();
inputArgs.takeValueForKey(batchId, "@BatchID");
inputArgs.takeValueForKey(componentPart().partNumber
(),"@ItemNumber");
inputArgs.takeValueForKey(componentQuantity(), "@QTY");
inputArgs.takeValueForKey(siteId, "@SiteID");
inputArgs.takeValueForKey(partUsageType().typeName
(),"@Classification");
inputArgs.takeValueForKey(null,"@outIVDocNum");
inputArgs.takeValueForKey(null,"@outErrStatus");
inputArgs.takeValueForKey(null,"@outErrMsg");
log.debug("About to EXEC
[10.10.10.8].TEST.dbo.ott_spInventoryAdjustment" + "," +
"\n@BatchID = " + inputArgs.valueForKey("@BatchID") +
"," +
"\n@ItemNumber = " + inputArgs.valueForKey
("@ItemNumber") + "," +
"\n@QTY = " + inputArgs.valueForKey("@QTY") + "," +
"\n@SiteID = " + inputArgs.valueForKey("@SiteID") +
"," +
"\n@Classification = " + inputArgs.valueForKey
("@Classification") + "," +
"\n@outIVDocNum = " + inputArgs.valueForKey
("@outIVDocNum") + "," +
"\n@outErrStatus = " + inputArgs.valueForKey
("@outErrStatus") + "," +
"\n@outErrMsg = " + inputArgs.valueForKey("@outErrMsg")
);
NSDictionary outputArgs =
EOUtilities.executeStoredProcedureNamed(ec,
"SendToInventoryManagement", inputArgs);
log.debug("Returned From SP Call.");
setPartUsageStatusRelationship
(PartUsageStatus.fetchOneSentToInventoryManagement(ec));
setGpIvDocumentNumber((String) outputArgs.valueForKey
("@outIVDocNum"));
setGpErrorStatus((Integer) outputArgs.valueForKey
("@outErrStatus"));
setGpErrorMessage((String) outputArgs.valueForKey
("@outErrMsg"));
ec.saveChanges();
}
NSLog.refuseDebugLoggingForGroups(0x10000L);
}
Here's the run Log:
[2008-01-30 17:09:20 EST] <main> Waiting for requests...
[2008-01-30 17:10:15,592] <WorkerThread0>
PartUsage.sendToInventoryManagement -
sendToInventoryManagement() called
[2008-01-30 17:10:15 EST] <WorkerThread0> === Begin
Internal Transaction
[2008-01-30 17:10:15 EST] <WorkerThread0>
evaluateExpression:
<com.webobjects.jdbcadaptor.MicrosoftPlugIn
$MicrosoftExpression: "SELECT t0.Part_Numbering_Code,
t0.Part_Type_ID, t0.Part_Type_Name FROM dbo.Part_Type t0
WHERE t0.Part_Type_ID = ?" withBindings: 1:2(partTypeId)>
[2008-01-30 17:10:15 EST] <WorkerThread0> 1 row(s) processed
[2008-01-30 17:10:15 EST] <WorkerThread0> === Commit
Internal Transaction
[2008-01-30 17:10:15,616] <WorkerThread0>
PartUsage.sendToInventoryManagement - About to EXEC
[10.10.10.8].TEST.dbo.ott_spInventoryAdjustment,
@BatchID = 31041,
@ItemNumber = 02070000000011,
@QTY = 286.000000,
@SiteID = WAREHOUSE,
@Classification = Production,
@outIVDocNum = null,
@outErrStatus = null,
@outErrMsg = null
[2008-01-30 17:10:15 EST] <WorkerThread0>
executeStoredProcedure: SendToInventoryManagement
withValues:{@Classification = "Production"; @SiteID =
"WAREHOUSE"; @ItemNumber = "02070000000011"; @BatchID =
"31041"; @QTY = 286.000000; }
[2008-01-30 17:10:15 EST] <WorkerThread0> === Begin
Internal Transaction
[2008-01-30 17:10:15 EST] <WorkerThread0>
evaluateExpression:
<com.webobjects.jdbcadaptor.MicrosoftPlugIn
$MicrosoftExpression: "{ call [10.10.10.8].[TEST].[dbo].
[ott_spInventoryAdjustment] (?, ?, ?, ?, ?, ?, ?, ?)}"
withBindings: 1:"Production"(@Classification),
2:"02070000000011"(@ItemNumber), 3:"31041"(@BatchID),
4:286.000000(@QTY),
5:<com.webobjects.foundation.NSKeyValueCoding$Null>
(@outIVDocNum), 6:"WAREHOUSE"(@SiteID),
7:<com.webobjects.foundation.NSKeyValueCoding$Null>
(@outErrMsg), 8:<com.webobjects.foundation.NSKeyValueCoding
$Null>(@outErrSatus)>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-
email@hidden)
Help/Unsubscribe/Update your Subscription:
40global-village.net
This email sent to email@hidden
--
Practical WebObjects - for developers who want to increase
their overall knowledge of WebObjects or who are trying to
solve specific problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-
email@hidden)
Help/Unsubscribe/Update your Subscription:
email@hidden
This email sent to email@hidden
--
Practical WebObjects - for developers who want to increase
their overall knowledge of WebObjects or who are trying to
solve specific problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40avendasora.com
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40global-village.net
This email sent to email@hidden
--
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden