Recent

Author Topic: Executing SP with parameters against MS SQL Server  (Read 866 times)

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Executing SP with parameters against MS SQL Server
« on: September 10, 2019, 11:16:33 pm »

Using Lazarus 2.04 and running against a 2014 MS SQL server, I have a stored procedure with this signature:

Code: Pascal  [Select]
  1. create procedure [dbo].[lst_UserNotifications]
  2. (
  3.         @chrUserID varchar(16),
  4.         @bitShowComplete  bit = 0,
  5.         @bitShowIgnored   bit = 0,
  6.         @bitIgnoreYourOwn bit = 0
  7. )
  8.  

Using a

- TMSSQLConnection
- TSQLQuery
- TSQLTransaction

and executing

Code: Pascal  [Select]
  1.   SQLQuery.SQL.Text := 'EXEC lst_UserNotifications ''NHTACCESS'', 0, 0, 0';
  2.   query.Active := True;
  3.  
  4.  

works fine and delivers the expected result set.


I have had no success using proper parameters no matter what I tried:

With query.ParseSQL = True and no parameter objects

Creating Parameter object via IDE

Specifying the 4 required parameters as in EXEC lst_UserNotifications :chrUserID, bitShowComplete ...' or not
and any imaginable combination...

I usually end up with it not finding the first parameter.

What do I need to do?

Thanks for any help you can give!
« Last Edit: September 11, 2019, 06:54:35 am by kpeters58 »
Lazarus 2.0.4/FPC 3.0.4/Win 64

valdir.marcos

  • Hero Member
  • *****
  • Posts: 857
Re: Executing SP with parameters against MS SQL Server
« Reply #1 on: September 11, 2019, 07:04:04 am »
Using Lazarus 2.04 and running against a 2014 MS SQL server, I have a stored procedure with this signature:

Code: SQL  [Select]
  1. CREATE PROCEDURE [dbo].[lst_UserNotifications]
  2. (
  3.         @chrUserID VARCHAR(16),
  4.         @bitShowComplete  bit = 0,
  5.         @bitShowIgnored   bit = 0,
  6.         @bitIgnoreYourOwn bit = 0
  7. )

Using a
- TMSSQLConnection
- TSQLQuery
- TSQLTransaction

and executing
Code: Pascal  [Select]
  1.   SQLQuery.SQL.Text := 'EXEC lst_UserNotifications ''NHTACCESS'', 0, 0, 0';
  2.   query.Active := True;
works fine and delivers the expected result set.

I have had no success using proper parameters no matter what I tried:
With query.ParseSQL = True and no parameter objects
Creating Parameter object via IDE

Specifying the 4 required parameters as in EXEC lst_UserNotifications :chrUserID, bitShowComplete ...' or not and any imaginable combination...
What do I need to do?
Thanks for any help you can give!
Try
Code: Pascal  [Select]
  1.   chrUserID := 'NHTACCESS';
  2.   bitShowComplete := '0';
  3.   bitShowIgnored := '0';
  4.   bitIgnoreYourOwn := '0';
  5.   SQLQuery.SQL.Text := 'EXEC lst_UserNotifications ' + QuotedStr(chrUserID) + ', ' + bitShowComplete + ', ' + bitShowIgnored + ', ' + bitIgnoreYourOwn;
  6.   SQLQuery.ExecSQL;

Or:
Code: Pascal  [Select]
  1.   SQLQuery.SQL.Text := 'EXEC lst_UserNotifications :chrUserID, :bitShowComplete, :bitShowIgnored, :bitIgnoreYourOwn';
  2.   SQLQuery.Params.ParamByName('chrUserID').AsString := QuotedStr('NHTACCESS');
  3.   SQLQuery.Params.ParamByName('bitShowComplete').AsString := '0';
  4.   SQLQuery.Params.ParamByName('bitShowIgnored').AsString := '0';
  5.   SQLQuery.Params.ParamByName('bitIgnoreYourOwn').AsString := '0';
  6.   SQLQuery.ExecSQL;

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: Executing SP with parameters against MS SQL Server
« Reply #2 on: September 11, 2019, 04:54:31 pm »
The first one works fine as expected since it's just a replacement of literals with variables from my working example.

The second one fails as described in my initial post:

Parameter "chrUserID" not found
« Last Edit: September 11, 2019, 04:56:53 pm by kpeters58 »
Lazarus 2.0.4/FPC 3.0.4/Win 64

valdir.marcos

  • Hero Member
  • *****
  • Posts: 857
Re: Executing SP with parameters against MS SQL Server
« Reply #3 on: September 11, 2019, 09:29:58 pm »
The first one works fine as expected since it's just a replacement of literals with variables from my working example.
The second one fails as described in my initial post:
Parameter "chrUserID" not found
Maybe:
Code: Pascal  [Select]
  1.   SQLQuery.SQL.Text := 'EXEC lst_UserNotifications :chrUserID, :bitShowComplete, :bitShowIgnored, :bitIgnoreYourOwn';
  2.   SQLQuery.Params.ParamByName('@chrUserID').AsString := QuotedStr('NHTACCESS');
  3.   SQLQuery.Params.ParamByName('@bitShowComplete').AsString := '0';
  4.   SQLQuery.Params.ParamByName('@bitShowIgnored').AsString := '0';
  5.   SQLQuery.Params.ParamByName('@bitIgnoreYourOwn').AsString := '0';
  6.   SQLQuery.ExecSQL;

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: Executing SP with parameters against MS SQL Server
« Reply #4 on: September 12, 2019, 04:39:40 pm »
Nope.
Made a little progress, though.

The crucial line is

Code: Pascal  [Select]
  1. query.ParamCheck := True;


so that the parameter objects get built. I had wrongfully assumed that ParseSQL would do that. Once I had that, the 'Parameter not found' error went away.
Now another issue emerged:


If i use
Code: Pascal  [Select]
  1. query.ExecSQL;
nothing gets returned.

If I use
Code: Pascal  [Select]
  1. query.Active := True;
I get the proper column headers showing up in my attached grid, but no data ...









Lazarus 2.0.4/FPC 3.0.4/Win 64

valdir.marcos

  • Hero Member
  • *****
  • Posts: 857
Re: Executing SP with parameters against MS SQL Server
« Reply #5 on: September 16, 2019, 06:18:54 pm »
Nope.
Made a little progress, though.

The crucial line is
Code: Pascal  [Select]
  1. query.ParamCheck := True;
so that the parameter objects get built. I had wrongfully assumed that ParseSQL would do that. Once I had that, the 'Parameter not found' error went away.
Now another issue emerged:
If i use
Code: Pascal  [Select]
  1. query.ExecSQL;
nothing gets returned.
If I use
Code: Pascal  [Select]
  1. query.Active := True;
I get the proper column headers showing up in my attached grid, but no data ...
Different engines demand different solutions.
If possible, try Firebird and check how it is intuitive for Pascal programmers.

Getting back to Microsoft SQL Server.
Instead of thinking of an "one step selectable stored procedure," you should use the Stored Procedure to save data to a temporary table, and then use Select to retrieve that information.
« Last Edit: September 16, 2019, 08:04:07 pm by valdir.marcos »

af0815

  • Sr. Member
  • ****
  • Posts: 388
Re: Executing SP with parameters against MS SQL Server
« Reply #6 on: September 16, 2019, 07:17:50 pm »
one question, which driver is used for the connection ?
regards
Andreas

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: Executing SP with parameters against MS SQL Server
« Reply #7 on: September 17, 2019, 04:21:08 pm »
Whatever is being used by the default TMSSQLConnection; a MS DLL of sorts I would think.

Definitely no ODBC (nothing installed there)
Lazarus 2.0.4/FPC 3.0.4/Win 64

af0815

  • Sr. Member
  • ****
  • Posts: 388
Re: Executing SP with parameters against MS SQL Server
« Reply #8 on: September 18, 2019, 09:19:28 am »
I am using the dll from Free TDS Project, because the 'normal' MS-DLL are not the newest.
regards
Andreas