FiveTech Software tech support forums

www.FiveTechSoft.com
https://fivetechsoft.com/forums/

Problem in ADODB.Command

https://fivetechsoft.com/forums/viewtopic.php?f=3&t=15483

Page 1 of 2

Problem in ADODB.Command

Posted: Mon May 04, 2009 12:30 pm
by 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

Re: Problem in ADODB.Command

Posted: Mon May 04, 2009 5:31 pm
by 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 )

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 7:31 am
by 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

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 9:17 am
by 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

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 10:06 am
by 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

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 10:12 am
by 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

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 10:38 am
by 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

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 11:09 am
by 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

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 11:33 am
by 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

Re: Problem in ADODB.Command

Posted: Tue May 05, 2009 1:40 pm
by 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

Re: Problem in ADODB.Command

Posted: Wed May 06, 2009 6:04 am
by 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

Re: Problem in ADODB.Command

Posted: Wed May 06, 2009 8:29 am
by 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() )

Re: Problem in ADODB.Command

Posted: Wed May 06, 2009 1:10 pm
by 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

Re: Problem in ADODB.Command

Posted: Wed May 06, 2009 1:49 pm
by 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

Re: Problem in ADODB.Command

Posted: Fri May 08, 2009 8:52 am
by 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)
 
All times are UTC
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited