* * *

Author Topic: Sql call with output fail with MS sql 2017  (Read 867 times)

SteenJorgensen

  • New member
  • *
  • Posts: 38
Sql call with output fail with MS sql 2017
« on: February 12, 2018, 01:01:43 pm »
I have a problem with a long sql query that don't work.
It say "Cannot open a non-selected statement. But i have a output in the last .Add.

DATA.QueryIL2.Close;
DATA.QueryIL2.SQL.Text := 'DECLARE @t TABLE (Myint1 int, Myint2 int)';
DATA.QueryIL2.SQL.Add(    'INSERT INTO @t Select distinct Myint1 , Myint2 from myTable');
DATA.QueryIL2.SQL.Add(    'Where Myint3 =''' + Myint3 + '''');
DATA.QueryIL2.SQL.Add(    'Select sum(CAST(Myint2 as int)) as MyOut from @t ');
DATA.QueryIL2.SQL.SaveToFile('700.sql');
DATA.QueryIL2.Open;   

The Query works fine when i execute in the sql manager tool.


----------------------------------------
Lazarus version 1.8.4
FPC 3.0.4

bigeno

  • Full Member
  • ***
  • Posts: 211
Re: Sql call with output fail with MS sql 2017
« Reply #1 on: February 12, 2018, 01:16:32 pm »
But INSERT is there, what you expect ?

SteenJorgensen

  • New member
  • *
  • Posts: 38
Re: Sql call with output fail with MS sql 2017
« Reply #2 on: February 12, 2018, 01:45:41 pm »
I expect to get the MyOut from the last line...
The insert is to the tmp table. Then i select from tmp table.
----------------------------------------
Lazarus version 1.8.4
FPC 3.0.4

valdir.marcos

  • Sr. Member
  • ****
  • Posts: 418
Re: Sql call with output fail with MS sql 2017
« Reply #3 on: February 12, 2018, 04:22:53 pm »
SteenJorgensen, are you trying to use Microsoft SQL Server Temporary Tables or Table Variables?
https://www.mssqltips.com/sqlservertip/1556/differences-between-sql-server-temporary-tables-and-table-variables/

Instead of using TSQLQuery component (SQLdb tabsheet), try using TSQLScript component.

Another solution could be using your Temporary Tables or Table Variables embeded in a Stored Procedure.
That way, you could select your data from a stored procedure using a TSQLQuery component.

Leledumbo

  • Hero Member
  • *****
  • Posts: 7937
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Sql call with output fail with MS sql 2017
« Reply #4 on: February 14, 2018, 05:27:27 am »
TSQLQuery was not designed to run multiple SQL queries at once, let alone a combination of select (which requires .Open) and non-select (requires .ExecSQL). If you need to run insert followed by select whose returned rows are required, there's no other way but to run each query on its own (insert + .ExecSQL followed by select + .Open).

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus