Lazarus

Programming => Databases => Topic started by: kpeters58 on September 10, 2019, 11:16:33 pm

Title: Executing SP with parameters against MS SQL Server
Post by: kpeters58 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!

Title: Re: Executing SP with parameters against MS SQL Server
Post by: valdir.marcos on September 11, 2019, 07:04:04 am

Quote from: kpeters58 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: 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;

Title: Re: Executing SP with parameters against MS SQL Server
Post by: kpeters58 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

Title: Re: Executing SP with parameters against MS SQL Server
Post by: valdir.marcos on September 11, 2019, 09:29:58 pm

Quote from: kpeters58 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

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;

Title: Re: Executing SP with parameters against MS SQL Server
Post by: kpeters58 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 ...

Title: Re: Executing SP with parameters against MS SQL Server
Post by: valdir.marcos on September 16, 2019, 06:18:54 pm

Quote from: kpeters58 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 ...

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.

Title: Re: Executing SP with parameters against MS SQL Server
Post by: af0815 on September 16, 2019, 07:17:50 pm

one question, which driver is used for the connection ?

Title: Re: Executing SP with parameters against MS SQL Server
Post by: kpeters58 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)

Title: Re: Executing SP with parameters against MS SQL Server
Post by: af0815 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.