Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delphi ADO  - calling stored procedures with params

Posted on 2003-11-24
20
Medium Priority
?
19,578 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
[X]
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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

636 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