Solved

Delphi ADO  - calling stored procedures with params

Posted on 2003-11-24
20
18,496 Views
Last Modified: 2013-11-23
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

0
Comment
Question by:henrikatwork
20 Comments
 
LVL 6

Expert Comment

by:bpana
ID: 9813306
hi,

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

Bogdan
0
 
LVL 6

Expert Comment

by:bpana
ID: 9813584
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9816583
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
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 9816922
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
 
LVL 3

Expert Comment

by:Peter_
ID: 9817570
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9817999
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
 
LVL 6

Expert Comment

by:bpana
ID: 9818225
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9818294
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
 
LVL 6

Expert Comment

by:bpana
ID: 9818420
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9818585
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

by:bpana
ID: 9820983
rsResult := cmdCommand.Execute( oleRecords, EmptyParam , adCmdStoredProc );
0
 
LVL 6

Expert Comment

by:bpana
ID: 9821002
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9823640
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
 
LVL 6

Expert Comment

by:bpana
ID: 9823683
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9823872
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
 
LVL 6

Expert Comment

by:bpana
ID: 9823917
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
 
LVL 6

Accepted Solution

by:
bpana earned 500 total points
ID: 9828742
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
 
LVL 1

Expert Comment

by:roknjohn
ID: 9846837
I'm curious... How come you don't use the TADOStoredProc component? I use it all the time against MS SQL & Access databases.
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 9866354
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
 
LVL 6

Expert Comment

by:bpana
ID: 9866391
Hi, henrik.

I'm glad to hear that.

cheers!
Bogdan
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now