Hello, i need to use a query from DA in 2 diferent dataservers, MSSQL and PostgreSQL.
The query must work with TOP(n) in MSSQL and LIMIT in Postgre, from the client side i use a MemDataTable component, adding a MaxRecord to the query, when i active him, this one put LIMIT instead TOP in the query for MSSQL.
What can i do to solve this issue?.
Hi,
What statement type do you set for this table in Schema Modeller? AutoSQL or SQL?
can you create a simple testcase that reproduces this case, pls?
you can attach it here or send directly to support@ if you want to keep it privately.
Hello,
it’s an standard query:
select field1, field2, field3, from xtable
where
{where}
order by field1, field2
the statement type defined to this query is just SQL.
This’s the code in the client side:
procedure QueryMaxRecordTest;
begin
TDADataSource(VistadeTabla.DataController.DataSource).DataTable.MaxRecords := 2;
VistadeTabla.DataController.DataModeController.GridMode := True;
VistadeTabla.BeginUpdate();
---here the error appears----
TDADataSource(VistadeTabla.DataController.DataSource).DataTable.Active := True;
----------------------
TDADataSource(VistadeTabla.DataController.DataSource).DataTable.Active := False;
VistadeTabla.DataController.DataModeController.GridMode := False;
VistadeTabla.EndUpdate;
end;
All the queries executed from the server side pass through this function, but never when it’s ccalled from the client side
When the procedure QueryMaxRecordTest it’s called, never enter the function GenerateSelectSQL
> function TDASimpleQueryBuilder.GenerateSelectSQL: string;
from the unit uDASimpleQueryBuilder
Here’s what i found using DBMonitor:
Incorrect syntax near ‘LIMIT’.
where
(1=1)
order by field1, field2
LIMIT 2
Instead LIMIT 2, should be TOP 2 because we’re working with SQL Server.
Hi,
can you show callstack or/and attach your test project, pls?
Call stack before the error:
uVerFacturasdeArticulosBase.TfmVerFacturasdeArticulosBase.QueryMaxRecordTest
uVerFacturasdeArticulosBase.TfmVerFacturasdeArticulosBase.FormShow($F1F90B0)
uVerFacturasdeCombustibleEcuador.TfmVerFacturasdeCombustibleEcuador.FormShow($F1F90B0)
Vcl.Forms.TCustomForm.DoShow
Vcl.Forms.TCustomForm.CMShowingChanged(???)
Vcl.Controls.TControl.WndProc((45081, 0, 0, 0, 0, 0, (), 0, 0, (), 0, 0, ()))
Vcl.Controls.TWinControl.WndProc((45081, 0, 0, 0, 0, 0, (), 0, 0, (), 0, 0, ()))
Vcl.Forms.TCustomForm.WndProc((45081, 0, 0, 0, 0, 0, (), 0, 0, (), 0, 0, ()))
Vcl.Controls.TControl.Perform(???,???,0)
Vcl.Controls.TWinControl.UpdateShowing
Vcl.Controls.TWinControl.UpdateControlState
Vcl.Controls.TWinControl.CMVisibleChanged(???)
Vcl.Controls.TControl.WndProc((45067, 1, 0, 0, 1, 0, (), 0, 0, (), 0, 0, ()))
Vcl.Controls.TWinControl.WndProc((45067, 1, 0, 0, 1, 0, (), 0, 0, (), 0, 0, ()))
Vcl.Forms.TCustomForm.WndProc((45067, 1, 0, 0, 1, 0, (), 0, 0, (), 0, 0, ()))
Vcl.Controls.TControl.Perform(???,???,0)
Vcl.Controls.TControl.SetVisible(True)
Vcl.Forms.TCustomForm.SetVisible(True)
Vcl.Forms.TCustomForm.Show
uToolsDxBaseExt.showformMaximized(TfmVerFacturasdeCombustibleEcuador,False)
MAIN.TfmMain.acVerFacturasdeCombustibleExecute($169842E0)
System.Classes.TBasicAction.Execute
Vcl.ActnList.TCustomAction.Execute
System.Classes.TBasicActionLink.Execute(???)
dxBar.TdxBarItem.DoClick
dxBar.TdxBarItem.DirectClick
dxBar.TdxBarItemControl.ControlUnclick(True)
dxBar.TdxBarButtonControl.ControlUnclick(True)
dxBar.TCustomdxBarControl.DoLButtonUp((514, (), 0, (), 98, 55, (), (98, 55), (), 0))
dxBar.TCustomdxBarControl.WMLButtonUp((514, (), 0, (), 98, 55, (), (98, 55), (), 0))
Vcl.Controls.TControl.WndProc((514, 0, 3604578, 0, 0, 0, (), 98, 55, (), 0, 0, ()))
Vcl.Controls.TWinControl.WndProc((514, 0, 3604578, 0, 0, 0, (), 98, 55, (), 0, 0, ()))
dxBar.TCustomdxBarControl.WndProc((514, 0, 3604578, 0, 0, 0, (), 98, 55, (), 0, 0, ()))
Vcl.Controls.TWinControl.MainWndProc(???)
System.Classes.StdWndProc(3088274,514,0,3604578)
:74d96238 ; C:\Windows\syswow64\USER32.dll
:74d968ea ; C:\Windows\syswow64\USER32.dll
:74d97d31 ; C:\Windows\syswow64\USER32.dll
:74d97dfa USER32.DispatchMessageW + 0xf
Vcl.Forms.TApplication.ProcessMessage(???)
Call stack after the error:
uROTransportChannel.TROTransportChannel.Dispatch(TROBinMessage($11CA7B04) as IROMessage) uRODynamicRequest.TRODynamicRequest.DoExecute($14BD9C28) uRODynamicRequest.TRODynamicRequest.Execute(nil) uDARemoteDataAdapter.TDARemoteDataAdapter.InternalFill((...),$16AE3EF8,False,False,(((), True, False, 0, False, nil, False, nil, nil))) uDADataAdapter.TDABaseDataAdapter.Fill((...),(...),False,False,False) uDADataAdapter.TDABaseDataAdapter.Fill((...),False,False,False) uDADataTable.TDADataTable.LoadFromRemoteSource(False) uDADataTable.TDADataTable.DoOpen(True) uDAMemDataTable.TDAMemDataTable.DoOpen(True) uDADataTable.TDADataTable.Open uDADataTable.TDADataTable.SetActive(True) uDADataTable.TDADataTable.SetActiveProperty(True) uVerFacturasdeArticulosBase.TfmVerFacturasdeArticulosBase.QueryMaxRecordTest uVerFacturasdeArticulosBase.TfmVerFacturasdeArticulosBase.FormShow($F1F90B0) uVerFacturasdeCombustibleEcuador.TfmVerFacturasdeCombustibleEcuador.FormShow($F1F90B0) Vcl.Forms.TCustomForm.DoShow Vcl.Forms.TCustomForm.CMShowingChanged(???) Vcl.Controls.TControl.WndProc((45081, 0, 0, 0, 0, 0, (), 0, 0, (), 0, 0, ())) Vcl.Controls.TWinControl.WndProc((45081, 0, 0, 0, 0, 0, (), 0, 0, (), 0, 0, ())) Vcl.Forms.TCustomForm.WndProc((45081, 0, 0, 0, 0, 0, (), 0, 0, (), 0, 0, ())) Vcl.Controls.TControl.Perform(???,???,0) Vcl.Controls.TWinControl.UpdateShowing Vcl.Controls.TWinControl.UpdateControlState Vcl.Controls.TWinControl.CMVisibleChanged(???) Vcl.Controls.TControl.WndProc((45067, 1, 0, 0, 1, 0, (), 0, 0, (), 0, 0, ())) Vcl.Controls.TWinControl.WndProc((45067, 1, 0, 0, 1, 0, (), 0, 0, (), 0, 0, ())) Vcl.Forms.TCustomForm.WndProc((45067, 1, 0, 0, 1, 0, (), 0, 0, (), 0, 0, ())) Vcl.Controls.TControl.Perform(???,???,0) Vcl.Controls.TControl.SetVisible(True) Vcl.Forms.TCustomForm.SetVisible(True) Vcl.Forms.TCustomForm.Show uToolsDxBaseExt.showformMaximized(TfmVerFacturasdeCombustibleEcuador,False) MAIN.TfmMain.acVerFacturasdeCombustibleExecute($169842E0) System.Classes.TBasicAction.Execute Vcl.ActnList.TCustomAction.Execute System.Classes.TBasicActionLink.Execute(???) dxBar.TdxBarItem.DoClick dxBar.TdxBarItem.DirectClick dxBar.TdxBarItemControl.ControlUnclick(True) dxBar.TdxBarButtonControl.ControlUnclick(True) dxBar.TCustomdxBarControl.DoLButtonUp((514, (), 0, (), 98, 55, (), (98, 55), (), 0)) dxBar.TCustomdxBarControl.WMLButtonUp((514, (), 0, (), 98, 55, (), (98, 55), (), 0)) Vcl.Controls.TControl.WndProc((514, 0, 3604578, 0, 0, 0, (), 98, 55, (), 0, 0, ())) Vcl.Controls.TWinControl.WndProc((514, 0, 3604578, 0, 0, 0, (), 98, 55, (), 0, 0, ())) dxBar.TCustomdxBarControl.WndProc((514, 0, 3604578, 0, 0, 0, (), 98, 55, (), 0, 0, ())) Vcl.Controls.TWinControl.MainWndProc(???) System.Classes.StdWndProc(3088274,514,0,3604578) :74d96238 ; C:\Windows\syswow64\USER32.dll :74d968ea ; C:\Windows\syswow64\USER32.dll :74d97d31 ; C:\Windows\syswow64\USER32.dll :74d97dfa USER32.DispatchMessageW + 0xf Vcl.Forms.TApplication.ProcessMessage(???)
Hi,
This is client-side.
can you show call-stack on server-side, pls?
Client side
client-side will show that error was raised on server-side only so call-stack of server-side is needed
i can’t see the call stack at server side, in which procedure can i put a breakpoint to capture the error?
You can set breakpoint at
procedure TDAEBaseDataset.DoSetActive(Value: boolean);
...
try
NativeDatabaseAccess.Active := True;
FixKnownIssues;
except
on E:Exception do begin //<<<<<<<<<<<<<<<<<<< here
Call stack from server side.
Before exception:
uDAEBaseDataset.TDAEBaseDataset.DoSetActive(True)
uDAEBaseDataset.TDAEBaseDataset.SetActive(True)
uDAEBaseDataset.TDAEBaseDataset.Open
DataAbstractService_Impl.TDataAbstractService.GetData_StandardMode($C72F924,$A564280)
DataAbstractService_Impl.TDataAbstractService.InternalGetData($A4D3610,$A4D32A0,$A564280,False)
DataAbstractService_Impl.TDataAbstractService.GetData($A4D3610,$A4D32A0)
DataAbstract4_Invk.TDataAbstractService_Invoker.Invoke_GetData(TPetroBoxQueryService($903227C) as IInterface,TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,[])
uROServer.TROInvoker.CustomHandleMessage(TROClassFactory($737476C) as IROClassFactory,TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,[])
uROServer.TROInvoker.HandleMessage(TROClassFactory($737476C) as IROClassFactory,TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,[])
uROServer.MainProcessMessage(TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,$A498520,$A498C20,[],False)
uROServer.TROMessageDispatcher.ProcessMessage(TIndyHTTPTransport($991820C) as IROTransport,$A498520,$A498C20,[],False)
uROServer.TROServer.IntDispatchMessage($75B74E0,TIndyHTTPTransport($991820C) as IROTransport,$A498520,$A498C20,[])
uROBaseHTTPServer.TROBaseHTTPServer.ProcessRequest(TIndyHTTPTransport($991820C) as IROHTTPTransportEx,$A498520,$A498C20,TIPHTTPResponseHeaders($A4CF7D0) as IROHTTPResponse)
uROIndyHTTPServer.TROIndyHTTPServer.InternalServerCommandGet($A50A1F0,$59EF960,$A3208C0)
IdCustomHTTPServer.TIdCustomHTTPServer.DoCommandGet(???,???,$A3208C0)
IdCustomHTTPServer.TIdCustomHTTPServer.DoExecute($A50A1F0)
IdContext.TIdContext.Run
IdTask.TIdTask.DoRun
IdThread.TIdThreadWithTask.Run
IdThread.TIdThread.Execute
System.Classes.ThreadProc($A3AA2C0)
System.ThreadWrapper($A49A630)
:753b343d kernel32.BaseThreadInitThunk + 0x12
:773f9802 ntdll.RtlInitializeExceptionChain + 0x63
:773f97d5 ntdll.RtlInitializeExceptionChain + 0x36
After exception:
uDAEBaseDataset.TDAEBaseDataset.DoSetActive(True)
uDAEBaseDataset.TDAEBaseDataset.SetActive(True)
uDAEBaseDataset.TDAEBaseDataset.Open
DataAbstractService_Impl.TDataAbstractService.GetData_StandardMode($C72F924,$A564280)
DataAbstractService_Impl.TDataAbstractService.InternalGetData($A4D3610,$A4D32A0,$A564280,False)
DataAbstractService_Impl.TDataAbstractService.GetData($A4D3610,$A4D32A0)
DataAbstract4_Invk.TDataAbstractService_Invoker.Invoke_GetData(TPetroBoxQueryService($903227C) as IInterface,TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,[])
uROServer.TROInvoker.CustomHandleMessage(TROClassFactory($737476C) as IROClassFactory,TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,[])
uROServer.TROInvoker.HandleMessage(TROClassFactory($737476C) as IROClassFactory,TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,[])
uROServer.MainProcessMessage(TROBinMessage($9347EA4) as IROMessage,TIndyHTTPTransport($991820C) as IROTransport,$A498520,$A498C20,[],False)
uROServer.TROMessageDispatcher.ProcessMessage(TIndyHTTPTransport($991820C) as IROTransport,$A498520,$A498C20,[],False)
uROServer.TROServer.IntDispatchMessage($75B74E0,TIndyHTTPTransport($991820C) as IROTransport,$A498520,$A498C20,[])
uROBaseHTTPServer.TROBaseHTTPServer.ProcessRequest(TIndyHTTPTransport($991820C) as IROHTTPTransportEx,$A498520,$A498C20,TIPHTTPResponseHeaders($A4CF7D0) as IROHTTPResponse)
uROIndyHTTPServer.TROIndyHTTPServer.InternalServerCommandGet($A50A1F0,$59EF960,$A3208C0)
IdCustomHTTPServer.TIdCustomHTTPServer.DoCommandGet(???,???,$A3208C0)
IdCustomHTTPServer.TIdCustomHTTPServer.DoExecute($A50A1F0)
IdContext.TIdContext.Run
IdTask.TIdTask.DoRun
IdThread.TIdThreadWithTask.Run
IdThread.TIdThread.Execute
System.Classes.ThreadProc($A3AA2C0)
System.ThreadWrapper($A49A630)
:753b343d kernel32.BaseThreadInitThunk + 0x12
:773f9802 ntdll.RtlInitializeExceptionChain + 0x63
:773f97d5 ntdll.RtlInitializeExceptionChain + 0x36do you try to execute this manual SQL?
DataAbstract doesn’t modify any manual SQL. can you check this sql in Schema Modeler again and confirm that you have above SQL and not something like
select field1, field2, field3, from xtable
where
{where}
order by field1, field2
LIMIT 2
?
Yes, we executed the query in a script at sql management studio and works, the Schema Modeller it’s configured to SQL Server and the query works too with maxrecords=50 and 100.
But when it’s executed the TDAMemDataTable this set LIMIT instead of TOP to the query.
can you send your server-side project to support@, pls?
Really?
Im pretty sure if you change max records from to -1 to anything property for the dataset the sql get a added LIMIT (or TOP) sentence to the sql with the max records value, don’t matter if is an auto or manual sql defined on the schema.
OTOH, Is not supposed to work in that way?
Best regards.
I clarify that using manual sql in postgres with maxrecord greater than -1 in the query adds the LIMIT and it works, but performing the same operation with MSSQL erroneously also adds LIMIT instead of TOP.
Hi,
it works only with AutoSQL mode.
if Manual mode is used, we don’t touch original SQL and just write specified max records to stream at serialization of IDADataset.
I need your server-side project for reproducing this issue.
If you can’t send original project, can you create a simple testcase that reproduces this behavior, pls?
You are rigth, my bad, I have a helper to do that.
procedure TDataService.DataAbstractServiceBeforeGetDatasetData(aSender: TObject; const aDataset: IDADataset;
const aIncludeSchema: Boolean; const aMaxRecords: Integer);
begin
uToolsDataAbstractService.doSetSQLimit(aDataset, aMaxRecords);
end;
procedure doSetSQLimit;
begin
if not (aMaxRecords = -1) then
begin
/// 07/09/2017 G.G. -> Si ya tiene limit el dataset salgo.
if (Pos('LIMIT', (aDataset as IDAServerDataset).SQL) = 0) then
begin
(aDataset as IDAServerDataset).SQL := (aDataset as IDAServerDataset).SQL + #13#10 + Format('LIMIT %d',[aMaxRecords]);
end;
end;
end;
Question. How can I detect if the database is Postgres or MS-SQL there? To change use from LIMIT to TOP?
Another question. Why don’t you implement that on the databastract core? Even if the SQL is manual I believe must support maxrecords using database engine.
Best regards.
you can check options of TDAQueryBuilder. it can be got with Connection.GetQueryBuilder.
don’t forget to destroy this object later 
it can be
qboCanUseSelectSkip, // FB
qboCanUseSelectFirst,// FB
qboCanUseSelectTop, // MSSQL+MSACCESS, Nexus
qboCanUseSelectTopSkip, // Nexus
qboCanUseLimit, // MySQL + PostgreSQL
qboCanUseLimitSkip, // MySQL
qboCanUseOffsetSkip, // PostgreSQL
qboCanRows // IB
manual SQL can be pretty specific.
for example, you can get records from stored procedure or use union of selects or use some block of commands, etc so using TOP/LIMIT/etc can broke request.
Great!
Testing code (maybe can help another users).
uses
SysUtils, uDAInterfaces, uDAServerInterfaces, uDASchema;
procedure doSetSQLimit(const aDataset: IDADataset; const aMaxRecords: Integer; aSchema : TDASchema);
implementation
uses
uDAQueryBuilder, uGlobalParamsBase, uDASimpleQueryBuilder;
procedure doSetSQLimit;
var
aConnection : IDAConnection;
aQueryBuilder : TDAQueryBuilder;
begin
aQueryBuilder := nil;
if (aMaxRecords = -1) then exit;
try
aConnection := aSchema.ConnectionManager.NewConnection(uGlobalParamsBase.fDBConexion);
aQueryBuilder := aConnection.GetQueryBuilder;
if qboCanUseLimit in aQueryBuilder.Options then
begin
/// 07/09/2017 G.G. -> Si ya tiene limit el dataset salgo.
if (Pos('LIMIT', (aDataset as IDAServerDataset).SQL) = 0) then
begin
(aDataset as IDAServerDataset).SQL := (aDataset as IDAServerDataset).SQL + #13#10 + Format(' LIMIT %d',[aMaxRecords]);
end;
end
else
begin
if qboCanUseSelectTop in aQueryBuilder.Options then
begin
// 09/10/2019 G.G. -> Soporte a TOP en MS SQL
if (Pos('TOP', (aDataset as IDAServerDataset).SQL) = 0) then
begin
(aDataset as IDAServerDataset).SQL :=
StringReplace((aDataset as IDAServerDataset).SQL, 'SELECT', 'SELECT ' + #13#10 + Format(' TOP %d ',[aMaxRecords]),
[rfIgnoreCase]);
end;
end;
end;
FreeandNil(aQueryBuilder);
except
on e: exception do
begin
FreeandNil(aQueryBuilder);
raise;
end;
end;
end;
BTW. Formatting code destroy indentation ? Don’t know how to fix that, sorry.