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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.proudnet.com/proudnet.cn/proudnet/db_system/odbc_api.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.