Procedure return value on vb

Dear Vowca,

Here i am using IBPhoenix Open Source ODBC Driver.
When i am trying to execute a stored procedure on vb I can a execute the procedure but if the the procedure returns any value i can't get it. Then here I would like to use odbc driver
instead of OLEDB Driver. IBProvier is not a ODBC Driver.
Pls Help.

Thanks & Regards,
Saju.

Are You using the 1) variant or the 2) one ?
Does both of them fail ?

Please, show the text of procedure and of VB request

Also it is generally good to give details about versions of VB / FB / FB-ODBC - for me it will be no help, i am not familar with VB and with ODBC, but someone who is, might find this information of help.

PS: there are also other ODBC drivers, for example lits of those at http://ibase.ru/ib6.htm#odbc

Dear vowca,
These links are not showing how to execute a procedure and how to get a return value.

Thanks & Regards,
Saju.

Answer the questions plz.
I'm tired guessing too.

I just give you tips that can be of help (some little chance, say 10%)
I can't say any more (and seems no one can) cause you do not will to describe Your situation, and hence we no almost nothing about Your trouble.

Dear Vowca,

Here is the stored procedure(Firebird version 1.5 Final, ODBC version 1-1-Beta).

CREATE PROCEDURE  CHANGEPASSWORD_PROC( USERNAME                         VARCHAR( 35 )
                                    , UM_PASSWD                        VARCHAR( 30 ) )
RETURNS ( OUT_INT                          INTEGER )
AS
BEGIN
UPDATE user_mast1 SET UM_PASSWD = :UM_PASSWD WHERE upper(UM_NAME)=:USERNAME;
IF (SQLCODE =0) THEN
OUT_INT = 1;
END

Here is the vb code(Version vb6).
Dim con As New ADODB.Connection
Dim com As New ADODB.Command
Set con = New ADODB.Connection
con.Open ("DRIVER=Firebird/InterBase(r) driver;UID=sysdba;PWD=masterkey;DBNAME=130.28.35.5:/opt/firebird/data/dpfb.gdb;")
Set com = New ADODB.Command
com.ActiveConnection = con
com.CommandText = "execute procedure CHANGEPASSWORD_PROC(?,?)"
com.Parameters.Append com.CreateParameter("par0", adBSTR, adParamInput, , "MGM")
com.Parameters.Append com.CreateParameter("par1", adBSTR, adParamInput, , "TEST")
'com.Parameters.Append com.CreateParameter("out_int_1", adInteger, adParamOutput)
com.Execute
'If com("out_int_1").Value = 1 Then
'    MsgBox "UPDATATED SUCCESSFUL"
'Else
'    MsgBox "OPERATION NOT SUCCESSFUL"
'    Exit Sub
'End If

My problem is I can't use the commented option on this ODBC Driver. I can use this on IBProvider OLEDB driver. I would like to use ODBC Driver instead of OLEDB Driver.
Pls Help....

Thanks & Regards
Saju.

IBProvider has a restricted free version, there are also free IB provider somewhere on (google for SIBProvider) - though it has less quality, i think it has more features than IBProviderFree.
ADO -> ODBC -> IB seems too complex to me.

I wonder about IF in Your SP.
I think that if there'd be error, this IF will never be executed, so it can be removed.
You can justify if i'm right here via IB manual (www.firebirdSQL.ord carryies manual for IB6, in case You don’t have one)

Curently i may suggest You to add SUSPEND; before EXIT;

After that You will be able to call SP as if it was table:

"SELECT  OUT_INT FROM CHANGEPASSWORD_PROC(?,?)"

Or even You can use parameterless statement - just build the string on the client.
Of course it will not be prepared once-for-every-call - but sicne changing password is not frequent - that do not matter.

I hope this VB code is correct, though i did not test it. The BASIC that i 1st learned concatenated strings with +, but AFAIR Microsoft use vert. line of it.
Also i am not ffamiliar it ADO, so i expect You neeed RecordSet to run it, but i may be wrong.

DIM usr_log, usr_pwd, reqest as string
LET usr_log = "MGM"
LET usr_pwd = "TEST"
LET request = "SELECT OUT_INT FROM CHANGEPASSWORD_PROC('" | usr_log |"','" | usr_pwd | "')"

PS: i think that you used SQL cide to see if the update changed something.
If i'm true - that will not work.
SQLCODE is just error number, SQLCODE = 0 - means that no errors was happened, even if no single row oftable was changed.

I'd rewritten You SP as

BEGIN
UPDATE user_mast1 SET UM_PASSWD = :UM_PASSWD WHERE upper(UM_NAME)=:USERNAME;
OUT_INT = ROWS_AFFECTED;
END
Warning! ROWS_AFFECTED is old variable from FB beta's. It is named according to Borland Delphi tradition.
It has been renamed, so read changelog for FB 1.5 to get it's new name.
http://sf.net/projects/firebird -> show all files -> book icon to the right form FB 1,5 release title.


the_Arioch@nm.ru

Dear Vowca
Thanks for your help on the if statement. I prepare this stored procedure and vbcode for to show u that what i am facing the problem.
I am not using these procedure on my project but many procedure i am using return values so i need know how catch a return value on using odbc driver on firebird.
I check SIBProvider it's not a odbc driver its a OLE DB Driver and this is also non free.
So i have not maid many experiment on that.
Here IBProvider can solve my problem. But in my software company they want a odbc driver so I am much struggling abt that.
Ok any way thanks for your help and immediate replay.

Thanks & Regards
Saju

I still can't understand what is wrong with    Select *From StoredProcedure

Dear Vowca
Thank you very much for your help. On updating ODBC driver and using record set my problem is solved.

Thanks & Regards
Saju