Executing SP with parameters against MS SQL Server
lazarus / 16-Databases

----------------------------------------------------------------------
kpeters58 · 2019-09-10 21:16

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

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i create
procedure [dbo].[lst_UserNotifications](        @chrUserID
varchar(16),        @bitShowComplete  bit = 0,        @bitShowIgnored
 bit = 0,        @bitIgnoreYourOwn bit = 0) 
Using a

- TMSSQLConnection
- TSQLQuery
- TSQLTransaction

and executing

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery.SQL.Text := 'EXEC lst_UserNotifications ''NHTACCESS'', 0, 0,
0';  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...

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

What do I need to do?

Thanks for any help you can give!

----------------------------------------------------------------------
valdir.marcos · 2019-09-11 05:04

Quote from: kpeters58 on September 10, 2019, 11:16:33 pmUsing Lazarus
2.04 and running against a 2014 MS SQL server, I have a stored
procedure with this signature:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
PROCEDURE [dbo].[lst_UserNotifications](        @chrUserID
VARCHAR(16),        @bitShowComplete  bit = 0,        @bitShowIgnored
 bit = 0,        @bitIgnoreYourOwn bit = 0)
Using a
- TMSSQLConnection
- TSQLQuery
- TSQLTransaction

and executing
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery.SQL.Text := 'EXEC lst_UserNotifications ''NHTACCESS'', 0, 0,
0';  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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   chrUserID
:= 'NHTACCESS';  bitShowComplete := '0';  bitShowIgnored := '0'; 
bitIgnoreYourOwn := '0';  SQLQuery.SQL.Text := 'EXEC
lst_UserNotifications ' + QuotedStr(chrUserID) + ', ' +
bitShowComplete + ', ' + bitShowIgnored + ', ' + bitIgnoreYourOwn; 
SQLQuery.ExecSQL;
Or:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery.SQL.Text := 'EXEC lst_UserNotifications :chrUserID,
:bitShowComplete, :bitShowIgnored, :bitIgnoreYourOwn'; 
SQLQuery.Params.ParamByName('chrUserID').AsString :=
QuotedStr('NHTACCESS'); 
SQLQuery.Params.ParamByName('bitShowComplete').AsString := '0'; 
SQLQuery.Params.ParamByName('bitShowIgnored').AsString := '0'; 
SQLQuery.Params.ParamByName('bitIgnoreYourOwn').AsString := '0'; 
SQLQuery.ExecSQL;

----------------------------------------------------------------------
kpeters58 · 2019-09-11 14:54

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

----------------------------------------------------------------------
valdir.marcos · 2019-09-11 19:29

Quote from: kpeters58 on September 11, 2019, 04:54:31 pmThe 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery.SQL.Text := 'EXEC lst_UserNotifications :chrUserID,
:bitShowComplete, :bitShowIgnored, :bitIgnoreYourOwn'; 
SQLQuery.Params.ParamByName('@chrUserID').AsString :=
QuotedStr('NHTACCESS'); 
SQLQuery.Params.ParamByName('@bitShowComplete').AsString := '0'; 
SQLQuery.Params.ParamByName('@bitShowIgnored').AsString := '0'; 
SQLQuery.Params.ParamByName('@bitIgnoreYourOwn').AsString := '0'; 
SQLQuery.ExecSQL;

----------------------------------------------------------------------
kpeters58 · 2019-09-12 14:39

Nope.
Made a little progress, though.

The crucial line is

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
query.ExecSQL; nothing gets returned.

If I use Code: Pascal  [Select][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
query.Active := True; I get the proper column headers showing up in my
attached grid, but no data ...

----------------------------------------------------------------------
valdir.marcos · 2019-09-16 16:18

Quote from: kpeters58 on September 12, 2019, 04:39:40 pmNope.
Made a little progress, though.

The crucial line is
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
query.ExecSQL; nothing gets returned.
If I use Code: Pascal  [Select][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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.

----------------------------------------------------------------------
af0815 · 2019-09-16 17:17

one question, which driver is used for the connection ?

----------------------------------------------------------------------
kpeters58 · 2019-09-17 14:21

Whatever is being used by the default TMSSQLConnection; a MS DLL of
sorts I would think.

Definitely no ODBC (nothing installed there)

----------------------------------------------------------------------
af0815 · 2019-09-18 07:19

I am using the dll from Free TDS Project, because the 'normal' MS-DLL
are not the newest.