×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

ADODB command object - syntax error on EXECUTE with a return value on stored procedure

ADODB command object - syntax error on EXECUTE with a return value on stored procedure

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

With the exact same code?
You, too, bind a connection string to a command without first establishing - and opening - a connection object?

My VBScript is a tad rusty but I do believe the requirements for a successful connection are similar across languages.

Please post your problematic code here rather than cross-referencing an age-old post without any responses...

Cheers,
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

(OP)
My code is:
_____________________________________________________________________________________________________________
Public Function gn_runSQLProcedure(strProcedure)

Dim connectionString: connectionString = "DRIVER={Microsoft ODBC for Oracle}; SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="&Environment.Value("Server_Name")&")(PORT="&Environment.Value("Port")&"))(CONNECT_DATA=(SERVICE_NAME="&Environment.Value("Service_Name")&")));Uid="&Environment.Value("User_ID")&";Pwd="&Environment.Value("DB_Password")&";"
Dim connection : Set connection = CreateObject("ADODB.Command")

connection.ActiveConnection = connectionString

'Set the command type to Stored Procedures
connection.CommandType = 4
connection.CommandText = strProcedure

'Execute the stored procedure
connection.Execute()

Set connection = Nothing

End Function
______________________________________________________________________________________________________________
strProcedure is a name of procedure that I am passing through excel sheet.

Please let me know if I am following some incorrect approach.

The stored procedure has no inputs or output values, only the returned string "PL/SQL procedure successfully completed." No recordset is returned either.

Note: Same procedure runs successfully when I run it in SQL Developer.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

Same mistake then.
You write "Dim connection", yet you do not use a connection object but a command object.

Here a basic example how to properly set up a connection :

CODE

Dim connection
Set connection = createobject("ADODB.Connection")
connection.Open connectionString 

So: first create a connection object, then open said connection. For your command, give it the proper name:

CODE

Dim cmd
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = connection 

As you can see, you assign a connection object rather than a connection string.
You also need something to receive/hold the return value:

CODE

DIM RS
SET RS = CreateObject("ADODB.recordset") 

From there on, you can continue, using RS to capture the return value:

CODE

cmd.CommandType = 4
cmd.CommandText = strProcedure

'Execute the stored procedure
Set RS = cmd.Execute 

Do your magic. After that, don't forget to close the connection:

CODE

connection.Close 

Cheers,
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

Did you test this?
Did it help?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

(OP)
Hey,

Connection in my code is a variable name, earlier I was trying with ADODB.Connection but it was not working that time.
After research I got to know that there is another method to run stored procedures i.e. ADODB.Command
Recordset I am not using because the procedure that I am using is not returning any value.

The stored procedure i am passing in the excel sheet, please let me know if there is any particular way to pass procedure in the excel as I am not sure of this.

Thanks,

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

(OP)
One more query:
Is it necessary to have SQL procedure stored in Database to run it through UFT?

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

The ADODB command still needs an established and open connection.
If the SP just needs execution but does not return a value, ditch the Recordset. You must keep the connection object though.
Concerning UFT: no idea. Your question is much too broad for this. Solve one problem at a time. :)

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

(OP)
I have tried executing it the way you have explained, I am still getting the same error.
I have attached the error screenshot.

Is it necessary to have SQL procedure stored in Database with some name?

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

a) The screenshot is truncated. All I see is "Run error" and "syntax error or access violation".
b) Please do not simply provide a screenshot of an error message. Instead, provide the code you are currently working with and specify the line at which the error occurs.
c) Necessary for what? I still do not understand the meaning of your question whether a stored procedure needs to be stored.ponder Everything depends on what exactly your goal is.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

(OP)
The stored procedure I am trying to execute is not saved in Database (SQL Developer).
I am passing the stored procedure in the external excel sheet, then fetching the stored procedure in uft by making connection with the excel sheet.

strProcedure = Trim(objExcelDB.cn_getReqShtData("ProcedureToRun","RunbookReq_Data"))
strProcedure is a variable name where I am storing the procedure passed in the external excel sheet.


This Function gn_runSQLProcedure is called to execute the same
---------------------------------------------------------------------
gn_runSQLProcedure(storedProcedure)
---------------------------------------------------------------------
Public Function gn_runSQLProcedure(strProcedure)

objExcelDB.cn_connectDBDetails()
Dim connection: Set connection = CreateObject("ADODB.Connection")
Dim connectionString: connectionString = "DRIVER={Microsoft ODBC for Oracle}; SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="&Environment.Value("Server_Name")&")(PORT="&Environment.Value("Port")&"))(CONNECT_DATA=(SERVICE_NAME="&Environment.Value("Service_Name")&")));Uid="&Environment.Value("User_ID")&";Pwd="&Environment.Value("DB_Password")&";"
connection.Open connectionString

Dim cmd : Set cmd = CreateObject("ADODB.Command")

cmd.ActiveConnection = connection

'Set the command type to Stored Procedures
cmd.CommandType = 3
cmd.CommandText = strProcedure
'Set rs = cmd.Execute

'Execute the stored procedure
cmd.Execute()
connection.Close

Set cmd = Nothing
Set connection = Nothing

End Function
-----------------------------------------------------------------------------

Is this approach fine to execute this kind of procedure or is there any other way?

Thanks,

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

Quote:

The stored procedure I am trying to execute is not saved in Database (SQL Developer).
I am passing the stored procedure in the external excel sheet, then fetching the stored procedure in uft by making connection with the excel sheet.

Sorry, but you are not making a lot of sense here, at least not to me. They keyword in "stored procedure" is "stored", it is a procedure that is "stored" in order to have it ready when needed.
How are you "passing" a non-stored SP from an Excel sheet (where to?) and "fetching" it by making a connection "to that sheet" when in your code you are making a connection "to an Oracle DB"?

Nothing in your code shows any of this!

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

Can you help how to get the rows affected when we run the update statements with above code. Records affected i tried and its not working.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

ponder
Are you sure you are in the correct thread, ctha?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

RE: ADODB command object - syntax error on EXECUTE with a return value on stored procedure

Since it was relegated to ADODb connection i submitted here
Function Sq_QryUdate(field,value )


'Qry1.ConnectionString = "Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=XXX;Password=XXX;Data Source=XXX"

Set Qry1 = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )

Qry1.Open = field 'Connection string passed from excel
Set myCommand.ActiveConnection = Qry1
myCommand.CommandText = value' Update statement passed in excel'
myCommand.CommandType = adCmdText
Qry1.Execute value
myCommand.Execute RecordsAffected,adExecuteNoRecords

Log.Message(myCommand.Execute(RecordsAffected,adExecuteNoRecords))

Qry1.Close
End Function

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close