2014年10月01日

Delphi XE6 FireDAC Connection Oracle Database Express Edition 11g


1. GroupBox1に、LabeledEdit1 〜 LabeledEdit6まで、配置します。(GroupBox1でなくてもOKですが、のちほど、コピーして使いまわせるようにしています)
    LabeledEdit1.Caption  [Server]
    LabeledEdit2.Caption  [Database]
    LabeledEdit3.Caption  [user]
    LabeledEdit4.Caption  [Password]
    LabeledEdit5.Caption  [ServerCharSet]
    LabeledEdit6.Caption  [VendorLib]

FireDACコンポーネント
  FDConnection1
  FDPhysOracleDriverLink1
  FDGUIxWaitCursor1
  FDQuery1

SpeedButton1 →接続
SpeedButton2 →Create Table
SpeedButton3 →Drop Table

Ora001.png
2.  FDConnection1→DriverName→Ora
Ora002.png
3.   FDQuery1→Connection→FDConnection1
Ora003.png
4. 実行して、接続ボタンを押します。
Ora010.png

5.[FireDAC][Phys][Ora]ORA-12154: TNS: could not resolve the connect identifier specified.
のエラーが表示されます。
Ora004_err.png
5. Debugフォルダ
Ora005.png
6. oci.dll
oraociei11.dll
tnsnames.ora 
の3つが、必要です。
Ora006.png
7. 再度、接続ボタンで、Connection Successful となれば、接続成功です。
Ora008.png
8. Create Tableボタンを押して、テーブルを作成します。
Ora009.png
9. すでに、テーブルが存在すると、
[FireDAC][Phys][Ora]ORA-00955: name is already used by an existing object
となります。
Ora007_err.png



//------------------------------------------------------------------------------
//Delphi XE6 FireDAC Connection
//Oracle_11g_XE
//------------------------------------------------------------------------------
unit FD_Con_Oracle_11g_XE_Unit;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
  FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async,
  FireDAC.Phys, FireDAC.Phys.Oracle, FireDAC.VCLUI.Wait, FireDAC.Stan.Param,
  FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client, FireDAC.Comp.UI, Vcl.Buttons;

type
  TForm1 = class(TForm)
    GroupBox1: TGroupBox;
    LabeledEdit1: TLabeledEdit;
    LabeledEdit2: TLabeledEdit;
    LabeledEdit3: TLabeledEdit;
    LabeledEdit4: TLabeledEdit;
    LabeledEdit5: TLabeledEdit;
    LabeledEdit6: TLabeledEdit;
    Panel1: TPanel;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    Label1: TLabel;
    FDConnection1: TFDConnection;
    FDPhysOracleDriverLink1: TFDPhysOracleDriverLink;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    FDQuery1: TFDQuery;
    SpeedButton3: TSpeedButton;
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
  private
    { Private 宣言 }
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
  FDConnection1.Params.Values['Hostname']   :=LabeledEdit1.Text;
  FDConnection1.Params.Values['Database']   :=LabeledEdit2.Text;
  FDConnection1.Params.Values['User_Name']  :=LabeledEdit3.Text;
  FDConnection1.Params.Values['Password']   :=LabeledEdit4.Text;
  FDConnection1.Params.Values['CharacterSet']:=LabeledEdit5.Text;
  FDPhysOracleDriverLink1.VendorLib :=LabeledEdit6.Text;

  FDConnection1.Connected:=True;
  if FDConnection1.Connected=True  then Label1.Caption:='Connection Successful '
  else                                  Label1.Caption:='Not connected';
end;

procedure TForm1.SpeedButton2Click(Sender: TObject);
var
  sqlstmt:string;
begin
  sqlstmt:='CREATE TABLE TEST_TBL '+
  '('+
  'TS_NO      INTEGER  not null,'+
  'TS_NAME    VARChar(30) ,'+
  'TS_QTY     DECIMAL(14,2) Default 0 ,'+
  'TS_BLOB    BLOB ,'+
  'primary key(TS_NO)'+
  ')';
  if FDConnection1.Connected=true then
  begin
    try
      FDQuery1.SQL.Clear;
      FDQuery1.SQL.Add(sqlstmt);
      FDQuery1.ExecSQL();
      ShowMessage('CREATE TABLE');
    except
      on E: EFDDBEngineException do
      ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
    end;
  end
  else ShowMessage('Connected ERR');
end;

procedure TForm1.SpeedButton3Click(Sender: TObject);
var
  sqlstmt:string;
begin
  sqlstmt:='DROP TABLE TEST_TBL ';
  if FDConnection1.Connected=true then
  begin
    try
      FDQuery1.SQL.Clear;
      FDQuery1.SQL.Add(sqlstmt);
      FDQuery1.ExecSQL();
      ShowMessage('DROP TABLE');
    except
      on E: EFDDBEngineException do
      ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
    end;
  end
  else ShowMessage('Connected ERR');
end;

end.





posted by a23 at 09:01| Comment(0) | 01. Oracle Express

2014年10月02日

Delphi XE6 FireDAC Connection Microsoft SQL Server 2014 Express


1. GroupBox1に
    LabeledEdit1.Caption  [Server]
    LabeledEdit2.Caption  [Database]
    LabeledEdit3.Caption  [user]
    LabeledEdit4.Caption  [Password]
    LabeledEdit5.Caption  [ServerCharSet]
    LabeledEdit6.Caption  [VendorLib]   (2012までは、指定必要 / 2014は指定不要?)

FireDACコンポーネント
  FDConnection1
  FDPhysMSSQLDriverLink1 
  FDGUIxWaitCursor1
  FDQuery1

SpeedButton1 →接続
SpeedButton2 →Create Table
SpeedButton3 →Drop Table

ms001.png
2. FDConnection1→DriverName→MSSQL
ms002.png
3. FDQuery1→Connection→FDConnection1
ms003.png
4. 接続ボタンを押す。 (2012までは、sqlncli11.dllを指定し、実行時にパスを通す必要がありましが、2014では、指定が不要となったようです、間違ってたら、ご指摘ください)
ms004.png
5. Create TABLEボタンを押す。
ms005.png
6. CreateTABLEで、 BOLBは、エラーとなりますので、binary か varbinary に変更します。
ms_blob.png

//------------------------------------------------------------------------------
//Delphi XE6 FireDAC Connection
//Microsoft SQL Server 2014 Express
//------------------------------------------------------------------------------
unit FD_Con_MSSQL2014_Unit;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param,
  FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf,
  FireDAC.Stan.Async, FireDAC.DApt, FireDAC.UI.Intf, FireDAC.VCLUI.Wait,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Phys, FireDAC.Phys.MSSQL,
  Data.DB, FireDAC.Comp.Client, FireDAC.Phys.ODBCBase, FireDAC.Comp.UI,
  FireDAC.Comp.DataSet, Vcl.Buttons;

type
  TForm1 = class(TForm)
    GroupBox1: TGroupBox;
    LabeledEdit1: TLabeledEdit;
    LabeledEdit2: TLabeledEdit;
    LabeledEdit3: TLabeledEdit;
    LabeledEdit4: TLabeledEdit;
    LabeledEdit5: TLabeledEdit;
    LabeledEdit6: TLabeledEdit;
    Panel1: TPanel;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    Label1: TLabel;
    SpeedButton3: TSpeedButton;
    FDQuery1: TFDQuery;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    FDPhysMSSQLDriverLink1: TFDPhysMSSQLDriverLink;
    FDConnection1: TFDConnection;
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
  private
    { Private 宣言 }
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
  FDConnection1.Params.Values['Server']     :=LabeledEdit1.Text;
  FDConnection1.Params.Values['Database']   :=LabeledEdit2.Text;
  FDConnection1.Params.Values['User_Name']  :=LabeledEdit3.Text;
  FDConnection1.Params.Values['Password']   :=LabeledEdit4.Text;
  FDConnection1.Params.Values['CharacterSet']:=LabeledEdit5.Text;
  //FDPhysMSSQLDriverLink1.VendorLib    :=LabeledEdit6.Text;

  FDConnection1.Connected:=True;
  if FDConnection1.Connected=True  then Label1.Caption:='Connection Successful '
  else                                  Label1.Caption:='Not connected';
end;

procedure TForm1.SpeedButton2Click(Sender: TObject);
var
  sqlstmt:string;
begin
  sqlstmt:='CREATE TABLE TEST_TBL '+
  '('+
  'TS_NO      INTEGER  not null,'+
  'TS_NAME    VARChar(30) ,'+
  'TS_QTY     DECIMAL(14,2) Default 0 ,'+
  //'TS_BLOB    BLOB ,'+
  //'TS_BLOB    Binary ,'+
  'TS_BLOB    VarBinary ,'+
  'primary key(TS_NO)'+
  ')';
  if FDConnection1.Connected=true then
  begin
    try
      FDQuery1.SQL.Clear;
      FDQuery1.SQL.Add(sqlstmt);
      FDQuery1.ExecSQL();
      ShowMessage('CREATE TABLE');
    except
      on E: EFDDBEngineException do
      ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
    end;
  end
  else ShowMessage('Connected ERR');
end;

procedure TForm1.SpeedButton3Click(Sender: TObject);
var
  sqlstmt:string;
begin
  sqlstmt:='DROP TABLE TEST_TBL ';
  if FDConnection1.Connected=true then
  begin
    try
      FDQuery1.SQL.Clear;
      FDQuery1.SQL.Add(sqlstmt);
      FDQuery1.ExecSQL();
      ShowMessage('DROP TABLE');
    except
      on E: EFDDBEngineException do
      ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
    end;
  end
  else ShowMessage('Connected ERR');
end;

end.


posted by a23 at 11:48| Comment(0) | 02. MS SQL Server Express