Recent

Author Topic: MS SQL-possible to use with SQLDB-Stored Procedures, Inline -, Scalar function  (Read 1388 times)

af0815

  • Hero Member
  • *****
  • Posts: 728
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 ?
regards
Andreas

devEric69

  • Hero Member
  • *****
  • Posts: 615
As 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.
« Last Edit: October 12, 2021, 12:36:52 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

af0815

  • Hero Member
  • *****
  • Posts: 728
Disclaimer: 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.
regards
Andreas

devEric69

  • Hero Member
  • *****
  • Posts: 615
Sorry :-[ .
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

LacaK

  • Hero Member
  • *****
  • Posts: 646
a) Stored Procedures: Can give back a mix of Resultset and Output params
https://wiki.lazarus.freepascal.org/mssqlconn#Output_parameters_of_stored_procedures

b) 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)")

c) Scalar function: Returns a Value
How can i do it ?
Open ("select dbo.fnName(:param1)")

devEric69

  • Hero Member
  • *****
  • Posts: 615
b) 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][+][-]
  1. Create Procedure doStuff(@idFoo Integer, ...);
  2.   SELECT idFoo, ...
  3.   FROM tblFoo
  4.   WHERE (idFoo=@idFoo) AND
  5.               (...)
  6.  
« Last Edit: October 13, 2021, 10:08:59 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

 

TinyPortal © 2005-2018