Devart Forums

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 299
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 176
    Top = 91
    Width = 320
    Height = 120
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object SQLConnection1: TSQLConnection
    ConnectionName = 'DEVART SQL SERVER DIRECT'
    DriverName = 'DevartSQLServerDirect'
    GetDriverFunc = 'getSQLDriverSQLServerDirect'
    LibraryName = 'dbexpsda40.dll'
    LoginPrompt = False
    Params.Strings = (
      'BlobSize=-1'
      'HostName=.\SQLEXPRESS'
      'DataBase=ADBDEMOS'
      'DriverName=DevartSQLServerDirect'
      'User_Name='
      'Password='
      'LongStrings=True'
      'EnableBCD=True'
      'FetchAll=True'
      'TrimFixedChar=True')
    VendorLib = 'not used'
    Connected = True
    Left = 128
    Top = 72
  end
  object SQLQuery1: TSQLQuery
    MaxBlobSize = -1
    Params = <>
    SQL.Strings = (
      
        'select '#39'hi '#39' as VarChar, CAST('#39'hi '#39' AS CHAR(3)) AS Char, Cast('#39'h' +
        'i '#39' as nvarchar(3)) as NVarChar, CAST('#39'hi '#39' AS NCHAR(3)) AS NCha' +
        'r')
    SQLConnection = SQLConnection1
    Left = 136
    Top = 128
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = SQLQuery1
    Left = 296
    Top = 88
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 368
    Top = 96
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 312
    Top = 152
  end
end

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DBXDevartSQLServer, FMTBcd, DB, StdCtrls, Mask, DBCtrls, DBClient,
  Provider, SqlExpr, Grids, DBGrids;

type
  TForm1 = class(TForm)
    SQLConnection1: TSQLConnection;
    SQLQuery1: TSQLQuery;
    DataSetProvider1: TDataSetProvider;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    ClientDataSet1: TClientDataSet;
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  ClientDataSet1.Close;
  ClientDataSet1.Open;
end;

end.

Code: Select all

SELECT COLUMN_NAME AS name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1 AND TABLE_NAME = 'TBNAME' AND TABLE_SCHEMA = 'dbo'

Code: Select all

function v_FetchQueryPrimaryKey(A_Query: TSQLQuery; A_TableName: string): string;
var
  LColumnDS: TSQLQuery;
  LColumnField: TField;
begin
  Result := '';
  if A_TableName = '' then Exit;

  LColumnDS:= TSQLQuery.Create(A_Query);
  LColumnDS.SQLConnection := A_Query.SQLConnection;
  try
    LColumnDS.SQL.Text :=
      Format('SELECT COLUMN_NAME AS name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 '
            +'AND TABLE_NAME = ''%s'' AND TABLE_SCHEMA = ''dbo'' ', [A_TableName]);
    LColumnDS.Open;
    LColumnField := LColumnDS.Fields[0];
    while not LColumnDS.Eof do
    begin
      if A_Query.FindField(LColumnField.AsString) <> nil then begin
        Result := Result + UpperCase(LColumnField.AsString);

        if LColumnDS.RecNo <> LColumnDS.Recordcount then  // <- NOTE LColumnDS.Recordcount!
          Result := Result + ',';
      end;

      LColumnDS.Next();
    end;
  Finally
    FreeAndNil(LColumnDS);
  end;
end;

Code: Select all

unit Unit2;

interface

uses
  DBXCommon, Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dbxsda, DBXDevartSQLServer, DB, SqlExpr;

type
  TForm2 = class(TForm)
    SQLConnection1: TSQLConnection;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form2: TForm2;

implementation

{$R *.dfm}

initialization
    RegisterDbXpressLib(@getSQLDriverSQLServer);

end.

Code: Select all

procedure TMemSQLCursor.GetField(FieldNo: word; Buffer: pointer; var IsBlank: LongBool);
...
    FRecordSet.GetField(Field, FRecBuf, Buffer, BufferLen, False, _IsBlank);
...

Code: Select all

const DEVART_MSSQL_CONNECTION_STRING = ''
  +'%s=DevartSQLServer;%s=%s;%s=%s;%s=%s;%s=%s'
  +';BlobSize=-1;SchemaOverride=%%.dbo;LongStrings=True;EnableBCD=True'
  +';FetchAll=True;UseUnicode=True;IPVersion=IPv4';

procedure TForm1.Button1Click(Sender: TObject);
var
  cn: TDBXConnection;
  FConnectionProps: TDBXProperties;
  FConnectionFactory: TDBXConnectionFactory;
  cmd: TDBXCommand;
  rer: TDBXReader;
begin
  FConnectionProps := TDBXProperties.Create;
  FConnectionProps.SetProperties(Format(DEVART_MSSQL_CONNECTION_STRING,
                        [TDBXPropertyNames.DriverName,
                         TDBXPropertyNames.HostName, 'localhost',
                         TDBXPropertyNames.Database, 'ADBDEMOS',
                         TDBXPropertyNames.UserName, 'sa',
                         TDBXPropertyNames.Password, 'saps']));
  FConnectionFactory := TDBXConnectionFactory.GetConnectionFactory;
  cn := FConnectionFactory.GetConnection(FConnectionProps);
  cmd := cn.CreateCommand;
  cmd.Text := 'SELECT * FROM employee where empno=:empno';
  cmd.Prepare;
  if cmd.Parameters.Count > 0 then
  begin
    if cmd.IsPrepared then
    begin
      rer := cmd.ExecuteQuery;
      if rer.Next then
        Caption := rer.Values[0].AsString;
      rer.Free;
    end;
  end;
  cmd.Free;
end;