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

[nupur21]

thread329-1688177
I am facing exactly same issue as mentioned in the above thread, can someone please help me here. Help is much needed.
Thank You in advance.

 

[MakeItSo]

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.

 

[nupur21]

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.

 

[MakeItSo]

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 :

Dim connection
Set connection = createobject("ADODB.[b]Connection[/b]")
connection.[b]Open[/b] connectionString

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

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:

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

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

cmd.CommandType = 4
cmd.CommandText = strProcedure

'Execute the stored procedure
[b]Set RS =[/b] cmd.Execute

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

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.

 

[MakeItSo]

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.

 

[nupur21]

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,

 

[nupur21]

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

 

[MakeItSo]

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.

 

[nupur21]

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?

 

[MakeItSo]

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

 

[nupur21]

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,

 

[MakeItSo]

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.

 

[ctha]

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.

 

[MakeItSo]

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.

 

[ctha]

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