OO4O - Needs help me with LONG RAW problem please?
From: Ofir <ofira_at_federationweb.com>
Date: Mon, 24 Jun 2002 12:26:44 +0300
Message-ID: <3d16e5f9_at_news.mhogaming.com>
My problem is that at the first time i execute the SP (with a long string - 4K+ - for the LONG RAW) it works fine, but at the second time it always fails.
I will appreciate any help.
Thank you.
So now i will give you some additional code to make my problem more clear :
Types enumeration to make the code more clear:
Public Enum ParameterTypes
ptBlob = serverType.ORATYPE_BLOB
ptCLOB = serverType.ORATYPE_CLOB
ptDATE = serverType.ORATYPE_DATE
ptCHAR = serverType.ORATYPE_CHAR
ptDECIMAL = serverType.ORATYPE_DECIMAL
ptDOUBLE = serverType.ORATYPE_DOUBLE
ptFLOAT = serverType.ORATYPE_FLOAT
ptNUMBER = serverType.ORATYPE_NUMBER
ptVARCHAR = serverType.ORATYPE_VARCHAR2
ptCURSOR = serverType.ORATYPE_CURSOR
ptLONGROW = serverType.ORATYPE_LONGRAW
End Enum
'*****************************************************
' the major function that i execute
'*****************************************************
Public Function Update(ByRef tGroupDefRec As Record_GROUP_DEFINITION) As enumFedErr
On Error GoTo ErrorHandler
m_Db.ClearParameters
With tGroupDefRec.Row
Dim lRetVal As Long
Dim lNewVersionStamp As Long
If (tGroupDefRec.Info.sUser <> "") Then
Call m_Db.AppendParameter("pUser_Id", tGroupDefRec.Info.sUser, ptVARCHAR, pmIN)
Else
Call m_Db.AppendParameter("pUser_Id", Null, ptVARCHAR, pmIN)
End If
Call m_Db.AppendParameter("pSourceSite_Id", .lSOURCE_SITE, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pUId", .lU_ID, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pName", .sNAME, ptVARCHAR, pmIN)
Call m_Db.AppendParameter("pGdlScript", .sGDL_SCRIPT, ptLONGROW, pmIN)
Call m_Db.AppendParameter("pDynamic", .lDYNAMIC, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pStatus", .lSTATUS, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pRoutingLevel", .lROUTING_LEVEL, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pRpySequence", .lRPY_SEQUENCE, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pVersion_Stamp", .lVERSION_STAMP, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pNew_Version_Stamp", lNewVersionStamp, ptNUMBER, pmOUT)
Call m_Db.AppendParameter("pRetVal", tGroupDefRec.Info.lRowsAffected, ptNUMBER, pmOUT)
lRetVal = m_Db.ExecuteStoredProc("GROUP_ACTIONS.UPDATE_GROUP_DEFINITION", False)
Update = m_Db.GetParameter("pRetVal")
tGroupDefRec.Info.lRowsAffected = m_Db.GetParameter("pRetVal")
.lVERSION_STAMP = m_Db.GetParameter("pNew_Version_Stamp")
End With
m_Db.ClearParameters
GetLastError
If (lLastErrorCode = 0) Then
Update = eFedErr_General_Succeeded
m_LogObj.AddTraceMsg "Update", "Ended Successfully"
Else
m_LogObj.RaiseError "Update"
End If
Exit Function
ErrorHandler:
Update = eFedErr_General_Failure
m_LogObj.AddLogMsg "Update", eFedErr_DAL_SpInternalFailed, _
"the SP GROUP_ACTIONS.UPDATE_GROUP_DEFINITION call failed"
End Function
'*****************************************************
' the function that execute the stored procedure
'*****************************************************
Public Function ExecuteStoredProc(ByVal sSQL As String, _
Optional DropParams As Boolean = True)
On Error GoTo HandleIT
m_OraDb.LastServerErrReset
Dim strSql As String
strSql = "begin " & sSQL & "(" & m_sParameters & "); end;"
ExecuteStoredProc = m_OraDb.ExecuteSQL(strSql)
If DropParams Then
Call ResetParams
End If
NormalExit:
Exit Function
HandleIT:
RaiseError "ciCOnnectionObject.ExecuteStoredProc"
End Function
'*****************************************************
' the stored procedure for the update action
'*****************************************************
Procedure UPDATE_GROUP_DEFINITION (pUser_Id IN VARCHAR2,
pSourceSite_Id IN NUMBER,
pUId IN NUMBER,
pName IN VARCHAR2 DEFAULT NULL,
pGdlScript IN LONG,
pDynamic IN NUMBER DEFAULT NULL,
pStatus IN NUMBER DEFAULT NULL,
pRoutingLevel IN NUMBER DEFAULT NULL,
pRpySequence IN NUMBER DEFAULT NULL,
pVersion_Stamp IN NUMBER,
pNew_Version_Stamp OUT NUMBER,
pRetVal OUT NUMBER)
IS
BEGIN
UPDATE MNG_GROUP_DEFINITION SET
NAME=pName,
GDL_SCRIPT=pGdlScript,
DYNAMIC=pDynamic,
LAST_UPDATE_USER=pUser_Id,
STATUS=pStatus,
ROUTING_LEVEL=pRoutingLevel,
RPY_SEQUENCE=pRpySequence,
VERSION_STAMP=pVersion_Stamp+1
WHERE
SOURCE_SITE=pSourceSite_Id
AND U_ID=pUId
AND VERSION_STAMP=pVersion_Stamp;
pRetVal:=sql%rowcount;
pNew_Version_Stamp:=pVersion_Stamp+1;
END UPDATE_GROUP_DEFINITION;
'*****************************************************
' the table structure
'*****************************************************
CREATE TABLE MNG_GROUP_DEFINITION (
SOURCE_SITE NUMBER (30) NOT NULL,
U_ID NUMBER (30) NOT NULL,
NAME VARCHAR2 (40) NOT NULL,
GDL_SCRIPT LONG NOT NULL,
DYNAMIC NUMBER (1) DEFAULT 0 NOT NULL,
CREATE_DATE DATE DEFAULT SYSDATE NOT NULL,
CREATE_USER VARCHAR2 (40),
LAST_UPDATE_DATE DATE DEFAULT SYSDATE NOT NULL,
LAST_UPDATE_USER VARCHAR2 (40),
STATUS NUMBER (3),
ROUTING_LEVEL NUMBER (3),
RPY_SEQUENCE NUMBER (30),
VERSION_STAMP NUMBER (10) DEFAULT 1 NOT NULL,
CONSTRAINT AVCON_1010683928_DYNAM_000
CHECK (DYNAMIC IN (0, 1)) ,
CONSTRAINT AVCON_1019992803_DYNAM_000
CHECK (DYNAMIC IN (0, 1)),
CONSTRAINT GD_PK
PRIMARY KEY ( SOURCE_SITE, U_ID )
USING INDEX
TABLESPACE USERS PCTFREE 10
STORAGE ( INITIAL 131072 NEXT 131072 PCTINCREASE 0 ))
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 131072
NEXT 131072
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 4096
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
Received on Mon Jun 24 2002 - 11:26:44 CEST
Date: Mon, 24 Jun 2002 12:26:44 +0300
Message-ID: <3d16e5f9_at_news.mhogaming.com>
Hi,
I have a question and i wonder if you can help me:
My case is the need to update a record with a LONG_RAW field.
So i execute a stored procedure with the appropriate parameters to do so.
My major problem is that the first time i execute the SP (with a long string) it works fine, but the second time it always fails.
I have no idea which version of OO4O i have installed, but it suppose to be the newest one because the Platform SDK that i installed is the last one as much as i know. on the references of VB i include the "Oracle InProc-Server 3.0 TYPE Library" if it means something for you. we are working with oracle DB 8.1.7
I have a question and i wonder if you can help me:
My case is the need to update a record with a LONG_RAW field.
So i execute a stored procedure with the appropriate parameters to do so.
My major problem is that the first time i execute the SP (with a long string) it works fine, but the second time it always fails.
I have no idea which version of OO4O i have installed, but it suppose to be the newest one because the Platform SDK that i installed is the last one as much as i know. on the references of VB i include the "Oracle InProc-Server 3.0 TYPE Library" if it means something for you. we are working with oracle DB 8.1.7
My problem is that at the first time i execute the SP (with a long string - 4K+ - for the LONG RAW) it works fine, but at the second time it always fails.
Is it the appropriate types that i am using for the
LONG RAW ?
I signed the relevant information in Red to bold the relevant
information.
I will appreciate any help.
Thank you.
So now i will give you some additional code to make my problem more clear :
Types enumeration to make the code more clear:
Public Enum ParameterTypes
ptBlob = serverType.ORATYPE_BLOB
ptCLOB = serverType.ORATYPE_CLOB
ptDATE = serverType.ORATYPE_DATE
ptCHAR = serverType.ORATYPE_CHAR
ptDECIMAL = serverType.ORATYPE_DECIMAL
ptDOUBLE = serverType.ORATYPE_DOUBLE
ptFLOAT = serverType.ORATYPE_FLOAT
ptNUMBER = serverType.ORATYPE_NUMBER
ptVARCHAR = serverType.ORATYPE_VARCHAR2
ptCURSOR = serverType.ORATYPE_CURSOR
ptLONGROW = serverType.ORATYPE_LONGRAW
End Enum
'*****************************************************
' the major function that i execute
'*****************************************************
Public Function Update(ByRef tGroupDefRec As Record_GROUP_DEFINITION) As enumFedErr
On Error GoTo ErrorHandler
m_Db.ClearParameters
With tGroupDefRec.Row
Dim lRetVal As Long
Dim lNewVersionStamp As Long
If (tGroupDefRec.Info.sUser <> "") Then
Call m_Db.AppendParameter("pUser_Id", tGroupDefRec.Info.sUser, ptVARCHAR, pmIN)
Else
Call m_Db.AppendParameter("pUser_Id", Null, ptVARCHAR, pmIN)
End If
Call m_Db.AppendParameter("pSourceSite_Id", .lSOURCE_SITE, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pUId", .lU_ID, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pName", .sNAME, ptVARCHAR, pmIN)
Call m_Db.AppendParameter("pGdlScript", .sGDL_SCRIPT, ptLONGROW, pmIN)
Call m_Db.AppendParameter("pDynamic", .lDYNAMIC, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pStatus", .lSTATUS, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pRoutingLevel", .lROUTING_LEVEL, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pRpySequence", .lRPY_SEQUENCE, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pVersion_Stamp", .lVERSION_STAMP, ptNUMBER, pmIN)
Call m_Db.AppendParameter("pNew_Version_Stamp", lNewVersionStamp, ptNUMBER, pmOUT)
Call m_Db.AppendParameter("pRetVal", tGroupDefRec.Info.lRowsAffected, ptNUMBER, pmOUT)
lRetVal = m_Db.ExecuteStoredProc("GROUP_ACTIONS.UPDATE_GROUP_DEFINITION", False)
Update = m_Db.GetParameter("pRetVal")
tGroupDefRec.Info.lRowsAffected = m_Db.GetParameter("pRetVal")
.lVERSION_STAMP = m_Db.GetParameter("pNew_Version_Stamp")
End With
m_Db.ClearParameters
GetLastError
If (lLastErrorCode = 0) Then
Update = eFedErr_General_Succeeded
m_LogObj.AddTraceMsg "Update", "Ended Successfully"
Else
m_LogObj.RaiseError "Update"
End If
Exit Function
ErrorHandler:
Update = eFedErr_General_Failure
m_LogObj.AddLogMsg "Update", eFedErr_DAL_SpInternalFailed, _
"the SP GROUP_ACTIONS.UPDATE_GROUP_DEFINITION call failed"
End Function
'*****************************************************
' the function that execute the stored procedure
'*****************************************************
Public Function ExecuteStoredProc(ByVal sSQL As String, _
Optional DropParams As Boolean = True)
On Error GoTo HandleIT
m_OraDb.LastServerErrReset
Dim strSql As String
strSql = "begin " & sSQL & "(" & m_sParameters & "); end;"
ExecuteStoredProc = m_OraDb.ExecuteSQL(strSql)
If DropParams Then
Call ResetParams
End If
NormalExit:
Exit Function
HandleIT:
RaiseError "ciCOnnectionObject.ExecuteStoredProc"
End Function
'*****************************************************
' the stored procedure for the update action
'*****************************************************
Procedure UPDATE_GROUP_DEFINITION (pUser_Id IN VARCHAR2,
pSourceSite_Id IN NUMBER,
pUId IN NUMBER,
pName IN VARCHAR2 DEFAULT NULL,
pGdlScript IN LONG,
pDynamic IN NUMBER DEFAULT NULL,
pStatus IN NUMBER DEFAULT NULL,
pRoutingLevel IN NUMBER DEFAULT NULL,
pRpySequence IN NUMBER DEFAULT NULL,
pVersion_Stamp IN NUMBER,
pNew_Version_Stamp OUT NUMBER,
pRetVal OUT NUMBER)
IS
BEGIN
UPDATE MNG_GROUP_DEFINITION SET
NAME=pName,
GDL_SCRIPT=pGdlScript,
DYNAMIC=pDynamic,
LAST_UPDATE_USER=pUser_Id,
STATUS=pStatus,
ROUTING_LEVEL=pRoutingLevel,
RPY_SEQUENCE=pRpySequence,
VERSION_STAMP=pVersion_Stamp+1
WHERE
SOURCE_SITE=pSourceSite_Id
AND U_ID=pUId
AND VERSION_STAMP=pVersion_Stamp;
pRetVal:=sql%rowcount;
pNew_Version_Stamp:=pVersion_Stamp+1;
END UPDATE_GROUP_DEFINITION;
'*****************************************************
' the table structure
'*****************************************************
CREATE TABLE MNG_GROUP_DEFINITION (
SOURCE_SITE NUMBER (30) NOT NULL,
U_ID NUMBER (30) NOT NULL,
NAME VARCHAR2 (40) NOT NULL,
GDL_SCRIPT LONG NOT NULL,
DYNAMIC NUMBER (1) DEFAULT 0 NOT NULL,
CREATE_DATE DATE DEFAULT SYSDATE NOT NULL,
CREATE_USER VARCHAR2 (40),
LAST_UPDATE_DATE DATE DEFAULT SYSDATE NOT NULL,
LAST_UPDATE_USER VARCHAR2 (40),
STATUS NUMBER (3),
ROUTING_LEVEL NUMBER (3),
RPY_SEQUENCE NUMBER (30),
VERSION_STAMP NUMBER (10) DEFAULT 1 NOT NULL,
CONSTRAINT AVCON_1010683928_DYNAM_000
CHECK (DYNAMIC IN (0, 1)) ,
CONSTRAINT AVCON_1019992803_DYNAM_000
CHECK (DYNAMIC IN (0, 1)),
CONSTRAINT GD_PK
PRIMARY KEY ( SOURCE_SITE, U_ID )
USING INDEX
TABLESPACE USERS PCTFREE 10
STORAGE ( INITIAL 131072 NEXT 131072 PCTINCREASE 0 ))
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 131072
NEXT 131072
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 4096
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;