Problem with stored procedure(ran in VB) not updating database.

[Kooda]

However, when this identical stored procedure is ran manually for Sybase central, it works.
It must be some issue with VB and the parameters I am using.
Thanks for any help. Here is the code:

SQL1 = "SELECT CMC_GRGR_GROUP.GRGR_CK, CMC_MEME_MEMBER.MEME_CK " & _
"From CMC_MEME_MEMBER, CMC_GRGR_GROUP " & _
"WHERE CMC_GRGR_GROUP.GRGR_ID = '" & GroupID2 & "' " & _
"AND CMC_MEME_MEMBER.GRGR_CK = CMC_GRGR_GROUP.GRGR_CK"

sqlset.Open SQL1, Conn, , , adCmdText
If sqlset.EOF = False Then
sqlset.MoveFirst
End If
Dim Comm As New ADODB.Command
Comm.ActiveConnection = Conn
Comm.CommandType = adCmdStoredProc 'a constant
Comm.CommandText = "SP_NOTE_UPDATE " & sqlset!MEME_CK & ", " & sqlset!GRGR_CK & ", '" & NoteBox & "'"
Comm.Execute

 

[Guest_imported]

are you getting back an error from the DB?
are you sure the param data types in VB are the ones required by the DB?

 

[Kooda]

Hey Steve,

No, I am not getting any errors back. It seems to run fine but nothing is changed in the db. However, when ran in Sybase Central, the stored procedure works great.

And, yes, the parameters are of the same type as the stored procedure asks for.

thanks

 

[Guest_imported]

Just for giggles.. give me the SP_NOTE_UPDATE code.
I have to trouble shoot these types of things ALL the time.
80% of the time it has to do with params coming in from VB

 

[Kooda]

Thanks for helping out Steve. Here is the code. It wasn't written by any of us, it is a stored procedure written by the software vendor. This is only the first SP that continues to call numerous other stored procedures. Plus, they don't comment their code very well. Good luck!

Again, there is something different with this SP. We have successfully used other SP's in our VB app, but this once produces no results.
-----------------------------------------

/*EXECUTE CERSP_ATT0_SELECT_GEN_IDS @pATXR_SOURCE_ID = NULL , @pATSY_ID = "ATN0" ,
@pATXR_DEST_ID = NULL */

create procedure SP_NOTE_UPDATE

/***********************************************************
******* Declare Arguments ******
***********************************************************/
@MEME_CK int = NULL ,
@GRGR_CK int = NULL ,
@NTNT_TEXT char(100) = NULL
as
/*********************************************************
****** Declare Note Stuff Vairables ******
*********************************************************/
declare @pATXR_SOURCE_ID datetime
declare @pATSY_ID char(004)
declare @pATXR_DEST_ID datetime
declare @pATXR_CREATE_DT datetime
declare @pATXR_LAST_UPD_DT datetime
declare @pATNT_SEQ_NO smallint
declare @pLOCK_TOKEN smallint
declare @pATXR_CREATE_USUS char(016)
declare @pATXR_LAST_UPD_USUS char(016)
declare @pATTB_ID char(004)
declare @pATTB_TYPE char(001)
declare @pATXR_DESC char(070)
declare @pATXR_COMPILED_KEY char(100)
declare @pATXR_LOCK_TOKEN smallint
declare @pATNT_TYPE char(004)
declare @pATXR_ATTACH_ID datetime
declare @pATNT_LOCK_TOKEN smallint
declare @p_Class char(30)
declare @pATND_TEXT char(100)

/*********************************************************
****** Declare Member Vairables ******
*********************************************************/
declare @SBSB_CK int
declare @MEME_SFX smallint
declare @MEME_REL char(1)
declare @MEME_ID_NAME char(06)
declare @MEME_LAST_NAME char(35)
declare @MEME_FIRST_NAME char(15)
declare @MEME_MI char(1)
declare @MEME_TITLE char(10)
declare @MEME_ORIG_EFF_DT datetime
declare @MEME_SSN char(009)
declare @MEME_SEX char(001)
declare @MEME_BIRTH_DT datetime
declare @MEME_WRK_PHONE char(020)
declare @MEME_WRK_PHONE_EXT char(004)
declare @MEME_MCTR_STS char(004)
declare @MEME_MCTR_LANG char(004)
declare @MEME_RECORD_NO char(011)
declare @MEME_LATE_ENR_IND char(001)
declare @MEME_MARITAL_STATUS char(001)

/***********************************************************
****** Begin Procedure Code ******
***********************************************************/
/***********************************************************
this sets the @pATSY_ID to ATN0, and calls a
procedure that will return the ATXR_SOURCE_ID and
the ATXR_DEST_ID (these are the two IDS that are
used to link the MEMBER table to a consoldation table
to the note
************************************************************/
select @pATSY_ID = "ATN0"
Begin
EXECUTE CERSP_ATT0_RETURN_GEN_IDS
@pATXR_SOURCE_ID = @pATXR_SOURCE_ID output,
@pATSY_ID = @pATSY_ID output,
@pATXR_DEST_ID = @pATXR_DEST_ID output
end
/***********************************************************
Move values into the fields used for the next stored
procedure
***********************************************************/
select @pATNT_SEQ_NO = 0
select @pLOCK_TOKEN = 1
select @pATTB_ID = "MEME"
select @pATTB_TYPE = "S"
select @pATXR_DESC = "NOTE"
select @pATXR_CREATE_DT = getdate()
select @pATXR_CREATE_USUS = "a0b412"
select @pATXR_LAST_UPD_DT = getdate()
select @pATXR_LAST_UPD_USUS = "a0b412"
select @pATXR_COMPILED_KEY = ""
select @pATXR_LOCK_TOKEN = 0
select @pATNT_TYPE = ""
select @pATXR_ATTACH_ID = NULL
select @pATNT_LOCK_TOKEN = 0

/***********************************************************
This inserts a record into the CER_ATXR_ATTACH_U
table, useing the ID generated in the previous stored
procedures
***********************************************************/
Begin
EXECUTE CERSP_ATNT_INSERT_VIEW
@pATXR_SOURCE_ID,
@pATSY_ID,
@pATXR_DEST_ID,
@pATNT_SEQ_NO,
@pATTB_ID,
@pATTB_TYPE,
@pATXR_DESC,
@pATXR_CREATE_DT ,
@pATXR_CREATE_USUS ,
@pATXR_LAST_UPD_DT ,
@pATXR_LAST_UPD_USUS,
@pATXR_COMPILED_KEY,
@pATXR_LOCK_TOKEN ,
@pATNT_TYPE,
@pATXR_ATTACH_ID ,
@pATNT_LOCK_TOKEN,
@pLOCK_TOKEN
END
/***********************************************************
I think this removes any notes that all ready exist (not
sure though)
***********************************************************/
Begin
EXECUTE CERSP_ATND_DELALL
@p_Class = "CER_APPREC_ATND",
@pLOCK_TOKEN = @pLOCK_TOKEN ,
@pATSY_ID = @pATSY_ID ,
@pATXR_DEST_ID= @pATXR_DEST_ID ,
@pATNT_SEQ_NO = @pATNT_SEQ_NO ,
@pATND_SEQ_NO = NULL
END
/***********************************************************
Here the text is set to what text was passed to the
procedure, and a row is inserted into the
CER_ATND_NOTE_C table (this is the table where the
text information is stored
***********************************************************/
Begin
SELECT @pATND_TEXT = @NTNT_TEXT

EXECUTE CERSP_ATND_INSERT
@p_Class="CER_APPREC_ATND",
@pLOCK_TOKEN = @pLOCK_TOKEN ,
@pATSY_ID = @pATSY_ID ,
@pATXR_DEST_ID = @pATXR_DEST_ID ,
@pATNT_SEQ_NO = @pATNT_SEQ_NO ,
@pATND_SEQ_NO = 0,
@pATND_TEXT = @pATND_TEXT,
@pATND_LOCK_TOKEN = 0
END
/***********************************************************
This I don't understand why it is done, but Facets
does it so I am too.
***********************************************************/
/*Begin
EXECUTE CERSP_ATSY_SEARCH_SORC_TBL
@pATTB_ID = "ATXR",
@pATTB_TYPE = @pATTB_TYPE,
@pATSY_LIST_TYPE = "%"
END*/
/***********************************************************
First I select the relevent row from the
CMC_MEME_MEMBER table, from there I run the Facets
stored procedure that will update the members
information so the new note is reflected
***********************************************************/
SELECT @SBSB_CK = SBSB_CK,
@MEME_SFX = MEME_SFX,
@MEME_REL = MEME_REL,
@MEME_ID_NAME = MEME_ID_NAME,
@MEME_LAST_NAME = MEME_LAST_NAME,
@MEME_FIRST_NAME = MEME_FIRST_NAME,
@MEME_MI = MEME_MID_INIT,
@MEME_TITLE = MEME_TITLE,
@MEME_ORIG_EFF_DT = MEME_ORIG_EFF_DT,
@MEME_SSN = MEME_SSN,
@MEME_SEX = MEME_SEX,
@MEME_BIRTH_DT = MEME_BIRTH_DT,
@MEME_WRK_PHONE = MEME_WRK_PHONE,
@MEME_WRK_PHONE_EXT = MEME_WRK_PHONE_EXT,
@MEME_MCTR_STS = MEME_MCTR_STS,
@MEME_MCTR_LANG = MEME_MCTR_LANG,
@MEME_RECORD_NO = MEME_RECORD_NO,
@MEME_LATE_ENR_IND = MEME_LATE_ENR_IND,
@MEME_MARITAL_STATUS = MEME_MARITAL_STATUS
FROM CMC_MEME_MEMBER MEME WHERE MEME_CK = @MEME_CK
BEGIN
EXECUTE CMCSP_MEME_UPDATE
@p_Class= "CMC_APPREC_MEME",
@pLOCK_TOKEN = 1,
@pMEME_CK = @MEME_CK ,
@pGRGR_CK = @GRGR_CK,
@pSBSB_CK = @SBSB_CK,
@pMEME_SFX = @MEME_SFX ,
@pMEME_REL = @MEME_REL,
@pMEME_ID_NAME = @MEME_ID_NAME ,
@pMEME_LAST_NAME = @MEME_LAST_NAME,
@pMEME_FIRST_NAME = @MEME_FIRST_NAME,
@pMEME_MID_INIT = @MEME_MI,
@pMEME_TITLE = @MEME_TITLE,
@pMEME_ORIG_EFF_DT = @MEME_ORIG_EFF_DT,
@pMEME_SSN = @MEME_SSN,
@pMEME_SEX = @MEME_SEX,
@pMEME_BIRTH_DT = @MEME_BIRTH_DT,
@pMEME_WRK_PHONE = @MEME_WRK_PHONE,
@pMEME_WRK_PHONE_EXT = @MEME_WRK_PHONE_EXT,
@pMEME_MCTR_STS = @MEME_MCTR_STS,
@pMEME_MCTR_LANG = @MEME_MCTR_LANG,
@pMEME_RECORD_NO = @MEME_RECORD_NO,
@pMEME_LATE_ENR_IND = @MEME_LATE_ENR_IND,
@pMEME_MARITAL_STATUS = @MEME_MARITAL_STATUS,
@pATXR_SOURCE_ID = @pATXR_SOURCE_ID ,
@pLOCK_TOKEN_IND = 1
END

 

[Kooda]

One more thing about the variables being passed to the stored procedure.
The first 2(MEME_CK, GRGR_CK ) are returned from a query.
The 3rd(NoteBox ) comes from a user input value.

 

[Guest_imported]

I will look at the SP.
As part of my job I have to set the coding standards for which my department uses.
These standards are based on industry standard models like MSF (Microsoft Framework)
Thus I have a natural tendency is to comment on things when I see them. Hope you do not mind.

Prefix your variables with the data type.

sSQL= “bla bla bla”
or
strSQL = “bla bla bla”

in this case s = string

It is good to see the “& _” being used

The prefix should also be used for objects.

oSetSet

You might want to call the recordset object something more meaningful like:

oRSNote

If you like the idea of an abstract name for the purpose of code reuse at least call it something like:

oRS

You can execute your SPs without using a Command object:

sSQL = "EXEC spMySproc param1,param2”
oConnection.Execute(sSQL)

Should you SP return records you could use this:

oRS = oCon.Execute(sSQL)

Also you may want to push you initial SQL select statement into a SP. It will increase performance.

I am sure you are closing and destroying you recordset objects, but wanted to mention that as well..

 

[Kooda]

Thanks for the pointers. I will consider them all.

If you are querying a large table, would you put the query in a seperate SP, or the same SP that updates the records and loop through these records in a loop (within the stored procedure)? I know that the second kills the performance of the database, but if the first damages the performance of the application, how do you determine which is the better option?

 

[Guest_imported]

Do you try to add an explicit commit to the SP?
Sometime when you issue the SP in DB tool is will implicitly commit the procedure for you... VB may not.

 

[Kooda]

Oh, you mean put a "go"s in. I can do that.

 

[Kooda]

Acutally, we can't put any "go" statements in because we will loose all of our variable values.

 

[FowlerConsulting]

>>” which is the better option”

I can tell from that SP you gave me that you have some pretty complex business rules in this application. So the best performing solution is going to take some analysis.

All things being relative I would place this select statement:

SELECT CMC_GRGR_GROUP.GRGR_CK, CMC_MEME_MEMBER.MEME_CK
From CMC_MEME_MEMBER, CMC_GRGR_GROUP
WHERE CMC_GRGR_GROUP.GRGR_ID = '" & GroupID2 & "' AND CMC_MEME_MEMBER.GRGR_CK = CMC_GRGR_GROUP.GRGR_CK

In this SP and in pass GroupID2.
Why would you want to bring a recordset across the network and into application memory when it could stay right back on the DB server?

I would process both solutions with the Query Analyzer to insure the best one.

Here is goes again… I would rewrite you SQL statement like this:

SELECT
G.GRGR_CK,
M.MEME_CK
FROM
CMC_MEME_MEMBER M,
CMC_GRGR_GROUP G
WHERE
M.GRGR_CK = G.GRGR_CK
G.GRGR_ID = '" & GroupID2 & "' AND

Alias the table names will make it easier to read.
Placing the links (M.GRGR_CK = G.GRGR_CK) above the selection criteria will also make it read easier.

In some DBs the optimizer will process the SQL statements quicker if this model is followed…
Steven Fowler, Principal
steve@fowlerconsulting.com
- Development, Training, and Consulting

 

[Kooda]

Thanks, I will try this. I might not be able to work on this in the next couple days but thanks a TON for you time.
Do you frequent this forum? If I have additional questions, would you mind an email from me?

thanks