We are using C++Builder 6 for an old product, and ran into a problem with comparison of DateTime objects failing if they contain milliseconds.
This problem seems to be present even in Visual Studio C#, but one of our colleagues already found the cause and a solution there.
The problem is that, after the query is created, it will use datetime2 as a parameter, however, comparisons with datetime2 will fail if the time contains milliseconds. In Visual Studio C#, this was solved by manually manipulating the query in the events:
var dbParameters = command.Parameters.OfType<DbParameter>().Where(p => p.DbType == DbType.DateTime2);
foreach(var parameter in dbParameters)
{
parameter.DbType = DbType.DateTime;
}
Basically, what it does is changes a query from this:
exec sp_executesql N'select *
from Table
where Date = @P1
',N'@P1 datetime2(0)','2022-05-18 15:24:17.840'
To this:
exec sp_executesql N'select *
from Table
where Date = @P1
',N'@P1 datetime','2022-05-18 15:24:17.840'
Note: The former query will not return anything, while the latter returns the expected result.
I need a similar solution for C++Builder 6, but I can't seem to find any place where I can even access these parameters.
I tried the TADOConnection.OnWillExecute event, but it seems to pass only the CommandText with ? in place of the parameters, but not the parameters themselves.
Is there any place where I can do such a query manipulation in C++Builder 6 with ADO components?
Sample project: UntDateTimeTestMain.cpp
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "UntDateTimeTestMain.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TFrmDateTimeTestMain *FrmDateTimeTestMain;
//---------------------------------------------------------------------------
__fastcall TFrmDateTimeTestMain::TFrmDateTimeTestMain(TComponent* Owner)
: TForm(Owner)
{
bConnect->Focused();
}
//---------------------------------------------------------------------------
void __fastcall TFrmDateTimeTestMain::bConnectClick(TObject *Sender)
{
bool ConnectionOK = false;
ADOConnection->ConnectionString = "Provider=MSOLEDBSQL;Password=" + eDbPassword->Text
+ ";Persist Security Info=True;User ID=" + eDbUser->Text
+ ";Initial Catalog=" + eDatabase->Text
+ ";Data Source=" + eServer->Text
+ ";OLE DB Services = -2;Application Name=DateTimeTestTool";
ADOConnection->Connected = true;
ADOQueryBgTasks->Open();
}
//---------------------------------------------------------------------------
void __fastcall TFrmDateTimeTestMain::ADOQueryBgTasksAfterOpen(
TDataSet *DataSet)
{
lEntryTime->Caption = "EntryTime: " + ADOQueryBgTasks->FieldByName("EntryTime")->AsString;
}
//---------------------------------------------------------------------------
void __fastcall TFrmDateTimeTestMain::bUpdateClick(TObject *Sender)
{
//ADOQueryBgTasks->Post();
ADOQueryUpdateBgTasks->Parameters->ParamByName("Command")->Value = dbeCommand->Text;
ADOQueryUpdateBgTasks->Parameters->ParamByName("EntryTime")->Value = ADOQueryBgTasks->FieldByName("EntryTime")->AsDateTime;
ADOQueryUpdateBgTasks->ExecSQL();
}
//---------------------------------------------------------------------------
UntDateTimeTestMain.dfm
object FrmDateTimeTestMain: TFrmDateTimeTestMain
Left = 765
Top = 234
Width = 314
Height = 314
Caption = 'DateTime Test Tool'
Color = clBtnFace
Constraints.MinHeight = 314
Constraints.MinWidth = 314
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
Position = poScreenCenter
DesignSize = (
306
283)
PixelsPerInch = 96
TextHeight = 13
object Label1: TLabel
Left = 16
Top = 16
Width = 34
Height = 13
Caption = 'Server:'
end
object Label2: TLabel
Left = 16
Top = 48
Width = 49
Height = 13
Caption = 'Database:'
end
object Label3: TLabel
Left = 16
Top = 80
Width = 25
Height = 13
Caption = 'User:'
end
object Label4: TLabel
Left = 16
Top = 112
Width = 49
Height = 13
Caption = 'Password:'
end
object Label5: TLabel
Left = 16
Top = 144
Width = 50
Height = 13
Caption = 'Command:'
end
object lEntryTime: TLabel
Left = 16
Top = 176
Width = 50
Height = 13
Caption = 'EntryTime:'
end
object eServer: TEdit
Left = 72
Top = 12
Width = 222
Height = 21
Anchors = [akLeft, akTop, akRight]
TabOrder = 1
end
object eDatabase: TEdit
Left = 72
Top = 44
Width = 222
Height = 21
Anchors = [akLeft, akTop, akRight]
TabOrder = 2
end
object eDbUser: TEdit
Left = 72
Top = 76
Width = 131
Height = 21
Anchors = [akLeft, akTop, akRight]
TabOrder = 3
end
object eDbPassword: TEdit
Left = 72
Top = 108
Width = 131
Height = 21
Anchors = [akLeft, akTop, akRight]
PasswordChar = '*'
TabOrder = 4
end
object bConnect: TButton
Left = 218
Top = 104
Width = 75
Height = 25
Anchors = [akTop, akRight]
Caption = 'Connect'
TabOrder = 0
OnClick = bConnectClick
end
object dbeCommand: TDBEdit
Left = 72
Top = 140
Width = 222
Height = 21
Anchors = [akLeft, akTop, akRight]
DataField = 'Command'
DataSource = DataSource1
TabOrder = 5
end
object bUpdate: TButton
Left = 218
Top = 200
Width = 75
Height = 25
Caption = 'Update'
TabOrder = 6
OnClick = bUpdateClick
end
object ADOConnection: TADOConnection
CommandTimeout = 300
LoginPrompt = False
Provider = 'MSOLEDBSQL'
Left = 208
Top = 72
end
object ADOQueryBgTasks: TADOQuery
Connection = ADOConnection
AfterOpen = ADOQueryBgTasksAfterOpen
Parameters = <>
SQL.Strings = (
'SELECT * FROM BGTasks')
Left = 240
Top = 72
end
object DataSource1: TDataSource
DataSet = ADOQueryBgTasks
Left = 272
Top = 72
end
object ADOQueryUpdateBgTasks: TADOQuery
Connection = ADOConnection
Parameters = <
item
Name = 'Command'
Size = -1
Value = Null
end
item
Name = 'EntryTime'
Size = -1
Value = Null
end>
SQL.Strings = (
'UPDATE BGTasks'
'SET Command = :Command'
'WHERE EntryTime = :EntryTime')
Left = 184
Top = 192
end
end
UntDateTimeTestMain.h
//---------------------------------------------------------------------------
#ifndef UntDateTimeTestMainH
#define UntDateTimeTestMainH
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <Controls.hpp>
#include <StdCtrls.hpp>
#include <Forms.hpp>
#include <ADODB.hpp>
#include <DB.hpp>
#include <DBCtrls.hpp>
#include <Mask.hpp>
//---------------------------------------------------------------------------
class TFrmDateTimeTestMain : public TForm
{
__published: // Von der IDE verwaltete Komponenten
TADOConnection *ADOConnection;
TLabel *Label1;
TEdit *eServer;
TLabel *Label2;
TLabel *Label3;
TLabel *Label4;
TEdit *eDatabase;
TEdit *eDbUser;
TEdit *eDbPassword;
TButton *bConnect;
TADOQuery *ADOQueryBgTasks;
TDBEdit *dbeCommand;
TLabel *Label5;
TDataSource *DataSource1;
TLabel *lEntryTime;
TButton *bUpdate;
TADOQuery *ADOQueryUpdateBgTasks;
void __fastcall bConnectClick(TObject *Sender);
void __fastcall ADOQueryBgTasksAfterOpen(TDataSet *DataSet);
void __fastcall bUpdateClick(TObject *Sender);
private: // Anwender-Deklarationen
public: // Anwender-Deklarationen
__fastcall TFrmDateTimeTestMain(TComponent* Owner);
};
//---------------------------------------------------------------------------
extern PACKAGE TFrmDateTimeTestMain *FrmDateTimeTestMain;
//---------------------------------------------------------------------------
#endif
Resulting query:
exec sp_executesql N'UPDATE BGTasks
SET Command = @P1
WHERE EntryTime = @P2
',N'@P1 varchar(30),@P2 datetime2(0)','EXEC St_Proc_Download_Location','2022-02-07 10:50:50'
As you can see it results in using "datetime2(0)" as type and on top of that does not even pass the milliseconds in the time (the actual time in the database is 2022-02-07 10:50:50.487).
I tried changing the type from unknown to DateTime but it results in exactly the same query.
