[Caché ODBC][State : S1000][Native Code 460]

[Ohn Ic]

Hi all,

I'm attempting to call a stored procedure more than once.

The stored procedure works fine and does what it's suppose to do.

However, when i call the stored procedure a second time i get the below
error:
[Caché ODBC][State : S1000][Native Code 460]

I am calling the stored procedure through VB6 using the ADODB.Command
object.

Through further investigation i found that other stored procedures called
more than once did not produce this problem. I also attempted to re-create
the problem by calling the SQL statement instead of the stored procedure and
this also works fine.

I have also attempted to turn on the ODBC logging and nothing appears in the
CacheODBC.log

As i stated earlier, this stored procedure works the first time but produces
an error if it is attempted there after. If the application is shut down
after the first use then restarted it works fine.

Does anyone have any suggestions?

Regards,
Ohn Ic.

The code is below:

Stored proc
----------------------------------------
ClassMethod AddNewInv(tmpItemCode As %String = "", tmpShortDesc As %String =
"", tmpMainCatCode As %String = "", tmpIsKit As %Boolean = 0, tmpIsCount As
%Boolean = 0, tmpMinorCatCode As %String = "", tmpType As %String = "",
tmpImage As %String = "", tmpWeighting As %String = "", tmpProcessing As
%String = "", tmpTheatreNotes As %String = "", tmpMaterialCost As %String =
"", tmpQtyOnHand As %Integer = "", tmpSite As %Integer = "") As %String [
SqlProc ]
{
s $zt="LogErr"

; Validate variables for INSERT
i tmpItemCode=$c(0) s tmpItemCode=""
i tmpShortDesc=$c(0) s tmpShortDesc=""
i tmpMainCatCode=$c(0) s tmpMainCatCode=""
i tmpIsKit=$c(0) s tmpIsKit=0 ; boolean
i tmpIsCount=$c(0) s tmpIsCount=0 ; boolean
i tmpMinorCatCode=$c(0) s tmpMinorCatCode=""
i tmpType=$c(0) s tmpType=""
i tmpImage=$c(0) s tmpImage=""
i tmpWeighting=$c(0) s tmpWeighting=""
i tmpProcessing=$c(0) s tmpProcessing=""
i tmpTheatreNotes=$c(0) s tmpTheatreNotes=""
i tmpMaterialCost=$c(0) s tmpMaterialCost=""
i tmpQtyOnHand=$c(0) s tmpQtyOnHand=""
i tmpSite=$c(0) s tmpSite=""

; Insert new record
&sql(
insert into dbo.INVENTORY
(ivy_itemcode
,ivy_shortdesc
,ivy_maincatcode
,ivy_is_kit
,ivy_is_count
,ivy_minorcatcode
,ivy_type
,ivy_image
,ivy_weighting
,ivy_processing
,ivy_theatrenotes
,ivy_materialcost
,ivy_qty_onhand
,ivy_site)
values (:tmpItemCode
,:tmpShortDesc
,:tmpMainCatCode
,:tmpIsKit
,:tmpIsCount
,:tmpMinorCatCode
,:tmpType
,:tmpImage
,:tmpWeighting
,:tmpProcessing
,:tmpTheatreNotes
,:tmpMaterialCost
,:tmpQtyOnHand
,:tmpSite)
)
i 'SQLCODE {
; Return ivy_id of new entry
; Get ivy_id of recent addition
&sql(
select top 1 ivy_id
into :tmpID
from dbo.inventory
order by ivy_id desc
)
if 'SQLCODE {
q "1|"_tmpID ; Successful
}
else {
q "0|Select|SQL Error: "_SQLCODE
}
}
else {
q "0|Insert|SQL Error: "_SQLCODE
}

LogErr ; Error loging
set $zt=""
do ##class(Com.Utils).LogStoredProcedureError($ZE)
}



VB6
----------------------------------------
Private Sub Command1_Click()
Dim mbIsItem As Boolean
Dim tmpIvyIsCount As Boolean
Dim myString As String

Dim cmdObj As New ADODB.Command

cmdObj.ActiveConnection = aConn

tmpIvyIsCount = False
mbIsItem = True

cmdObj.ActiveConnection = aConn

cmdObj.CommandText = "? = Call
dbo.INVENTORY_AddNewInv(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cmdObj.CommandType = adCmdText

Call MakeParam(cmdObj, "retVal", adVarChar, adParamReturnValue, 10, "")
Call MakeParam(cmdObj, "ivy_itemcode", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_shortdesc", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_maincatcode", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_is_kit", adBoolean, adParamInput, 10,
IIf(mbIsItem, 0, 1))
Call MakeParam(cmdObj, "ivy_is_count", adBoolean, adParamInput, 10,
IIf(tmpIvyIsCount, 0, 1))
Call MakeParam(cmdObj, "ivy_minorcatcode", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_type", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_image", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_weighting", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_processing", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_theatrenotes", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_materialcost", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_qty_onhand", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_site", adVarChar, adParamInput, 10, "")

cmdObj.Execute

myString = cmdObj.Parameters(0).Value
End Sub

[Denver]

Ohn Ic wrote:
> i 'SQLCODE {
> ; Return ivy_id of new entry
> ; Get ivy_id of recent addition
> &sql(
> select top 1 ivy_id
> into :tmpID
> from dbo.inventory
> order by ivy_id desc
> )

Why do you specify ORDER BY?

If anything, you want to ORDER BY ivy_id.
I don't see why you need to order at all.
I believe TOP already implies ordering.


As for your problem, what happens if you do not s $zt="LogErr"?

[Ohn Ic]

Denver,

You are right. There is no need for the ORDER BY clause. Just careless.

I tried removing the 'S $ZT="LogErr"' but it still produced the error.

Below is a simplified VB6 app that shows how cmdButton 2 and 3 work without
erroring after being pressed several times. However, when cmdButton1 is
pressed on more than one occassion, it errors even though it worked on the
first click event.

Second stored procedure that works fine with multiple execution:
-----------------------------------------------------
ClassMethod TestConn(tmpBool As %Boolean = 0) As %String [ SqlProc ]
{
i tmpBool=1 {
q 0 ;
}
else {
q 1 ;
}
}


VB6
------
Option Explicit

Dim aConn As New ADODB.Connection

Private Sub Command1_Click()

Dim cmdObj As New ADODB.Command

cmdObj.ActiveConnection = aConn

cmdObj.CommandText = "? = Call
dbo.INVENTORY_AddNewInv(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cmdObj.CommandType = adCmdText

Call MakeParam(cmdObj, "retVal", adVarChar, adParamReturnValue, 10, "")
Call MakeParam(cmdObj, "ivy_itemcode", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_shortdesc", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_maincatcode", adVarChar, adParamInput, 10,
"")

Call MakeParam(cmdObj, "ivy_is_kit", adBoolean, adParamInput, 10, 1)
Call MakeParam(cmdObj, "ivy_is_count", adBoolean, adParamInput, 10, 0)

Call MakeParam(cmdObj, "ivy_minorcatcode", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_type", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_image", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_weighting", adVarChar, adParamInput, 10, "")
Call MakeParam(cmdObj, "ivy_processing", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_theatrenotes", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_materialcost", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_qty_onhand", adVarChar, adParamInput, 10,
"")
Call MakeParam(cmdObj, "ivy_site", adVarChar, adParamInput, 10, "")

cmdObj.Execute

Text1 = GetPiece(cmdObj.Parameters(0).Value, 1, "|")

Set cmdObj = Nothing
End Sub

Private Sub Command1_LostFocus()
Text1.Text = ""
End Sub

Private Sub Command2_Click()

Dim cmdObj As New ADODB.Command

cmdObj.ActiveConnection = aConn

cmdObj.CommandText = "? = Call Com.Utils_TestConn(?)"

cmdObj.CommandType = adCmdText

Call MakeParam(cmdObj, "retVal", adVarChar, adParamReturnValue, 10, "")

Call MakeParam(cmdObj, "retVal", adBoolean, adParamInput, 10, 0)

cmdObj.Execute

Text1 = GetPiece(cmdObj.Parameters(0).Value, 1, "|")

Set cmdObj = Nothing
End Sub

Private Sub Command2_LostFocus()
Text1.Text = ""
End Sub

Private Sub Command3_Click()
Dim sqlStr As String

Dim cmdObj As New ADODB.Command

cmdObj.ActiveConnection = aConn

sqlStr = "insert into dbo.INVENTORY
(ivy_itemcode,ivy_shortdesc,ivy_maincatcode,ivy_is_kit,ivy_is_count,ivy_mino
rcatcode,ivy_type,ivy_image,ivy_weighting,ivy_processing,ivy_theatrenotes,iv
y_materialcost,ivy_qty_onhand,ivy_site) values
(null,null,null,1,1,null,null,null,null,null,null,null,null,null)"
cmdObj.CommandText = sqlStr
cmdObj.CommandType = adCmdText

cmdObj.Execute

Set cmdObj = Nothing

Text1 = "Record Added"

End Sub

Private Sub Command3_LostFocus()
Text1.Text = ""
End Sub

Private Sub Form_Load()
aConn = "PROVIDER=MSDASQL;DSN=SDM_DEMO;uid=_system;pwd=sys;"
aConn.Open
End Sub

Private Sub Form_Unload(Cancel As Integer)
aConn.Close
End Sub

[Denver]

Ohn Ic wrote:
> I tried removing the 'S $ZT="LogErr"' but it still produced the error.

Okay. My approach to debugging is to rule out everything that is is not the problem.
Whatever is left must be the problem.

> Below is a simplified VB6 app that shows how cmdButton 2 and 3 work without
> erroring after being pressed several times. However, when cmdButton1 is
> pressed on more than one occassion, it errors even though it worked on the
> first click event.

Is button 1 adding a record?

Are you trying to add a prohibited duplicate record?
Please explain a little more about what is happening.
You might try simplifying the scenario until you have a simple case that demonstrates the error.

[Sukesh Hoogan]

Ohn

See in-line in VB code

Regards
Sukesh Hoogan
e-Linear Enterprise Solutions
Bombay (India)
http://personal.vsnl.com/sukesh_hoogan



"Ohn Ic" <ohn...@iinet.net.au> wrote in message
news:42845...@info2.kinich.com...

ADD THESE & TRY (YOU MAY ALSO BE REQUIRED TO DESTROY THE PARAMETER OBJECTS)

set cmdObj=Nothing

> End Sub
>
>

[Ohn Ic]

Denver,

Took your advice and removed all parameters from the stored procedure and
inserted them one at a time.

The stored procedure worked up until the second last parameter. I was
defining an incorrect data type to the parameter that differed to the data
type expected for the ClassMethod stored procedure - still don't understand
why it worked for one pass though...?

But at least that dilemma is over.

Thank you to everyone for your feedback on this matter.

Regards,
Ohn Ic.

"Denver" <ØDBraughlerØ_ØbwccØ·com> wrote in message
news:4284c...@info2.kinich.com...