psqlODBC

https://odbc.postgresql.org/
Admin
psqlODBC is the official PostgreSQL ODBC driver that enables applications to connect to and interact...
Tokens:18,803
Snippets:232
Trust Score:7.5
Update:6 months ago
Show doc for...
Tokens:
Raw
### Build and install from repository

Source: https://odbc.postgresql.org/docs/unix-compilation.html

Commands to configure and build the driver after bootstrapping.

```shell
./configure
make
make install
```

--------------------------------

### Install psqlODBC from source

Source: https://odbc.postgresql.org/docs/unix-compilation.html

Standard sequence of commands to extract, configure, and install the driver from a tarball.

```shell
% tar -zxvf psqlodbc-xx.xx.xxxx.tar.gz
% cd psqlodbc-xx.xx.xxxx
% ./configure
% make
% make install
```

--------------------------------

### Build MSI Installer with nmake.exe

Source: https://odbc.postgresql.org/docs/win32-compilation.html

Command to build the .msi installer file using nmake and the win64.mak makefile. The output will be in the installer/x64 or installer/x86 directory.

```makefile
C:\psqlodbc\> nmake /f win64.mak installer
```

--------------------------------

### Basic ODBC Connection Setup in Ch

Source: https://odbc.postgresql.org/howto-ch.html

This C code demonstrates the fundamental steps for initializing an ODBC environment and establishing a database connection using the Ch interpreter. It includes allocating handles, setting environment attributes, and configuring connection attributes. Proper error checking after each step is crucial.

```c
#include <sqlext.h>
#include <stdio.h>

void ODBC_error (       /* Get and print ODBC error messages */
    SQLHENV henv,       /* ODBC Environment */
    SQLHDBC hdbc,       /* ODBC Connection Handle */
    SQLHSTMT hstmt)     /* ODBC SQL Handle */
{
    UCHAR   sqlstate[10];
    UCHAR   errmsg[SQL_MAX_MESSAGE_LENGTH];
    SDWORD  nativeerr;
    SWORD   actualmsglen;
    RETCODE rc = SQL_SUCCESS;

    while ( rc != SQL_NO_DATA_FOUND)
    {
        rc = SQLError(henv, hdbc, hstmt,
                      sqlstate, &nativeerr, errmsg,
                      SQL_MAX_MESSAGE_LENGTH - 1, &actualmsglen);
                     
         if (rc == SQL_ERROR) {
              printf ("SQLError failed!\n");
              return;
         }

         if (rc != SQL_NO_DATA_FOUND) {
               printf ("SQLSTATE = %s\n", sqlstate);
               printf ("NATIVE ERROR = %d\n", nativeerr);
               errmsg[actualmsglen] = '\0';
               printf ("MSG = %s\n\n", errmsg);
          }
     }
     if (hdbc != SQL_NULL_HDBC)
     {
        SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
     }
     if (henv != SQL_NULL_HENV)
     {
        SQLFreeHandle (SQL_HANDLE_ENV, henv);
     }
}

int main(void)
{
    SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc = SQL_NULL_HDBC;
    SQLHSTMT hstmt = SQL_NULL_HSTMT;
    RETCODE  rc    = SQL_SUCCESS;

    rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

    if (rc != SQL_ERROR)
    {
        printf("SQLAllocHandle() OK\n");
        rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3,0);
        if (rc != SQL_ERROR)
        {
            printf("SQLSetEnvAttr() ok\n");
            rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
            if ( rc != SQL_ERROR)
            {
                 printf("SQLAllocHandle() ok\n");
                 rc = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF,0);
                 if (rc != SQL_ERROR)
                 {
                       printf("SQLSetConnectAttr() ok\n");
                       SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
                       SQLFreeHandle(SQL_HANDLE_ENV, henv);
                 }
             }
         }
     }

     if (rc == SQL_ERROR)
     {
         ODBC_error (henv, hdbc, hstmt);
     }
}

```

--------------------------------

### Connect and manipulate PostgreSQL data in Visual Basic

Source: https://odbc.postgresql.org/howto-vb.html

Main subroutine demonstrating connection setup, recordset navigation, data updates, and cleanup using ADODB.

```vb
Sub Main()
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
 
  'Open the connection
  cn.Open "DSN=<MyDataSourceName>;" & _
          "UID=<MyUsername>;" & _
          "PWD=<MyPassword>;" & _
          "Database=<MyDatabaseName>"
 
  'Clear the table
  cn.Execute "DELETE FROM vbtest;"
  
  'For updateable recordsets we would typically open a Dynamic recordset.
  'Forward Only recordsets are much faster but can only scroll forward and 
  'are read only. Snapshot recordsets are read only, but scroll in both 
  'directions. 
  rs.Open "SELECT id, data, accessed FROM vbtest", cn, adOpenDynamic, adLockOptimistic
 
  'Loop though the recordset and print the results
  'We will also update the accessed column, but this time access it through 
  'the Fields collection. ISO-8601 formatted dates/times are the safest IMHO.
  While Not rs.EOF
    Debug.Print rs!id & ": " & rs!data
    rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")
    rs.Update
    rs.MoveNext
  Wend
 
  'Add a new record to the recordset
  rs.AddNew
  rs!id = 76
  rs!data = 'More random data'
  rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")
  rs.Update

  'Insert a new record into the table
  cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random data');"

  'Refresh the recordset to get that last record...
  rs.Requery

  'Get the record count
  rs.MoveLast
  rs.MoveFirst
  MsgBox rs.RecordCount & " Records are in the recordset!"

  'Cleanup
  If rs.State <> adStateClosed Then rs.Close
  Set rs = Nothing
  If cn.State <> adStateClosed Then cn.Close
  Set cn = Nothing
End Sub
```

--------------------------------

### Add PostgreSQL to ODBC Drivers Registry Key

Source: https://odbc.postgresql.org/faq.html

This entry adds 'PostgreSQL= Installed' to the 'ODBC Drivers' key within the ODBCINST.INI registry path, marking the driver as installed.

```reg
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC] Drivers
"PostgreSQL"="Installed"
```

--------------------------------

### Define Table with Primary Key

Source: https://odbc.postgresql.org/faq.html

Example table definition that the ODBC driver recognizes for primary key lookups.

```sql
    create table foo (
        id integer primary key,
        data varchar(20)
    );
```

--------------------------------

### Configure Windows Build System with XML

Source: https://odbc.postgresql.org/docs/release.html

The Windows build system settings are centralized in an XML file (configuration.xml). This file is automatically generated from a template and specifies the build environment configuration. Scripts are provided to edit this configuration and build binaries or installers.

```xml
(configuration.xml) xml file to specify the setting of the build environment. It is automatically generated in winbuild folder as a copy of winbuild/configuration_template.xml when you invoke editConfiguration script firstly.
```

```powershell
_.winbuild/configuration.ps1_
```

```powershell
_.winbuild/editConfiguration.ps1_
```

```batch
_.winbuild/editConfiguration.bat_
```

```powershell
_.buildx86.ps1_
```

```powershell
_.buildx64.ps1_
```

```powershell
_.installer/buildx86-installer.ps1_
```

```powershell
_.installer/buildx64-installer.ps1_
```

--------------------------------

### Keyset Query Structure

Source: https://odbc.postgresql.org/faq.html

Example of a 10-row keyset query used by database engines like Jet to access records.

```sql
    -- This is a 3 part key

    select ... from foo where
        (v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 1
        (v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 2
    ...
        (v1 = "?" AND v2 = "?" AND v3 ="?") OR -- line 9
        (v1 = "?" AND v2 = "?" AND v3 ="?")    -- line 10
```

--------------------------------

### Create PostgreSQL table for VB testing

Source: https://odbc.postgresql.org/howto-vb.html

SQL schema definition for the test table used in the Visual Basic examples.

```sql
 CREATE TABLE vbtest(  
 	  id int4,  
 	  data text,  
 	  accessed timestamp  
 ); 
```

--------------------------------

### Manual PostgreSQL ODBC Driver Registry Entry

Source: https://odbc.postgresql.org/faq.html

This REGEDIT4 script can be used to manually register the PostgreSQL ODBC driver if the installer fails. Ensure the paths to PSQLODBC.DLL are correct for your system.

```reg
REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL] 
"Setup"="C:\\WINDOWS\\SYSTEM\\PSQLODBC.DLL"
"Driver"="C:\\WINDOWS\\SYSTEM\\PSQLODBC.DLL"
"APILevel"="1"
"ConnectFunctions"="YYN"
"CommLog"="0"
"Optimizer"="0"
"DriverODBCVer"="02.00"
"FileUsage"="0"
"SQLLevel"="1"
"UsageCount"=dword:00000001
"Fetch"="100"
"UniqueIndex"="1"
"ReadOnly"="0"
"UseCursors"="0"
"UnknownSizes"="0"
"TextAsLongVarchar"="0"
"UnknownsAsLongVarchar"="0"
"MaxVarcharSize"="254"
"MaxLongVarcharSize"="4094"
"ConnSettings"=""
"UseDeclareFetch"="0"
"BoolsAsChar"="0"
"ExtraSysTablePrefixes"="dd;"
"Parse"="1"
"Ksqo"="1"
"CancelAsFreeStmt"="0"
```

--------------------------------

### Bootstrap the build environment

Source: https://odbc.postgresql.org/docs/unix-compilation.html

Initializes the build system using autoreconf.

```shell
autoreconf -i
```

--------------------------------

### Edit Build Configuration

Source: https://odbc.postgresql.org/docs/win32-compilation.html

Launches a GUI tool to configure the build environment, particularly for setting paths to libpq include/lib/bin files.

```powershell
.\editConfiguration(.ps1)
```

--------------------------------

### Build All Versions with nmake.exe

Source: https://odbc.postgresql.org/docs/win32-compilation.html

Builds both 32-bit and 64-bit versions of the driver in a single command using nmake and win64.mak.

```makefile
C:\psqlodbc\> nmake /f win64.mak world
```

--------------------------------

### Build with nmake.exe (32/64-bit)

Source: https://odbc.postgresql.org/docs/win32-compilation.html

Use nmake with win64.mak to build both 32-bit and 64-bit psqlODBC binaries. Ensure you are in a suitable command prompt (e.g., Microsoft Windows SDK Command Prompt).

```makefile
C:\psqlodbc\> nmake /f win64.mak <options>
```

--------------------------------

### Build All DLLs with Command Prompt

Source: https://odbc.postgresql.org/docs/win32-compilation.html

An alternative to the Powershell script for building the driver, invoked from the Command Prompt using a helper batch file.

```batch
C:\psqlodbc\> (Command Prompt) .\BuildAll.bat <options>
```

--------------------------------

### Connect and Query PostgreSQL with C#

Source: https://odbc.postgresql.org/howto-csharp.html

C# code demonstrating how to establish a connection to PostgreSQL using psqlODBC, execute a query, and display results. Includes basic error handling for ODBC exceptions.

```csharp
using System;
using System.Data;
using Microsoft.Data.Odbc;
 
class psqlODBC_Howto
{

  [STAThread]
  static void Main(string[] args)
  {

    // Setup a connection string
    string szConnect = "DSN=dsnname;" +
                       "UID=postgres;" +
                       "PWD=********";

    // Attempt to open a connection
    OdbcConnection cnDB = new OdbcConnection(szConnect);
     
    // The following code demonstrates how to catch & report an ODBC exception.
    // To keep things simple, this is the only exception handling in this example.
    // Note: The ODBC data provider requests ODBC3 from the driver. At the time of
    //       writing, the psqlODBC driver only supports ODBC2.5 - this will cause
    //       an additional error, but will *not* throw an exception.
    try 
    {
      cnDB.Open();
    } 
    catch (OdbcException ex) 
    {
      Console.WriteLine (ex.Message + "\n\n" + "StackTrace: \n\n" + ex.StackTrace);
      // Pause for the user to read the screen.
      Console.WriteLine("\nPress  to continue...");
      Console.Read();
      return;
    }
    
    // Create a dataset
    DataSet dsDB = new DataSet(); 
    OdbcDataAdapter adDB = new OdbcDataAdapter();
    OdbcCommandBuilder cbDB = new OdbcCommandBuilder(adDB);
    adDB.SelectCommand = new OdbcCommand(
                             "SELECT id, data, accessed FROM csharptest", 
                             cnDB);
    adDB.Fill(dsDB);

    // Display the record count
    Console.WriteLine("Table 'csharptest' contains {0} rows.\n", 
                      dsDB.Tables[0].Rows.Count);
    
    // List the columns (using a foreach loop)
    Console.WriteLine("Columns\n=======\n");
    
    foreach(DataColumn dcDB in dsDB.Tables[0].Columns)
      Console.WriteLine("{0} ({1})", dcDB.ColumnName, dcDB.DataType);
    Console.WriteLine("\n");

    // Iterate through the rows and display the data in the table (using a for loop).
    // Display the data column last for readability.
    Console.WriteLine("Data\n====\n");
    for(int i=0;i < dsDB.Tables[0].Rows.Count; i++)
    {
      Console.WriteLine("{0} {1} {2}", 
                        dsDB.Tables[0].Rows[i][0], 
                        dsDB.Tables[0].Rows[i][1], 
                        dsDB.Tables[0].Rows[i][2]);
    }

    // Close the connection
    cnDB.Close();

    // Pause for the user to read the screen.
    Console.WriteLine("\nPress  to continue...");
    Console.Read();
  }
}

```

--------------------------------

### Specify libpq connection parameters

Source: https://odbc.postgresql.org/docs/config.html

Use conninfo style strings to define SSL certificates and keys. Avoid overriding standard parameters like host or port to prevent conflicts.

```text
sslrootcert=c:\\myfolder\\myroot sslcert=C:\\myfolder\\mycert sslkey=C:\\myfolder\\mykey
```

--------------------------------

### Handle SSL Client Certificate Authentication with Windows Schannel

Source: https://odbc.postgresql.org/docs/release.html

For SSL client certificate authentication on Windows using Schannel, place the certificate file (postgresql.pfx in PFX or PKCS12 format) in the %APPDATA%\postgresql folder. This method replaces the need for separate .crt and .key files when using Cryptography API.

```bash
openssl pkcs12 -export -in postgresql.crt -inkey postgresql.key -out postgresql.pfx (with empty password).
```

--------------------------------

### Connect and Manage Large Objects with DAO

Source: https://odbc.postgresql.org/howto-vblo.html

Subroutine demonstrating how to connect to a PostgreSQL database using DAO and perform read/write operations on Large Objects.

```vb
Private Sub DAO_Connect()
	Dim chunk() As Byte
	Dim fd As Integer
	Dim flen As Long
	Dim ws As Workspace
	Dim cn As Database
	Dim rs As DAO.Recordset
	Dim strConnection As String

	' Initialize the DB Engine
	Set ws = DBEngine.Workspaces(0)
	Let strConnection = "ODBC;DSN=pgsql_test_blob;"
	Set cn = ws.OpenDatabase("", False, False, strConnection)

	' Open the table MYTABLE
	Set rs = cn.OpenRecordset("MYTABLE")

	'
	' Add a new record to the table
	'
    rs.AddNew


    rs!main = 100 '' a random integer value ''

    fd = FreeFile
    Open "mydocument" For Binary Access Read As fd
    flen = LOF(fd)
    If flen = 0 Then
        Close
        MsgBox "Error while opening the file"
        End
    End If

    ' Get the blob object into the chunk variable
    ReDim chunk(1 to flen)
    Get fd, , chunk()

    ' Store it in the database
	rs!object.AppendChunk chunk()

	' Update changes
    rs.Update

    ' Close the file
    Close fd

    ' Close the record set
	rs.Close

	'
	' Read the blob object from the first record of MYTABLE
	'
	Set rs = Nothing

	' Open the table
	Set rs = cn.OpenRecordset("MYTABLE")

	' Open a file for writing
	fd = FreeFile
	Open "mydocument" For Binary Access Write As fd
	flen = rs!object.FieldSize
	ReDim chunk(1 to flen)

	' Get it from the database
	chunk() = rs!object.GetChunk(0, flen)
	' ...and put it into the file
	Put fd, , chunk()

	' Close all...
	rs.Close
	Close fd
	cn.Close
	Close
End Sub
```

--------------------------------

### Create and Populate PostgreSQL Table

Source: https://odbc.postgresql.org/howto-csharp.html

SQL statements to create a table named 'vbtest' and insert sample data into it. Ensure the table name matches the one used in your C# code.

```sql
CREATE TABLE vbtest(
  id serial,
  data text,
  accessed timestamp
);
INSERT INTO csharptest(data, accessed) VALUES('Rows: 1', now());
INSERT INTO csharptest(data, accessed) VALUES('Rows: 2', now());
INSERT INTO csharptest(data, accessed) VALUES('Rows: 3', now());

```

--------------------------------

### ADO BLOB Operations

Source: https://odbc.postgresql.org/howto-vblo.html

Demonstrates storing and fetching binary data using ADODB.Connection and ADODB.Command objects.

```vb
Private Sub ADO_Store()
	Dim cn As New ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim cmd As ADODB.Command
	Dim chunk() As Byte
	Dim fd As Integer
	Dim flen As Long
	Dim main As ADODB.Parameter
	Dim object As ADODB.Parameter

	' Connect to the database using ODBC
	With cn
	    .ConnectionString = "dsn=pgsql_test_blob;"
	    .Open
	    .CursorLocation = adUseClient
	End With

	' Here is an example if you want to issue a direct command to the database
	'
	'Set cmd = New ADODB.Command
	'With cmd
	'    .CommandText = "delete from MYTABLE"
	'    .ActiveConnection = cn
	'    .Execute
	'End With
	'Set cmd = Nothing

	'
	' Here is an example of how insert directly into the database without using
	' a recordset and the AddNew method
	'
	Set cmd = New ADODB.Command
	cmd.ActiveConnection = cn
	cmd.CommandText = "insert into MYTABLE(main,object) values(?,?)"
	cmd.CommandType = adCmdText

	' The main parameter
	Set main = cmd.CreateParameter("main", adInteger, adParamInput)
	main.Value = 100 '' a random integer value ''
	cmd.Parameters.Append main

	' Open the file for reading
	fd = FreeFile
	Open "mydocument" For Binary Access Read As fd
	flen = LOF(fd)
	If flen = 0 Then
	    Close
	    MsgBox "Error while opening the file"
	    End
	End If

	' The object parameter
	'
	' The fourth parameter indicates the memory to allocate to store the object
	Set object = cmd.CreateParameter("object", _
                                         adLongVarBinary, _
                                         adParamInput, _
                                         flen + 100)
	ReDim chunk(1 to flen)
	Get fd, , chunk()

	' Insert the object into the parameter object
	object.AppendChunk chunk()
	cmd.Parameters.Append object

	' Now execute the command
	Set rs = cmd.Execute

	' ... and close all
	cn.Close
	Close
End Sub

Private Sub ADO_Fetch()
	'
	' Fetch the first record present in MYTABLE with a lo object stored

	Dim cn As New ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim fd As Integer
	Dim flen As Long
	Dim chunk() As Byte

	' Connect to the database using ODBC
	With cn
	    .ConnectionString = "dsn=pgsql_test_blob;"
	    .Open
	    .CursorLocation = adUseClient
	End With

	' Open a recordset of the table
	Set rs = New ADODB.Recordset
	rs.Open "MYTABLE", cn, adOpenKeyset, adLockOptimistic, adCmdTable

	' Get the len of the stored object
	flen = rs!object.ActualSize

	' Initialize the file where to store the blob
	fd = FreeFile
	Open "mydocument" For Binary Access Write As fd

	ReDim chunk(1 to flen)

	' Get it from the database
	chunk() = rs!object.GetChunk(flen)
	' ... and store in the file
	Put fd, , chunk()


	Close
End Sub
```

--------------------------------

### Specify connection options in VBA

Source: https://odbc.postgresql.org/docs/config.html

Construct a connection string for the PostgreSQL Unicode driver using keyword strings.

```vba
myConn = "ODBC;DRIVER={PostgreSQL Unicode};" & serverConn & _
"A0=0;A1=7.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" & _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;BI=-5;" & _
"C0=0;C2=dd_;C4=1;C5=1;C6=1;C7=1;C8=1;C9=0;CA=verify-full;D1=30;D4=40;" & _
"D5={sslrootcert=C:\\myfolder\\myroot sslcert=C:\\myfolder\\mycert sslkey=C:\\myfolder\\mykey}"
```

--------------------------------

### RDO BLOB Operations

Source: https://odbc.postgresql.org/howto-vblo.html

Demonstrates storing and fetching binary data using RDO.rdoConnection and RDO.rdoQuery objects.

```vb
Private Sub RDO_Store()
	Dim cn As New RDO.rdoConnection
	Dim rs As RDO.rdoResultset
	Dim cmd As RDO.rdoQuery
	Dim fd As Integer
	Dim flen As Long
	Dim chunk() As Byte

	' Connect to the database using ODBC
	With cn
	    .Connect = "dsn=pgsql_test_blob;"
	    .LoginTimeout = 3
	    .CursorDriver = rdUseOdbc
	    .EstablishConnection rdDriverNoPrompt, True
	End With

	' Create the INSERT statement to store the record in the database
	Set cmd = cn.CreateQuery("insert", _
                                 "insert into MYTABLE (main,object) values(?,?)")

	' Insert the first parameter
	cmd.rdoParameters(0).Value = 100 '' a random integer value ''

	' Open the file for reading
	fd = FreeFile
	Open "mydocument" For Binary Access Read As fd
	flen = LOF(fd)
	If flen = 0 Then
	    Close
	    MsgBox "errore in apertura file"
	    End
	End If

	ReDim chunk(1 To flen)
	' Get it ...
	Get fd, , chunk()
	' and store into the parameter object
	cmd.rdoParameters(1).Type = rdTypeLONGVARBINARY
	cmd.rdoParameters(1).AppendChunk chunk()

	' Finally execute the INSERT statement
	cmd.Execute

	' Close all
	Close
End Sub

Private Sub RDO_Fetch()
	'
	' Fetch the first record present in MYTABLE with a lo object stored

	Dim cn As New RDO.rdoConnection
	Dim rs As RDO.rdoResultset
	Dim fd As Integer
	Dim flen As Long
	Dim chunk() As Byte

	' Connect to the database using ODBC
	With cn
	    .Connect = "dsn=pgsql_test_blob;"
	    .LoginTimeout = 3
	    .CursorDriver = rdUseOdbc
	    .EstablishConnection rdDriverNoPrompt, True
	End With

	' Open the table
	Set rs = cn.OpenResultset("select * from MYTABLE", rdOpenKeyset)

	' Get the length of the file
	flen = rs!object.ColumnSize

	' Initialize the file where to store the object
	fd = FreeFile
	Open "mydocument" For Binary Access Write As fd

	ReDim chunk(1 To flen)

	' Get it from the database
	chunk() = rs!object.GetChunk(flen)
	Put fd, , chunk()
	Close
End Sub
```

--------------------------------

### Build All DLLs with Powershell

Source: https://odbc.postgresql.org/docs/win32-compilation.html

Use this script to build all psqlODBC driver DLLs using MSBuild. Ensure your Powershell ExecutionPolicy is set to RemoteSigned or Unrestricted.

```powershell
C:\psqlodbc\winbuild\> (Powershell) ./BuildAll.ps1 <options>
```

--------------------------------

### Run 32-bit ODBC Administrator on 64-bit Windows

Source: https://odbc.postgresql.org/faq.html

Use this command to open the 32-bit ODBC Data Source Administrator on a 64-bit Windows system. This is necessary for configuring 32-bit DSNs.

```batch
Start 
%SystemRoot%\syswow64\odbcad32.exe
```

--------------------------------

### ODBC PostgreSQL Connection Options

Source: https://odbc.postgresql.org/docs/config-opt.html

This section lists the available connection options for the ODBC PostgreSQL driver, including their definitions, keywords, and abbreviations.

```APIDOC
## ODBC PostgreSQL Connection Options

### Description
This document details the various connection options available for the ODBC PostgreSQL driver, explaining their purpose and keywords.

### Connection Options Table

| Definition | Keyword | Abbreviation |
|---|---|---|
| Data source description | Description | Nothing |
| Name of Server | Servername | Nothing |
| Postmaster listening port | Port | Nothing |
| User Name | Username | Nothing |
| Password | Password | Nothing |
| Debug flag | Debug | B2 |
| Fetch Max Count | Fetch | A7 |
| Socket buffer size | Socket | A8 |
| Database is read only | ReadOnly | A0 |
| Communication to backend logging | CommLog | B3 |
| PostgreSQL backend protocol | Protocol | A1 |
| Connect Settings (commands sent to backend on connection) | ConnSettings | A6 |
| Recognize unique indexes | UniqueIndex | Nothing |
| Unknown result set sizes | UnknownSizes | A9 |
| Use Declare/Fetch cursors | UseDeclareFetch | B6 |
| Text as LongVarchar | TextAsLongVarchar | B7 |
| Unknowns as LongVarchar | UnknownsAsLongVarchar | B8 |
| Bools as Char | BoolsAsChar | B9 |
| Max Varchar size | MaxVarcharSize | B0 |
| Max LongVarchar size | MaxLongVarcharSize | B1 |
| Fakes a unique index on OID | FakeOidIndex | A2 |
| Includes the OID in SQLColumns | ShowOidColumn | A3 |
| Row Versioning | RowVersioning | A4 |
| Show SystemTables | ShowSystemTables | A5 |
| Parse Statements | Parse | C0 |
| SysTable Prefixes | ExtraSysTablePrefixes | C2 |
| Updateable Cursors | UpdatableCursors | C4 |
| LF <-> CR/LF conversion | LFConversion | C5 |
| True is -1 | TrueIsMinus1 | C6 |
| Datatype to report int8 columns as | BI | Nothing |
| Bytea as LO (longVarBinary) | ByteaAsLongVarBinary | C7 |
| Use serverside prepare | UseServerSidePrepare | C8 |
| Lower case identifier | LowerCaseIdentifier | C9 |
| SSL mode | SSLmode | CA |
| Extra options | AB | Nothing |
| Abbreviate(simple setup of a recommendation value) | CX | Nothing |
| TCP KEEPALIVE setting: idle time | KeepaliveTime | D1 |
| TCP KEEPALIVE setting: interval | KeepaliveInterval | D2 |
| Libpq parameters (a conninfo style string e.g. pqopt={sslcert=~/mycert sslkey=~/mykey} enclosed with braces in the case of connection strings) | Pqopt | D5 |
| Numeric As: specify the map from numeric items without precision to SQL data types. 2(SQL_NUMERIC default), 8(SQL_DOUBLE), 12(SQL_VARCHAR) or -1(SQL_LONGVARCHAR) can be specified. | NumericAS | D6 |
| Show optional error messages. | OptionalErrors | D7 |
| Chunk size when executing batches for parameterized SQL statements with arrays of parameters. | BatchSize | D8 |
| Ignore SQL_ATTR_QUERY_TIMEOUT set using SQLSetStmtAttr(). | IgnoreTimeout | D9 |
```

--------------------------------

### Create Table for Large Objects

Source: https://odbc.postgresql.org/howto-vblo.html

SQL command to create a table with a Large Object (LO) type column.

```sql
 CREATE TABLE MYTABLE(  
  	  MAIN INTEGER,  
  	  OBJECT LO  
  ); 
```

--------------------------------

### Create Large Object Type and Table

Source: https://odbc.postgresql.org/faq.html

Defines a custom 'lo' type and a table using it to store large objects in PostgreSQL.

```sql
    create type lo (
        internallength=4,
        externallength=10,
        input=int4in,
        output=int4out,
        default='',
        passedbyvalue
    );

    create table employee (
        id integer,
        name varchar(30),
        picture lo
    );
```

--------------------------------

### Modify PATH for 32-bit Application Execution

Source: https://odbc.postgresql.org/faq.html

These commands modify the PATH environment variable to prioritize the SysWOW64 directory, ensuring 32-bit applications run correctly. Remember to reset the PATH after the application completes if necessary.

```batch
set PATH=%systemroot%\SysWOW64;%PATH%
```

--------------------------------

### Overload int4eq and create xid comparison operator

Source: https://odbc.postgresql.org/faq.html

Required for row versioning support in older PostgreSQL releases to resolve missing operator errors between xid and int4 types.

```sql
    create function int4eq(xid,int4)
        returns bool
        as ''
        language 'internal';

    create operator = (
        leftarg=xid,
        rightarg=int4,
        procedure=int4eq,
        commutator='=',
        negator='<>',
        restrict=eqsel,
        join=eqjoinsel
    );
```

--------------------------------

### Access UPDATE Query with NULL

Source: https://odbc.postgresql.org/faq.html

Demonstrates the actual SQL UPDATE query sent by MS Access when it translates empty strings to NULL. This query fails in PostgreSQL because NULL is not treated as an empty string.

```sql
     UPDATE a SET name = 'Smartypants' WHERE id = 1 AND name IS NULL     
```

--------------------------------

### Access UPDATE Query with Empty String

Source: https://odbc.postgresql.org/faq.html

Shows the expected SQL UPDATE query from MS Access when handling empty strings, which includes 'AND name = ". This query fails in PostgreSQL.

```sql
     UPDATE a SET name = 'Smartypants' WHERE id = 1 AND name = ''     
```

--------------------------------

### Configure ODBC Data Source Name in Access

Source: https://odbc.postgresql.org/faq.html

Specify the Data Source Name (DSN) directly in the 'ODBC Connect Str' property for SQL Pass-Thru queries in Microsoft Access to avoid repeated prompts. You can also include user ID and password.

```sql
ODBC;DSN=<your_datasource_name_here>
```

```sql
ODBC;DSN=my_dsn;UID=me;PWD=test
```

--------------------------------

### Create custom 'lo' type in PostgreSQL

Source: https://odbc.postgresql.org/howto-accesslo.html

Defines the 'lo' type required by the psqlODBC driver to handle LONGVARBINARY data. This must be executed in every database where large object support is needed.

```sql
create type lo (
    internallength=4,
    externallength=10,
    input=int4in,
    output=int4out,
    default='',
    passedbyvalue
);
```

--------------------------------

### PostgreSQL Row with Empty String

Source: https://odbc.postgresql.org/faq.html

Illustrates a PostgreSQL table row where the 'name' field contains an empty string, not a NULL value. This is a common source of conflict with MS Access.

```sql
        id  | name
    ----+-------------------------
      1 | <- contains the empty string. Not NULL.
```

--------------------------------

### Link PostgreSQL Table in VBA

Source: https://odbc.postgresql.org/howto-accessvba.html

Links a PostgreSQL table to an MS Access database. Note the workaround for connection string length limits by using specific parameter codes.

```vb
Private Sub Link_ODBCTbl(serverConn As String, rstrTblSrc As String, _
                         rstrTblDest As String, db As Database) 
LogEvent "Entering " & APP_NAME & ": Form_Login.Link_ODBCTbbl(" & _
         rstrTblSrc & ")", etDebug 
On Error GoTo Err_Handler

StartWork "Adding relation: " & rstrTblSrc

    Dim tdf As TableDef
    Dim connOptions As String
    Dim myConn As String
    Dim myLen As Integer
    Dim bNoErr As Boolean

    bNoErr = True

    Set tdf = db.CreateTableDef(rstrTblDest)
    ' don't need next line, as only called if doesn't exist locally
    'db.TableDefs.Delete rstrTblDest 
    ' this is 1st error, as doesn't exist locally yet; maybe wrong key


' The length of the connection string allowed is limited such that you can't 
' specify all of the PostgreSQL ODBC driver options as you normally would. 
' For those that want to do it normally, you are limited to somewhere between 
' 269 characters (works) and 274 (doesn't work). Using a dsn is not a workaround. 
' 
' ***WORKAROUND*** Tested Access 2000 on Win2k, PostgreSQL 7.1.3 on Red Hat 7.2 
' 
' The connection string begins as usual, for example: 
'
'   "ODBC;DRIVER={PostgreSQL};DATABASE=database_name_to_connect_to;" & _
'   "SERVER=ip_address_to_connect_to;PORT=5432;Uid=username_to_connect_as;" & _
'   "Pwd=password_of_user;" & _ 
' 
' For all other parameters, you must code them in the same way Access stores them 
' in the hidden MSysObjects table.  Here is a cross-reference table: 
' 
'   PG_ODBC_PARAMETER           ACCESS_PARAMETER
'   *********************************************
'   READONLY                    A0
'   PROTOCOL                    A1
'   FAKEOIDINDEX                A2  'A2 must be 0 unless A3=1
'   SHOWOIDCOLUMN               A3
'   ROWVERSIONING               A4
'   SHOWSYSTEMTABLES            A5
'   CONNSETTINGS                A6
'   FETCH                       A7
'   SOCKET                      A8
'   UNKNOWNSIZES                A9  ' range [0-2]
'   MAXVARCHARSIZE              B0
'   MAXLONGVARCHARSIZE          B1
'   DEBUG                       B2
'   COMMLOG                     B3
'   OPTIMIZER                   B4  ' note that 1 = _cancel_ generic optimizer...
'   KSQO                        B5
'   USEDECLAREFETCH             B6
'   TEXTASLONGVARCHAR           B7
'   UNKNOWNSASLONGVARCHAR       B8
'   BOOLSASCHAR                 B9
'   PARSE                       C0
'   CANCELASFREESTMT            C1
'   EXTRASYSTABLEPREFIXES       C2
'
' So the parameter part of the connection string might look like: '
'   "A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" & _
'   "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_"
'
' Concatenating those four strings together will give you a working connection  
' string (but you may want to change the options specified). 
' 
' NOTES:
'   `Disallow Premature` in driver dialog is not stored by Access.
'   string must begin with `ODBC;` or you will get error
'   `3170 Could not find installable ISAM`.

'Debug.Print svr.Conn

myConn = "ODBC;DRIVER={PostgreSQL};" & serverConn & _
            "A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" &
            "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;" &
            "C0=0;C1=0;C2=dd_"

    tdf.Connect = myConn
    tdf.SourceTableName = rstrTblSrc
    db.TableDefs.Append tdf
    db.TableDefs.Refresh

    ' If we made it this far without errors, table was linked...
    If bNoErr Then
        LogEvent "Form_Login.Link_ODBCTbl: Linked new relation: " & _
                 rstrTblSrc, etDebug
    End If

    'Debug.Print "Linked new relation: " & rstrTblSrc ' Link new relation

    Set tdf = Nothing

Exit Sub

Err_Handler:
    bNoErr = False
    Debug.Print Err.Number & " : " & Err.Description
    If Err.Number <> 0 Then LogError Err.Number, Err.Description, APP_NAME & _
                                     ": Form_Login.Link_ODBCTbl"
    Resume Next

End Sub
```
Tokens:
Raw