mormot2笔记(一) 连接数据库

合集 - mormot2(5)

1.mormot2 笔记(二) Http服务的简单搭建2023-08-172.先作个记录，后面再研究为什么2023-08-22

3.mormot2笔记(一) 连接数据库2023-08-15

4.mormot2 笔记(三) 实体转JSON2023-08-255.mormot2 笔记(五) 储存过程调用2024-01-07

收起

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.Grids, Vcl.StdCtrls,
  mormot.db.sql, mormot.db.core, mormot.db.sql.oledb, mormot.core.base;

type
  TForm1 = class(TForm)
    StringGrid1: TStringGrid;
    Memo1: TMemo;
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

  TSqlDBOleDBMSSQLConnectionPropertiesEx = class(TSqlDBOleDBMSSQLConnectionProperties)
  public
    constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUtf8); override;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  var conPool := TSqlDBOleDBMSSQLConnectionPropertiesEx.Create('192.168.1.2', 'xb', 'sa', '223344');
  var dataTable := conPool.ExecuteInlined('select top 10 * from TestTable', true);
  StringGrid1.ColCount := dataTable.ColumnCount;
  StringGrid1.RowCount := 100;
  var rowIndex := 1;
  while dataTable.Step do
  begin
    for var I := 0 to dataTable.ColumnCount-1 do
    begin
      StringGrid1.Cells[I, rowIndex] := dataTable.ColumnString(I);
    end;
    inc(rowIndex);
  end;
end;

{ TSqlDBOleDBMSSQLConnectionPropertiesEx }

constructor TSqlDBOleDBMSSQLConnectionPropertiesEx.Create(const aServerName,
  aDatabaseName, aUserID, aPassWord: RawUtf8);
begin
  fProviderName := 'SQLOLEDB.1';
  inherited Create(aServerName,aDatabaseName, aUserID, aPassWord);
end;


end.

 

注：由于 ProviderName 属性是只读的，所以在create中初始化一下（能不修改源代码就不改）

mormot.db.sql.oledb   单元中定义了mssql相关类，如果连接其它数据库则要引用相对应的文件。

 

ODBC连接，注释的几种都是可行的：

  FDBProp := TSqlDBOdbcConnectionProperties.Create('321', '', 'sa', '12345');   //已经建好321连接，使用OLEDB驱动,不能省略【用户名】和【密码】
  FDBProp.Dbms := TSqlDBDefinition.dMSSQL;
//  FDBProp := TODBCProp.Create('123', '', 'sa', '12345'); //同上 123使用ODBC驱动， 不能省略【用户名】和【密码 】
//  FDBProp := TODBCProp.Create('', 'DSN=123;UID=sa;PWD=Sa12345;DATABASE=MyDB', '', '');  //使用ODBC驱动，可以省略【用户名】和【密码】
//  FDBProp := TODBCProp.Create('', 'DRIVER=ODBC Driver 17 for SQL Server;UID=sa;PWD=12345;Trusted_Connection=No;SERVER=192.168.1.50;MARS_Connection=yes;DATABASE=MyDB', '', ''); //这样也可以
//  FDBProp.SqlDriverConnectPrompt := TRUE;   //会弹出ODBC设置对话框

  var dbProp := TSqlDBOdbcConnectionProperties.Create('', 'FILEDSN=d:\test.dsn;PWD=Sa12345', '', '');

备注：DEUBG运行时，执行到 CreateMissingTables 方法时报函数序列错误，但脱离IDE环境执行是不报错.。

 

  //改下两个都可以行
  var constr := 'ODBCDriver=ODBC Driver 18 for SQL Server;User_Name=sa;Password=Sa12345;Database=platform_bh;ODBCAdvanced={SERVER=192.168.1.50;Trusted_Connection=No;Encrypt=Optional;TrustServerCertificate=Yes;AutoTranslate=No;Regional=Yes;TransparentNetworkIPResolution=Disabled};DriverID=ODBC';
  var str := 'ODBCDriver=SQL Server;User_Name=sa;Password=Sa12345;Database=CTLed;ODBCAdvanced={SERVER=JJW-PC\SQLEXPRESS;Regional=Yes};DriverID=ODBC';
  self.FDConnection1.ConnectionString := str;
  self.FDConnection1.Open();
  var db := TSqlDBFireDacConnectionProperties.Create('MSSQL?Server=192.168.1.50;Encrypt=no', 'testDB', 'sa', 'Sa12345');
//  var db := TSqlDBFireDacConnectionProperties.Create('ODBC?Server=192.168.1.50;Encrypt=no', 'platform_tj', 'sa', 'Sa12345');  无ODBC对应，会报异常
  var rows := db.ExecuteInlined('select * from T1', true);
  memo1.Text := rows.StepAsJson();

备：

[ODBC]
DRIVER=ODBC Driver 18 for SQL Server
UID=sa
TransparentNetworkIPResolution=Disabled
Regional=No
TrustServerCertificate=Yes
Encrypt=Optional
LANGUAGE=简体中文
DATABASE=MyDB
WSID=JJW-PC
APP=Microsoft? Windows? Operating System
Trusted_Connection=No
SERVER=192.168.1.50
PWD=12345

 

 

  var dsn := rawbytestring('FILEDSN='+ExtractFilePath(ParamStr(0))+'db.dsn');
  var db := TSqlDBODBCConnectionProperties.Create('', dsn, '', '');