Re: App stuck on executeStoredProcedure()
Re: App stuck on executeStoredProcedure()
- Subject: Re: App stuck on executeStoredProcedure()
- From: Chuck Hill <email@hidden>
- Date: Thu, 31 Jan 2008 10:38:21 -0800
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
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 (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