> For the complete documentation index, see [llms.txt](https://docs.proudnet.com/proudnet.eng/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.eng/proudnet/db_system/odbc_api.md).

# ODBC API

As with the [**ADO API**](/proudnet.eng/proudnet/db_system/ado_api.md), you can also use <mark style="color:orange;">ODBC</mark> to access the DB.

{% hint style="info" %}
The description below is based on the [**git example code**](https://github.com/Nettention/ProudNet_Sample/tree/main/SimpleOdbc).
{% endhint %}

## Object Definitions

```cpp
// The object to establish the connection with
Proud::COdbcConnection conn;
// The object to set the command on
Proud::COdbcCommand cmd;
// The object to hold the query results
Proud::COdbcRecordset record;
// There is no need to use it as an object to contain query errors.
Proud::COdbcWarnings warnings;
```

## Connecting to DB

The syntax entered as a parameter to the Open function follows the [**Connection Strings**](https://www.connectionstrings.com/) rules.

{% 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 %}

## Using DB

### -  Using Query

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

```cpp
conn.BeginTrans();

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

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

conn.CommitTrans();
```

{% endtab %}

{% tab title="MYSQL" %}

```cpp
// Example 1
conn.Execute(_PNT("CREATE TABLE test(id int primary key, name varchar(30))"));

// Example 2
Proud::String query = _PNT("INSERT INTO test(id) VALUES(1)");
result = conn.Execute(query);
```

{% endtab %}
{% endtabs %}

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

### - Using stored procedures

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

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

// If only an input parameter is present
cmd.Prepare(conn, _PNT("{call insertTest(?,?)}"));
cmd.AppendInputParameter(&id);
cmd.AppendInputParameter(&name);

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

// If the output parameter is present
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 %}

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