Insert TDateTime field to SQL 2008 DB using Native Client 10

Posted on 2012-08-29
Last Modified: 2012-08-31
We have a legacy application that is written in Delphi 2007 and is still using the BDE (yes it needs to be switched to ADO but with over 500K lines, that's a BIG job). It connects to an SQL Server 2008 DB using an SQL SERVER ODBC connection. I am playing around with switching to the SQL Server Native Client 10.0 instead and have come across an interesting issue. When trying to insert a record in to a table that contains datetime fields, we are getting the following error:
Project DateTimeParamTest.exe raised exception class EDBEngineError with message 'General SQL error.
[Microsoft][SQL Server Native Client 10.0]Datetime field overflow. Fractional second precision exceeds the scale specified in
the parameter binding.'.

Open in new window

In doing some research, I have seen comments to play with the NumericScale, Precision, and Size parameters of the TParameter object. A TADOQuery will automatically set the parameters to 3, 23, and 16 respectively and has no problem with the insert. If I set the parameters to the same on the TQuery object, I get the same error as above.

Does anyone have any experience with this and know of an easy work-around? I created the following sample code for anyone wanting to try. You will just need to change the connection and SQL code.

object Form10: TForm10
  Left = 0
  Top = 0
  Caption = 'Form10'
  ClientHeight = 111
  ClientWidth = 181
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button2: TButton
    Left = 20
    Top = 16
    Width = 75
    Height = 25
    Caption = 'BDE'
    TabOrder = 0
    OnClick = Button2Click
  object dbPMbde: TDatabase
    AliasName = 'PMTest'
    DatabaseName = 'DB'
    LoginPrompt = False
    SessionName = 'Default'
    Left = 20
    Top = 52
  object qryBDE: TQuery
    DatabaseName = 'DB'
    SQL.Strings = (
    Left = 88
    Top = 52
    ParamData = <
        DataType = ftInteger
        Name = 'ID'
        ParamType = ptInput
        DataType = ftDateTime
        Precision = 23
        NumericScale = 3
        Name = 'STARTDATE'
        ParamType = ptInput
        Size = 16
        DataType = ftDateTime
        Precision = 23
        NumericScale = 3
        Name = 'ENDDATE'
        ParamType = ptInput
        Size = 16

Open in new window

unit DateTimeParamTest_Main;


  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, DBTables;

  TForm10 = class(TForm)
    Button2: TButton;
    dbPMbde: TDatabase;
    qryBDE: TQuery;
    procedure Button2Click(Sender: TObject);
    { Private declarations }
    { Public declarations }

  Form10: TForm10;


{$R *.dfm}

procedure TForm10.Button2Click(Sender: TObject);
  with qryBDE do
    parambyname('ID').Value := 99999999;
    parambyname('StartDate').Value := now;
    parambyname('EndDate').Value := now;


Open in new window

Question by:CIPortAuthority
    LVL 36

    Accepted Solution

    build a layer on top of a query object
    and then use that new query object

    >> it doesn't matter if you have 500k lines or 500.000.000k lines
    just redefining the layer solves that problem

    i switch from ado to bde to sdac with 1 parameter
    >> but it did take a few months to build the library

    for the date, you can pass it as a string and format it with cast
    convert(datetime, :STARTDATE, 120)

    parambyname('startdate').asString := FormatDateTime(Now, 'YYYY-MM-DD HH:nn:ss');

    Author Comment

    Sorry for my late reply... Normally I get an email saying the question was updated but I didn't receive anything...

    It would be easy to switch by building my own query layer if the underlying application used queries properly.  Unfortunately, the application was originally built over an Access database instead of a proper SQL one.  So there are a lot of Append, Edit, Delete, Post type actions instead of proper SQL.  Also, if I was going to spend a couple of months designing and implementing a new query layer, I could probably redo the app with ADO.

    I'll give you the credit as your answer was pretty close to what I found on another site.  They suggested using
    ParamByName('STARTDATE').AsDateTime := RecodeMillisecond(Now, 0);

    Open in new window

    A more complete discussion can be found here:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now