Delphi ADO - calling stored procedures with params

Hi, why do I get an error using this code? "Operation not allowed in context (or something like that). Can anyone see the problem? I'm using a dynamic stored procedure, taking a SQL command as a parameter. The stored procedure at the end:

function ReaderADO.readADOSet(sSQLStatement: string): ADODB_TLB.Recordset;
var
  myConnection : ADODB_TLB.TConnection;
  rsResult : ADODB_TLB.Recordset;
  cmdCommand : ADODB_TLB.Command;
  oleParam, oleRecords : OleVariant;
begin
  myConnection := m_ConnectionPool.getADOConnection();
  oleRecords := Unassigned;
  rsResult := ADODB_TLB.CoRecordset.Create();
  cmdCommand := ADODB_TLB.CoCommand.Create( );
  cmdCommand.Set_ActiveConnection( myConnection.DefaultInterface );
  cmdCommand.CommandType := adCmdStoredProc;
  cmdCommand.CommandText := 'sp_DynamicRead';
  cmdCommand.Parameters.Item[0].Name := '@sql';
  cmdCommand.Parameters.Item[0].Value := 'SELECT * FROM xt_Car';
  cmdCommand.Parameters.Item[0].type_ := adVarChar;
   cmdCommand.Prepared := True;
  rsResult := cmdCommand.Execute(oleRecords, oleRecords , adLockOptimistic);
  result := rsResult;
  m_ConnectionPool.returnConnection();
end;

The Stored procedure looks like this:
CREATE PROCEDURE sp_Dynamicread (@sql VARCHAR(255)) AS
EXEC (@sql)
GO

LVL 1
henrikatworkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bpanaCommented:
hi,

why do you want to use a stored procedure for this task?

Bogdan
0
bpanaCommented:
hi,

I would use this kind of function:

function ExecSQL(sSQLStatement: string): Recordset;
var
  rsTemp: recordset;
  strCon: string;
  conn  : connection;
begin
  conn := CoConnection.Create;

  conn.ConnectionString := 'Provider=SQLOLEDB.1;Persist Security Info=True;User ID=...;Initial Catalog=...DBNAME...;Data Source=...TableName...; Password=...';
  conn.CursorLocation := adUseClient;
  conn.ConnectionTimeout := 1;
  conn.Open(conn.ConnectionString, '', '', -1);
  try
    try
      rsTemp := CoRecordset.Create;
      rsTemp.CursorLocation := adUseClient;
      rsTemp.Open(sSQLStatement, conn, adOpenStatic, adLockbatchOptimistic, adCmdtext);
      Result := rsTemp;
      rsTemp._Set_ActiveConnection(nil);
    except
      rsTemp._Set_ActiveConnection(nil);
      raise Exception.Create('Error!');
    end;
  finally
    conn.Close;
  end;
end;

Bogdan
0
henrikatworkAuthor Commented:
That stored procedure was just a sample one, I'm using several stored procedures and I need to know how to call them with parameters from Delphi!!

cheers,

henrik

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Wim ten BrinkSelf-employed developerCommented:
Use:
    cmdCommand.Parameters.Append(cmdCommand.CreateParameter('sql', adVarChar, adParamInput, 255, 'SELECT * FROM xt_Car'));
And see if it works better. Might be a lot easier to read. ;-)

Skip the "cmdCommand.Prepared := True;" since you're freeing the cmdCommand after the call again. Preparing a query is done automatically and only useful when you're going to do multiple calls to the server.
0
Peter_Commented:
You often need to add the parameters yourself to the parameters collection when using SP:s.

In case Parameters is empty, then setting values for cmdCommand.Parameters.Item[0] will cause some error.

Workshop_Alex code would solve this. I personally prefer to declare each one if not having too many, but it does much the same...

var
 MyFirstParameter: TParameter;

MyFirstParameter := cmdCommand.AddParameter;
MyFirstParameter.Name := '@sql';
MyFirstParameter.type_ := ...
   
0
henrikatworkAuthor Commented:
Hi,

the suggestions on how to set the parameters work, but I have one last question: How do I get the Command object to execute them?

rsResult := cmdCommand.Execute( oleRecords, ???? , adCmdStoredProc );

What comes in the question mark?

cheers,

henrik
0
bpanaCommented:
hi,

from the ado help:

Parameters
Optional. A Variant array of parameter values used in conjunction with the input string or stream specified in CommandText or CommandStream. (Output parameters will not return correct values when passed in this argument.)

try with cmdCommand.Parameters

Bogdan
0
henrikatworkAuthor Commented:
rsResult := cmdCommand.Execute( oleRecords, cmdCommand.Parameters , adCmdStoredProc );

gives an error : "Types of the actual and formal parameters have to be the same"

Any other suggestion?

I'm using MDAC 2.7

cheers,

henrik
0
bpanaCommented:
try :

var
  varParam          : variant;
begin
   varParam := VarArrayCreate([0, 4], varVariant);
   varParam[0] := ...;
   varParam[1] := ...;
   varParam[2] := ...;
   varParam[3] := ...;

when you finish filling this array, give it as parameter on execute
0
henrikatworkAuthor Commented:
I get an OLE -error  "A parameter object is not orderly defined. Inconsistent or incomplete information given." - when trying your solution, the code looking like this:

function ReaderADO.readADOSet(sSQLStatement: string): ADODB_TLB.Recordset;
var
  myConnection : ADODB_TLB.TConnection;
  rsResult : ADODB_TLB.Recordset;
  cmdCommand : ADODB_TLB.Command;
  myParameter : OleVariant;
begin
  myConnection := m_ConnectionPool.getADOConnection();
  cmdCommand := ADODB_TLB.CoCommand.Create( );
  cmdCommand.Set_ActiveConnection( myConnection.DefaultInterface );
  cmdCommand.CommandType := adCmdStoredProc;
  cmdCommand.CommandText := 'sp_DynamicRead';

    myParameter :=  VarArrayCreate([0, 4], varVariant);
    myParameter[0] := 'sql';
    myParameter[1] := 'adVarChar';
    myParameter[2] := 'adParamInput';
    myParameter[3] := sSQLStatement;

   rsResult := cmdCommand.Execute( oleRecords, myParameter, adCmdStoredProc );
    result := rsResult;
end;

Isn't there anyone doing Delphi, Mssql and Stored procedures?

cheers,

henrik
0
bpanaCommented:
rsResult := cmdCommand.Execute( oleRecords, EmptyParam , adCmdStoredProc );
0
bpanaCommented:
from the msdn:

Parameters
Optional. A Variant array of parameter values used in conjunction with the input string or stream specified in CommandText or CommandStream.
0
henrikatworkAuthor Commented:
Doing that, with the following code:

  cmdCommand := ADODB_TLB.CoCommand.Create( );
  cmdCommand.Set_ActiveConnection( myConnection.DefaultInterface );
  cmdCommand.CommandType := adCmdStoredProc;
  cmdCommand.CommandText := 'sp_DynamicRead';
  cmdCommand.Parameters.Append(cmdCommand.CreateParameter('@sql', adVarChar, adParamInput, 255, 'SELECT * FROM xt_car'));
  oleRecords := Unassigned;
  oleParam := Unassigned;
  rsResult := cmdCommand.Execute( oleRecords, oleParam , adCmdStoredProc );

I get the error - "The s_DynamicRead procedure is expecting the @sql parameter, but this wasn't delivered"

Can someone help me? Is there anyone who has a working sample, using parameters to a stored procedure?

0
bpanaCommented:
var
  MyParam : Parameter;
begin

 cmdCommand := ADODB_TLB.CoCommand.Create( );
  cmdCommand.Set_ActiveConnection( myConnection.DefaultInterface );
  MyParam :=  cmdCommand.Parameters.Append(cmdCommand.CreateParameter('@sql', adVarChar, adParamInput, 255, 'SELECT * FROM xt_car'));
  cmdCommand.CommandType := adCmdStoredProc;
  cmdCommand.Parameters.Append(MyParam);
  cmdCommand.CommandText := 'sp_DynamicRead';
  MyParam.Value := sSQLStatement;
  oleRecords := Unassigned;
  oleParam := Unassigned;
  rsResult := cmdCommand.Execute( oleRecords, oleParam , adCmdStoredProc );
0
henrikatworkAuthor Commented:
Bpana, I appreciate that you are trying to help, but if you are just guessing and pasting code full of errors that won't even close to compile, then you are doing quite the opposite of helping...

...I need an answer from someone who has ADO 2.7/2.8 installed and who is actually using it with Stored Procedures.

cheers,

henrik

0
bpanaCommented:
hi,

you can see the errors by yourself, there is not so hard to find an misstyped error

it's just that I don't have Delphi in front of me.

yesterday I've tried your code and is should work as I've posted in the last post (I've tested it with Delphi 6 and ADO 2.7).

anyway, sorry if my comments didn't help

good luck
0
bpanaCommented:
hi,

function TForm1.readADOSet(sSQLStatement: string): ADODB_TLB.Recordset;
var
  myConnection : ADODB_TLB.Connection;
  rsResult : ADODB_TLB.Recordset;
  cmdCommand : ADODB_TLB.Command;
  oleParam, oleRecords : OleVariant;
  MyFirstParameter: Parameter;
begin
  myConnection := coConnection.Create;
  myConnection.ConnectionString := ADOConnection1.ConnectionString;
  myConnection.Open(myConnection.ConnectionString, '', '', -1);
  myConnection.CursorLocation := adUseClient;
  oleRecords := Unassigned;
  rsResult := ADODB_TLB.CoRecordset.Create();
  cmdCommand := ADODB_TLB.CoCommand.Create( );
  cmdCommand.Set_ActiveConnection(myConnection);

  MyFirstParameter := cmdCommand.CreateParameter('@sql', adVarChar, adParamInput, 250, sSQLStatement);
  cmdCommand.CommandType := adCmdStoredProc;
  cmdCommand.Parameters.Append(MyFirstParameter);
  cmdCommand.CommandText := 'sp_DynamicRead';
  MyFirstParameter.Value := sSQLStatement;

  rsResult := cmdCommand.Execute(oleRecords, EmptyParam , adCmdStoredProc);
  rsResult._Set_ActiveConnection(nil);
  result := rsResult;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  RS: Recordset;
begin
  RS := readADOSet('select * from login');

  ADOTable1.Recordset := _Recordset(IUnknown(RS));
end;

this is the code which i have used and worked ok.
you can use the recordset with a adotable, a dataset and a dbgrid and see the recordset.

(don't forget to set the CursorLocation to adUseClient, otherwise the recordset will not be able to use bookmarks, that means it will be lkie a forward only recordset)

hope it helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
roknjohnCommented:
I'm curious... How come you don't use the TADOStoredProc component? I use it all the time against MS SQL & Access databases.
0
henrikatworkAuthor Commented:
Bogdan: GREAT!! THANKS A LOT!!!

Rokjohn: I use Command, since I'm actually using a stream object to retrieve XML results from the database. The code I've posted was only a simplified version.

cheers!!

a very happy henrik :)
0
bpanaCommented:
Hi, henrik.

I'm glad to hear that.

cheers!
Bogdan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.