Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Delphi ADO  - calling stored procedures with params

Posted on 2003-11-24
20
18,832 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

792 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