MS SQL-possible to use with SQLDB-Stored Procedures, Inline -, Scalar
function
lazarus / 16-Databases

----------------------------------------------------------------------
af0815 · 2021-10-12 07:38

Is it possible to use Stored Procedures, Inline functions, Scalar
function (SQLdb with FreeTDS driver) ?

a) Stored Procedures: Can give back a mix of Resultset and Output
params
As is understand SP can only one resultset (the first) and no output
params. See https://forum.lazarus.freepascal.org/index.php/topic,32765
.msg211589.html#msg211589
So it looks not realy useable, if you need calculated values back

b) Inline functions: Returns a Resultset
Should be possible, but only with open, not with ExecSQL

c) Scalar function: Returns a Value
How can i do it ?

Have anybody more information ?

----------------------------------------------------------------------
devEric69 · 2021-10-12 10:32

Quote from: af0815 on October 12, 2021, 09:38:45 amAs is understand SP
can only one resultset (the first) and no output params.

Concerning this point (sorry, I don't use SP in mySQL), but with other
server databases, the output variables sets constitute the iterable
recordset sent back towards the client program (client which will call
"while not SP.eof do call SP.next and do stuff").
The difference is that in a SP, it's possible to use different kind of
back-end variables like implicite or explicit cursors variables, input
parameters variables, local variables, and output parameters. AFAIK,
at the end of the server side data processing, it returns a
{pseudo-}SELECT: the ouptut parameters sets, which are seen in the
client with SP.fieldByName('abc').AsXyz.
That said SP.Execute should indeed return normally just\at most an row
(Execute is normaly used to do an "action SQL" like an INSERT or an
UPDATE). Some server databases can return a RETURNING clause). SP.Open
(Open is normaly used to do an "selectable SQL" i.e. a
{pseudo-}SELECT) should be called to iterate (fetch by fetch, i.e. row
by row) each output variables set.

Disclaimer: I don't use SP in mySQL.

----------------------------------------------------------------------
af0815 · 2021-10-12 10:38

Quote from: devEric69 on October 12, 2021, 12:32:28 pmDisclaimer: I
don't use SP in mySQL.
BTW: I don't talk about mySQL, i ask about MS SQL with SQLdb with the
FreeTDS driver.

----------------------------------------------------------------------
devEric69 · 2021-10-12 10:39

Sorry  .

----------------------------------------------------------------------
LacaK · 2021-10-13 17:03

Quote from: af0815 on October 12, 2021, 09:38:45 ama) Stored
Procedures: Can give back a mix of Resultset and Output params
https://wiki.lazarus.freepascal.org/mssqlconn#Output_parameters_of_sto
red_procedures

Quote from: af0815 on October 12, 2021, 09:38:45 amb) Inline
functions: Returns a Resultset
Should be possible, but only with open, not with ExecSQL
Yes, as far as resultset is returned you must use Open ("select * from
dbo.fnName(:param1)")

Quote from: af0815 on October 12, 2021, 09:38:45 amc) Scalar function:
Returns a Value
How can i do it ?
Open ("select dbo.fnName(:param1)")

----------------------------------------------------------------------
devEric69 · 2021-10-13 20:04

Quote from: LacaK on October 13, 2021, 07:03:34 pmQuote from: af0815
on October 12, 2021, 09:38:45 amb) Inline functions: Returns a
Resultset
Should be possible, but only with open, not with ExecSQL
Yes, as far as resultset is returned you must use Open ("select * from
dbo.fnName(:param1)")

Apparently, the return of multiple records with an MS-SQL SP is done -
at most simple - like this (using Open on the client's side):

Code: MySQL  [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 doStuff(@idFoo Integer, ...);As  SELECT idFoo, ...  FROM
tblFoo  WHERE (idFoo=@idFoo) AND              (...)