Problem in ADODB.Command

[sajith]

Can some one help me,

i want to return a value frm storedprocedure as "output parameter" iam using
following code

Code: Select all

  oParameter:=CreateObject("ADODB.Parameter")
oCommand:=CreateObject("ADODB.Command")
oCommand:ActiveConnection = oCon
oCommand:CommandText:="Sp_Job1"
oCommand:CommandType:=adCmdStoredProc
oCommand:PARAMETERS:Append:CreateParameter("Ename", adVarChar,adParamInput,cName)
oCommand:PARAMETERS:Append:CreateParameter("Job", adVarChar,adParamInput, cJob)
oParameter:=oCommand:CreateParameter("@Jcode",adVarChar, adParamOutput,50)
oCommand:PARAMETERS:Append:CreateParameter("@Jcode", adVarChar,adParamOutput, 50)

  oCommand:Execute()
 MsgInfo( cmd.Parameters("@Jcode")) 

Regards ,
Sajith

[nageswaragunupudi]

One simple way is to write
oCommand:Parameters:Refresh()

Going your way ( it is always better to define the parameters in our code as you have done )

Code: Select all

oCommand:Parameters:Append( oCommand:CreateParameter("Ename", adVarChar,adParamInput,nSize, cName) )   // Give the size. Important
oCommand:Parameters:Append( oCommand:CreateParameter("Job", adVarChar,adParamInput, nSize, cJob))  // Give the size : Important for all adVarChar params
oParameter:=oCommand:CreateParameter("@Jcode",adVarChar, adParamOutput,50)
oCommand:Parameters:Append( oParameter )

[sajith]

Many thanks for ur valuable reply,
When i run this code iam getting a error:Unknown name CreateParameter is my code correct

Code: Select all

oCommand:=CreateObject("ADODB.Command")
oCommand:ActiveConnection:=oCon
oCommand:CommandType:=adCmdStoredProc
oCommand:CommandText:="Sp_Job1"
oCommand:Parameters:Append(oCommand:CreateParameter("Ename",adVarChar,adParamInput,25, cName))//Error Unknown name CreateParameter
oCommand:Parameters:Append(oCommand:CreateParameter("Job", adVarChar,adParamInput, 25, cJob))
oCommand:Parameters:Append(oCommand:CreateParameter("Jcode",adVarChar, adParamOutput,20))
   oCommand:Execute()

Regards,
Sajith

[nageswaragunupudi]

The code seems to be correct. But since you have problems, let us test step by step.

Code: Select all

TRY
   oParam := oCommand:CreateParameter( 'anyname', adVarChar, adParamInput, 25 )
CATCH
   ShowAdoError( oCon )
END


function ShowAdoError( oCon )

   local nErrs := 0
   local oErr
   
   nErrs := oCon:Errors:Count()
   if nErrs > 0
      oErr  := oCon:Errors( nErrs - 1 )
      MsgInfo( oErr:Description + CRLF + ;
               cValToChar( oErr:NativeError ) )
   endif

return nil
 

Whichever line you are getting error, put that statement in TRY .. CATCH .. END and use the adoshowerror function. You can debug faster

[sajith]

Many Thanks for ur great Advice,

Code: Select all

oCon:= GetConnection()
oCommand:=CreateObject("ADODB.Command")
oCommand:ActiveConnection:=oCon
oCommand:CommandType:=adCmdStoredProc
oCommand:CommandText:="Sp_Job1"
Try
   oCommand:Parameters:Append(oCommand:CreateParameter("Ename",adVarChar,adParamInput,25, cName))
  CATCH
   ShowAdoError( oCon )
END
oCommand:Execute()
 RETURN  nil

function ShowAdoError( oCon )

   local nErrs := 0
   local oErr
   nErrs :=MsgInfo( oCon:Errors:Count())
   if nErrs > 0
      oErr  := oCon:Errors( nErrs - 1 )
      MsgInfo( oErr:Description + CRLF + ;
               cValToChar( oErr:NativeError ) )
   Endif
RETURN nil
 

Here oCon:Errors:Count() is alwase 0(zero).So no error msg is displayed.
if i wand to check "ADODB.Command" Object wht should i do.

Regards,
Sajith

[nageswaragunupudi]

TRY
oCommand:=CreateObject("ADODB.Command")
oCommand:ActiveConnection:=oCon
oCommand:CommandType:=adCmdStoredProc
oCommand:CommandText:="Sp_Job1"
CATCH
.. Show ,,,
END
May be the command object is not initialized properly

[sajith]

Many thanks for ur great help,

Code: Select all

Try
oCommand:=CreateObject("ADODB.Command")
oCommand:ActiveConnection:=oCon
oCommand:CommandType:=adCmdStoredProc
oCommand:CommandText:="Sp_Job1"
MsgInfo(oCommand)//here object is clarified 
oCommand:Parameters:Append(oCommand:CreateParameter("Ename",adVarChar,adParamInput,25, cName))//Frm here the problem occur
oCommand:Parameters:Append(oCommand:CreateParameter("Job", adVarChar,adParamInput, 25, cJob))
oCommand:Parameters:Append(oCommand:CreateParameter("Jcode",adVarChar, adParamOutput,20))
CATCH
   ShowAdoError(oCon)
END
 

Stored Procedure//

Code: Select all

CREATE PROCEDURE yy.`Sp_Job1`(IN Ename varchar(25),In Job varchar(25),OUT Jcode varchar(20))
Begin
DECLARE code Varchar(50);
set @code=CONCAT(LEFT(Ename, 4),Job);
insert into job values(@Ename,@Job,@code);
set @Jcode=code;
end;
 

Here iam attaching my StoredProcedure also,
When i run this code with out Parameter values the code runs fine.But when i send input parameter through code
Problem occur oCommand:Parameters:Append(oCommand:CreateParameter("Ename",adVarChar,adParamInput,25, cName)) frm this problem occur
Regards,
Sajith

[nageswaragunupudi]

>
CreateParameter("Ename",adVarChar,adParamInput,25, cName))
>
Try this:
Do not assign value while creating the parameter
make it .... ,25 )

before executing the command,

oCmd:Parameters(0):Value := cName
oCmd:Parameters(1):Value := <whatever>
TRY
oCmd:Execute()
CATCH
< SHOW ERROR >
END
msginfo( oCmd:Parameter(2):Value )
msginfo( oCmd:parameter(3):Value )

Note: It should work both ways though. But let us try this now and see

[sajith]

Many thanks,
Still not working ,even error msg is not showing

Code: Select all

Try
oCommand:=CreateObject("ADODB.Command")
oCommand:ActiveConnection:=oCon
oCommand:CommandType:=adCmdStoredProc
oCommand:CommandText:="Sp_Job1"
oCommand:Parameters:Append(oCommand:CreateParameter("Ename",adVarChar,adParamInput,25))
oCommand:Parameters:Append(oCommand:CreateParameter("Job", adVarChar,adParamInput, 25))
oCommand:Parameters(0):Value:=cName
oCommand:Parameters(1):Value:=cJob
//oCommand:Parameters:Append(oCommand:CreateParameter("Jcode",adVarChar, adParamOutput,20))
CATCH
   ShowAdoError(oCon)
END

TRY
   oCommand:Execute()

  CATCH oError
      ShowSqlError(oError)
end
 

Regards,
Sajith

[nageswaragunupudi]

your procedure name is yy.`Sp_Job1
why a space and single quote after yy. ?
if the name is yy.Sp_Job1, then try assigning commandtext as "yy.Sp_Job1"
Make sure your login has rights to execute it.

Can you try creating another procedure as dbo.testproc ? then you can use 'testproc' as command text

[sajith]

Many thanks,

yy.`Sp_Job1` here yy is database name amd Sp_job1 is the procedure name .it comes
automatically when we tried to create procedure(Template) from mysql.i changed the
procedure name as sp_job2 still no effect.If u have any code sample can u kindly share wih me.

Regards,
Sajith

[nageswaragunupudi]

I use Oracle mostly and MsSql also. I did not try MySql.

In oracle I assign the command text as <database>.<procname>

Try setting command text to fullname including database name. Ensure rights of execution for the user logged into MySql

I am of the opinion that the command object is not properly initialized.
If it is initialized well,
you can use oCommand:RefreshParameters()
and then
msginfo( oCommand:Parameters:Count() )

[anserkk]

Dear Sajith,

Your parameters are VarChar type, if you have forgot to do an alltrim on the variables, then you should do it before you set the parameter values. Please let us know the status

Eg.
Instead of

Code: Select all

oCommand:Parameters(0):Value:=cName
oCommand:Parameters(1):Value:=cJob
 

Do this

Code: Select all

cName:=Alltrim(cName)
cJob:=Alltrim(cJob)
oCommand:Parameters(0):Value:=cName
oCommand:Parameters(1):Value:=cJob

 

Regards

Anser

[nageswaragunupudi]

>
If u have any code sample can u kindly share wih me.
>

Please see the post http://forums.fivetechsupport.com/viewt ... hilit=null for code samples

[sajith]

Many thanks Anserkk,nageshswaganpati for ur gorgeous support,

Half the problen is solved with ur reply,now my problem is how to assign a value to
Outputparameter frm FiveWin

Code: Select all

//Procedure
DROP PROCEDURE IF EXISTS yy.Sp_Job5;
CREATE PROCEDURE Sp_Job5(IN Ename varchar(25),In Job varchar(25),OUT Jcode varchar(20))
Begin
DECLARE code Varchar(50);
set @code=CONCAT(LEFT(Ename, 4),Job);
insert into job values(Ename,Job,@code);
set Jcode=code;
end;
call Sp_Job5( 'Sajith','007',@w);//here the problem lies in my Fivewin 

 

Code: Select all

oCommand:=CreateObject("ADODB.Command")
oCommand:ActiveConnection:=oCon
oCommand:CommandType:=adCmdStoredProc
oCommand:CommandText:="Sp_Job5"
oTest1:=oCommand:CreateParameter("Ename",adVarChar,adParamInput,25)
oCommand:Parameters:Append(oTest1)
oTest2:=oCommand:CreateParameter("Job",adVarChar,adParamInput,25)
oCommand:Parameters:Append(oTest2)

cName:=AllTrim(cName)
cJob:=AllTrim(cJob)
oCommand:Parameters("Ename"):Value:=cName
oCommand:Parameters("Job"):Value:=cJob

oTest3:=oCommand:CreateParameter("Jcode",adVarChar, adParamOutput,20)
oCommand:Parameters:Append(oTest3)
oCommand:Parameters("Jcode"):Value:=//Here problem lies i assigned input parameter to
//execute my procedure from fivewin i must give outputparameter as @w format it is not string type Plz view Procedure above
//How can i set that value to here oCommand:Parameters("Jcode"):Value:=? as this format(@w)

CATCH
   MsgInfo("Ado")
   ShowAdoError(oCon)
END

TRY
   oCommand:Execute()
 
  CATCH oError
        ShowSqlError(oError)
end

MsgInfo( oCommand:Parameters("Jcode"):Value)