topic-15483

Posts: 110

Joined: Wed Feb 18, 2009 09:58 AM

Problem in ADODB.Command
Posted: Mon May 04, 2009 12:30 PM

Can some one help me,

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

  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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem in ADODB.Command
Posted: Mon May 04, 2009 05:31 PM

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 (fw): Select all Collapse

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 )

Regards

G. N. Rao.

Hyderabad, India

sajith

Posts: 110

Joined: Wed Feb 18, 2009 09:58 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 07:31 AM

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

Code (fw): Select all Collapse

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 09:17 AM

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

Code (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

sajith

Posts: 110

Joined: Wed Feb 18, 2009 09:58 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 10:06 AM

Many Thanks for ur great Advice,

Code (fw): Select all Collapse

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: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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 10:12 AM

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

Regards

G. N. Rao.

Hyderabad, India

sajith

Posts: 110

Joined: Wed Feb 18, 2009 09:58 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 10:38 AM

Many thanks for ur great help,

Code (fw): Select all Collapse

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 (fw): Select all Collapse

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 11:09 AM

>
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

Regards

G. N. Rao.

Hyderabad, India

sajith

Posts: 110

Joined: Wed Feb 18, 2009 09:58 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 11:33 AM

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

Code (fw): Select all Collapse

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem in ADODB.Command
Posted: Tue May 05, 2009 01:40 PM

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

Regards

G. N. Rao.

Hyderabad, India

sajith

Posts: 110

Joined: Wed Feb 18, 2009 09:58 AM

Re: Problem in ADODB.Command
Posted: Wed May 06, 2009 06:04 AM

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem in ADODB.Command
Posted: Wed May 06, 2009 08:29 AM

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() )

Regards

G. N. Rao.

Hyderabad, India

anserkk

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Problem in ADODB.Command
Posted: Wed May 06, 2009 01:10 PM

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 (fw): Select all Collapse

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

Do this

Code (fw): Select all Collapse

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

Regards

Anser

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem in ADODB.Command
Posted: Wed May 06, 2009 01:49 PM

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

Please see the post viewtopic.php?f=3&t=15339&hilit=null for code samples

Regards

G. N. Rao.

Hyderabad, India

sajith

Posts: 110

Joined: Wed Feb 18, 2009 09:58 AM

Re: Problem in ADODB.Command
Posted: Fri May 08, 2009 08:52 AM

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 (fw): Select all Collapse

//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 (fw): Select all Collapse

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"):=? as this format(@w)

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

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

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

FiveTech Support Forums