Databases
StackOverflow2013
Postsdbo
POInsert TDateTime field to SQL 2008 DB using Native Client 10
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInsert TDateTime field to SQL 2008 DB using Native Client 10
    primarykey
    data
    text
    <p>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:</p> <pre><code>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.'. </code></pre> <p>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.</p> <p>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.</p> <p>DateTimeParamTest_Main.dfm:</p> <pre><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 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 = &lt; 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&gt; end end </code></pre> <p>DateTimeParamTest_Main.pas:</p> <pre><code>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. </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTLama
    1. USCaynadian
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POInsert TDateTime field to SQL 2008 DB using Native Client 10
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POInsert TDateTime field to SQL 2008 DB using Native Client 10
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POInsert TDateTime field to SQL 2008 DB using Native Client 10
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWith ADO, you should better use the OleDB native provider, not the ODBC one: you are adding an unnecessary layer in-between your app and MS SQL. And ODBC need an explicit configuration in its control panel, whereas you can set an OleDB connection by code. See also third party components (paid or [or Open Source layer](http://blog.synopse.info/post/2011/06/27/SynOleDB%3A-OpenSource-Unit-for-direct-access-to-any-database-via-OleDB)).
      singulars
      1. POInsert TDateTime field to SQL 2008 DB using Native Client 10
      1. USArnaud Bouchez
    2. COStart with using 'AsString' for the `ID`, and `AsDateTime` for the dates instead of `Value`. Always use native Delphi types instead of variants when possible.
      singulars
      1. POInsert TDateTime field to SQL 2008 DB using Native Client 10
      1. USKen White
    3. COI'm not using ADO unfortunately - the whole project is BDE. I tried changing the .value parameters to .AsInteger/.AsDateTime but I still get the same error.
      singulars
      1. POInsert TDateTime field to SQL 2008 DB using Native Client 10
      1. USCaynadian
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload