> For the complete documentation index, see [llms.txt](https://docs.proudnet.com/proudnet.cn/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.proudnet.com/proudnet.cn/proudnet/db_system/odbc_api.md).

# ODBC API

和[**ADO API**](/proudnet.cn/proudnet/db_system/ado_api.md)一樣，也可以利用<mark style="color:orange;">ODBC</mark>使用DB。

{% hint style="info" %}
以下說明是根據[**git 示例代碼**](https://github.com/Nettention/ProudNet_Sample/tree/main/SimpleOdbc)編寫的。
{% endhint %}

## 對象定義

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

## 連接DB

作爲參數進入Open函數的語句遵循[**Connection Strings**](https://www.connectionstrings.com/)規則。

{% tabs %}
{% tab title="MSSQL" %}

<pre class="language-cpp"><code class="lang-cpp"><strong>conn.Open(Proud::String::NewFormat(L"Driver={ODBC Driver 17 for SQL Server};Server=localhost;Trusted_Connection=yes;"), &#x26;warnigs);
</strong></code></pre>

{% endtab %}

{% tab title="MYSQL" %}

```cpp
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;"));
```

{% endtab %}
{% endtabs %}

## 使用DB

### -  使用Query

{% tabs %}
{% tab title="MSSQL" %}

```cpp
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();
```

{% endtab %}

{% tab title="MYSQL" %}

```cpp
// 用法範例 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);
```

{% endtab %}
{% endtabs %}

### - 使用Query prepare

```cpp
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

{% tabs %}
{% tab title="MSSQL" %}

```cpp
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();
```

{% endtab %}

{% tab title="MYSQL" %}

```cpp
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();
```

{% endtab %}
{% endtabs %}

### - 用 Recordset 讀取數據

```cpp
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());
}
```