ODBC API

和ADO API一樣，也可以利用ODBC使用DB。

以下說明是根據git 示例代碼編寫的。

對象定義

// 要設置連接的對象
Proud::COdbcConnection conn;
// 要設置命令的對象
Proud::COdbcCommand cmd;
// 包含查詢結果的對象
Proud::COdbcRecordset record;
// 不需使用查詢錯誤作爲對象也無妨。
Proud::COdbcWarnings warnings;

連接DB

作爲參數進入Open函數的語句遵循Connection Strings規則。

MSSQL

conn.Open(Proud::String::NewFormat(L"Driver={ODBC Driver 17 for SQL Server};Server=localhost;Trusted_Connection=yes;"), &warnigs);

MYSQL

conn.Open(Proud::String::NewFormat(L"Driver={MySQL ODBC 8.0 Unicode Driver};Server=127.0.0.1;Port=3306;Uid=proud;Pwd=proudnet123;Option=3;"));

使用DB

- 使用Query

MSSQL

conn.BeginTrans();

// 用法範例 1
conn.Execute(_PNT("CREATE TABLE test(id int primary key, name nvarchar(30))"));

// 用法範例 2
Proud::String query = _PNT("INSERT INTO test(id) VALUES(1)");
result = conn.Execute(query);

conn.CommitTrans();

MYSQL

// 用法範例 1
conn.Execute(_PNT("CREATE TABLE test(id int primary key, name varchar(30))"));

// 用法範例 2
Proud::String query = _PNT("INSERT INTO test(id) VALUES(1)");
result = conn.Execute(query);

- 使用Query prepare

int id;
Proud::String name;

cmd.Prepare(conn, _PNT("INSERT INTO test(id, name) VALUES(?, ?)"));
cmd.AppendInputParameter(&id);
cmd.AppendInputParameter(&name);

id = 5;
name = _PNT("Nettention");
result = cmd.Execute();

- 使用Stored procedure

MSSQL

int ret;
int id;
Proud::String name;

// 只有input parameter時
cmd.Prepare(conn, _PNT("{call insertTest(?,?)}"));
cmd.AppendInputParameter(&id);
cmd.AppendInputParameter(&name);

id = 8;
name = _PNT("SPTest");
result = cmd.Execute();

// output parameter存在時
cmd.Prepare(conn, _PNT("{? = call outputTest(?,?)}"));
cmd.AppendOutputParameter(&ret);
cmd.AppendInputParameter(&id);
cmd.AppendOutputParameter(&name);

id = 5;
name.GetBuffer(100);
result = cmd.Execute();
name.ReleaseBuffer();

MYSQL

int id;
Proud::String name;

// input
cmd.Prepare(conn, _PNT("call insertTest(?,?)"));
cmd.AppendInputParameter(&id);
cmd.AppendInputParameter(&name);

id = 8;
name = _PNT("SPTest");
result = cmd.Execute();

// output
cmd.Prepare(conn, _PNT("call outputTest(?,?)"));
cmd.AppendInputParameter(&id);
cmd.AppendOutputParameter(&name);

id = 5;
name.GetBuffer(100);
result = cmd.Execute();
name.ReleaseBuffer();

- 用 Recordset 讀取數據

int id;
Proud::String name;

result = conn.Execute(record, _PNT("SELECT * FROM test"));
while (record.MoveNext())
{
	id = record.GetFieldValue(_PNT("id"));
	name = record.GetFieldValue(_PNT("name"));
	_tprintf_s(_PNT("id = %d, name = %ws\n"), id, name.GetString());
}