• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1271
  • Last Modified:

Insert TDateTime field to SQL 2008 DB using Native Client 10

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.

DateTimeParamTest_Main.dfm:
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
  end
  object dbPMbde: TDatabase
    AliasName = 'PMTest'
    DatabaseName = 'DB'
    LoginPrompt = False
    SessionName = 'Default'
    Left = 20
    Top = 52
  end
  object qryBDE: TQuery
    DatabaseName = 'DB'
    SQL.Strings = (
      'INSERT INTO TRAN_DETAIL (ID, STARTDATE, ENDDATE)'
      'VALUES (:ID, :STARTDATE, :ENDDATE);')
    Left = 88
    Top = 52
    ParamData = <
      item
        DataType = ftInteger
        Name = 'ID'
        ParamType = ptInput
      end
      item
        DataType = ftDateTime
        Precision = 23
        NumericScale = 3
        Name = 'STARTDATE'
        ParamType = ptInput
        Size = 16
      end
      item
        DataType = ftDateTime
        Precision = 23
        NumericScale = 3
        Name = 'ENDDATE'
        ParamType = ptInput
        Size = 16
      end>
  end
end

Open in new window

DateTimeParamTest_Main.pas:
unit DateTimeParamTest_Main;

interface

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

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

var
  Form10: TForm10;

implementation

{$R *.dfm}

procedure TForm10.Button2Click(Sender: TObject);
begin
  dbPMbde.Open;
  with qryBDE do
  begin
    parambyname('ID').Value := 99999999;
    parambyname('StartDate').Value := now;
    parambyname('EndDate').Value := now;
    execsql;
  end;
  dbPMbde.Close;
end;

end.

Open in new window

0
CIPortAuthority
Asked:
CIPortAuthority
1 Solution
 
Geert GruwezOracle dbaCommented:
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');

http://msdn.microsoft.com/en-us/library/ms187928
0
 
CIPortAuthorityAuthor Commented:
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: http://stackoverflow.com/questions/12183391/insert-tdatetime-field-to-sql-2008-db-using-native-client-10
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now