http://www.postgresql.org/docs/6.4/static/odbc18456.htm
standalone installation


ODBC Version
Generated into config.h.
My UNIX builds get 0x351, which is apparently a v. 3.

No reason to support libpq at all.  This is a postgresql-specific C library.
Simple/Normal queries just send text query.  Has sub-protocols, e.g. COPY.
Extended queries are prepared and executed.
info.c probably has to be completely replace.  MetaData manag.

Function Call stuff is legacy.  Skip it.

Query != Select.  Query just means SQL.

SOURCE
    Releases:  http://www.postgresql.org/ftp/odbc/versions/src/
               Latest here is psqlodbc-08.03.0400.tar.gz, timestamp 2008-11-07
    CVS:
    cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/psqlodbc co psqlodbc
    ACTIVELY DEVELOPED

TEST DATA
    t2c.i1, t2c.i2

Escape sequences:
http://www.ibprovider.com/eng/documentation/odbc_escape_sequences_eng.html

Can set following in .odbc.ini
Debug = 1         Causes mylog() logging to /tmp/mylog_<USER><PID>.log
                  On windows writes to %HOMEDIR%%HOMEPATH%/mylog_<PID>.log
*Debug = 2         mylog() + inolog() to same.
CommLog = 1       Causes qlog() to log to /tmp/psqlodbc_<USER><PID>.log

The OpenSUSE-supplied rpm is built for unixODBC.  OpenSUSE seems to have no
support for iODBC.  Can build libiodbc*.so manualy (which partially accounts
for the huge problems with Firebird's ODBC driver).

According to the FAQ, there are separate drivers for unicode and ansi, but I
see only one (with the unixODBC variant anyways).

Supports md5-encrypted logins but not crypt logins. (Independent of rest of
session).
Supports SSL.

Seems type support is not very general.  See FAQ.

Large objects are mapped to LONGVARBINARY.
Impl. uses multiple SQLPutData and SQLGetData calls.  See details.

Driver assumes primary keys are listed in system tables with unique id named
*_pkey.


PRIMARY SOURCE FILES:  (44,500 lines total)  All but one compile to .o w/ gcc.
    bind.c
    columninfo.c
    connection.c
    convert.c
    descriptor.c
    dlg_specific.c
    drvconn.c
    environ.c
    execute.c
    info30.c
    info.c
    loadlib.c
    lobj.c
    md5.c   md5.o file not generated?
    misc.c
    multibyte.c
    mylog.c
    odbcapi30.c
    odbcapi30w.c  Unicode support
    odbcapi.c
    odbcapiw.c  Unicode support
    options.c
    parse.c
    pgapi30.c
    pgtypes.c
    psqlodbc.c
    qresult.c
    results.c
    socket.c
    statement.c
    tuple.c
    win_md5.c
    win_unicode.c  Unicode support

BUILD PROCEDURE
    GOAL:  To build psqlodbcw.so.
    Edit Makefile.am to remove references to items below (copy at $HOME/odbc).
    rm -rf docs installer +
        Makefile.in
        dlg_wingui.c inouealc.c win_setup.h 
        setup.c psqlodbc.rc win64.mak 
        win32.mak psqlodbc.reg psqlodbc.dsp psqlodbc.vcproj 
        msdtc_enlist.cpp pgxalib.cpp xalibname.c 
        pgxalib.def odbcapi25w.c sspisvcs.c sspisvcs.h 
    export PG_CONFIG=/usr/local/pgsql/bin/pg_config
      (wherever pg_config Program is).
    Run aclocal, automake, ./configure.
    Run "make".  Completes in about 30 seconds.
    ##################
    CVS PROCEDURE
    export PG_CONFIG=/usr/local/pgsql/bin/pg_config
      (wherever pg_config Program is).
    Check out module "psqlodbc".
    Copy the following files in from a source distro (they don't get changed
     during builds, so can copy from a "used" build area).
        configure
        config.h.in
        config/ltmain.sh
        config/depcomp
        config/install-sh
        config/missing

    Run aclocal, automake -a, ./configure.
    Run "make".  Completes in about 30 seconds.

VERSION DETECTION
Hsqldb 1.7.3.3 to 1.8.0.10 client send bytes 0 0 0 34
psqlodbc 08.03.0400 sends 0 0 1 40
 w/Hsql 1.7.3.3:  0 0 0 34 0 1 0 7
 w/Hsql 1.8.0.10:  0 0 0 34 0 1 0 7
 w/PG*63:  0 0 1 40 0 1 0 0
 w/PG*64:  0 0 1 40 0 2 0 0

LOGGING
mylog("string\n", printf, params);  // Logs to MyLog.
qlog("string\n", pritnf, params);   // Logs to comm log.
! SEARCH log for "has to eat".  This indicates bad server writes.
inolog(...)        Where does it go?
I/O
SOCK_get_string() just gets a null-terminated String (conditionally truncating).
SOCK_get_id() reads just a byte
PG_PROTOCOL_74 IS us!
SOCK_get_response_length gets 4 byte int length and subtracts 4
 (+ saves to conn->reslen).
IS_SSPI #ifdef is not us.
SOCK_get_n_char()  Plain read of exactly that many bytes
SOCK_put_n_char()  Just writes precisely the given number of chars
new_format for messages is us.
SOCK_get_int() gets the specified number of bytes

#####################
connection:CC_send_query_append.  Only for Q commands!
If appendq param set, depending on a flag bit, will either send query;appendq
 or xmit query then xmit appendq.
The discard_next_*'s are because CC_send_query_append expects a reply for
 each query sent, and this takes care of possible extra replies for the
 BEGIN and SAVEPOINT components sent along with primary query (I wonder if
 things will work with explicit compound commands).
Constants:
    semi_colon: ;  [code often does not use this constant)
    bgncmd: BEGIN
    svpcmd: SAVEPOINT
    rlscmd: RELEASE
    rbkcmd: ROLLBACK
    per_query_svp: _per_query_svp_
IF need to begin a transaction THEN
    issue_begin = true (depend on state and flag param)
    writes "BEGIN;" immediately after 'Q'.   ("bgncmd")
    will swallow the first "C BEGIN" returned by server
    (there is also a function to send plain "BEGIN" statement).
IF roll-backable query (derived "query_rollback")
    SAVEPOINT _per_query_sp_;<Orig_Qry>;RELEASE _per_query_svp
    Then, if error:  ROLLBACK TO _per_query_svp;RELEASE _per_query_svp
    will swallow the first "C SAVEPOINT" returned by server
    (there is also a function to send plain "ROLLBACK" statement).
ERROR HANDLING
    IF query_rollback, then ROLLBACK + RELEASE for _per_query_svp (see above)
    ELSE IF in rollbackable state, then ROLLBACK
#####################

commands  (greatly duplicative of beresp).
    C:  CommandComplete
        Special processing for values o COMMIT, END, ABORT, ROLLBACK
        *First* occurrence of BEGIN swallowed IF this method started a trans.
        [TENTATIVE (untested):
        *First* occurrence of SAVEPOINT...
        ]
    Z:  Done
    N:  Notice  Should be send to connections upon shutdown immediate
    I:  Empty query (from us???)
    E:  Error message
    T:  Tuple (runs QR_fetch_tuples())

qresult:QR_next_tuple commands (greatly duplicative of befresp).
    B,D:  Binary and text row data
    C:  End-of-rows
    E:  Error message
    N:  Notice
    Z:  Done

CONNECTION SEQUENCE
original_CC_connect called with password_req == AUTH_REQ_OK   (i.e. 0)

Authenticates
    connection/auth:beresp values
    EVERY bresp PRECEDES A PACKET BEGINNING WITH INCLUSIVE 4-byte INT LENGTH.
        E Error
            Same as N below, plus
                Keeps reading more "truncated" strings?
                Causes client disconnect if message starts with "FATAL".
        R Requires password?
        S Send params to client
        K Sends server-> client a secret server pid and key
                         (used only in Cancel Request Packet)
        Z Ready for Query  Includes transaction status indictor of
            I:  Not in trans. (Idle)
            T:  in Trans.
            E:  in failed trans. block
        N Notice.  Logged to both MyLog + Comm log. Null termd.
                   Each null-termd String should have type prefix char, and
                   only one of each is allowed!:
                   See for all:
                     http://www.postgresql.org/docs/8.3/interactive/protocol-error-fields.html
                    S: specifies Severity like "SERROR", "SFATAL"
                       (FATAL has side-effect of causing client to disconnect)
                    C: SQL state <= 8 chars. like "C42601"
                    M,D: Message (don't know distinction)
                         New lines inside strings mean paragraph breaks.
                    P,F,L,R: ? (indicate location of error?)
                    If send S + M, will display like "SEVERITY: message".
                   Can send as many null termd strings as want. Termd with \0\0.
    areq seems to be Authentication Method.  We want one of
        AUTH_REQ_OK  No authentication?
        AUTH_REQ_PASSWORD
R <-
size <-
areq <-
p ->
int pwd len (incl. nul) ->
null-termd pwd ->
[ To real PG svr
    svr sends R, AUTH_REQ_MD5, salt
    cli sends encoded pwd,
    svr sends K + pid + key
    svr sends Z
]

S <-
size <-
client_encoding (null-termd) <-
SQL_ASCII (null-termd) <-
S <-
size <-
DateStyle (null-termd) <-
ISO, MDY (null-termd) <-
S <-
size <-
integer_datetimes (null-termd) <-
on (null-termd) <-
S <-
size <-
is_superuser (null-termd) <-
on (null-termd) <-
S <-
size <-
server_encoding (null-termd) <-
SQL_ASCII (null-termd) <-
S <-
size <-
server_version (null-termd) <-
8.3.1 (null-termd) <-
S <-
size <-
session_authorization (null-termd) <-
blaine (null-termd) <-
S <-
size <-
standard_conforming_trings (null-termd) <-
off (null-termd) <-
S <-
size <-
TimeZone (null-termd) <-
US/Eastern (null-termd) <-
  
Executes SQL statements:
  # " "  (single space)
  # select version()
  #     Server sends 182 bytes: 'P', 'T' or 'C' (see below), 'Z'.
  #     ('P' seems to be entirely ignored by driver)         adtid adtsize a*mod
  # set DateStyle to 'ISO'                                      25      -1 -1
  # set extra_float_digits to 2
    select oid, typbasetype from pg_type where typname = 'lo'
                                                          oid:  26       4 -1
                                                  typbasetype:  26       4 -1
    select pg_client_encoding()
                                           pg_client_encoding:  19      64 -1
szConnStrOut = 'DSN=...' (whole big ODBC conn string)
 (possibly consequence of pg_client_encoding failure or execution?)

EMPTY QUERY SEQUENCE  (Not empty result, but empty query)
    client Send 'Q'
    client Send query as characters
    client Send \0 character
    server Sends id 'I' + \0
    server Sends id 'Z'

For my simple queries, the only thing that happens in the CC_send_query_append
'T' block is QR_fetch_tuples().

QR_fetch_tuples(x, self, y)  [ to continue, connection calls qft(x, NULL, NUL)
                               nexted Ts in QR_next_tuple call (x, y, NULL)  ]
    CI_read_fields();
    return QR_next_tuple(self, NULL);  with QR_is_fetching_tuples TRUE!

CI_read_fields(self-fields, x) reads field DESCRIPTIONs
                             [ persists to self->fields ] QR->fields is a CI object.
    Read short of num fields in result
    FOREACH FIELD
        read Null-termd field name (lower-case?)
        read int table id
        read col table id
        read adtid int  (25  Type identifier)
        read adtsize short (-1 == Null-termd-or-variable?)
        reads atttypmod int -1 (unless PG date): bpchar/varchar length
        swallows a short


tuple = row = row-fields  (fields-for-a-single-row)
QR_next_tuple   MONSTER METHOD!!!!  Only interesting things:
    (at least first time through, QR_is_fetching_tuples is true).
    Through big for/switch for each server cmd
        <-- cmd
        <-- int size
        Each row  (or 0 times!):
            <- 'D' or 'B' for each row (may be 0)  (D and B are ASCII and Bin.)
            QR_get_tupledata();
        <- 'C'  End-of-tuples (aka rows)
        <-  Statement type (null termd)

QR_get_tupledata()
    QR_Read_a_tuple_from_db()

QR_Read_a_tuple_from_db()   Increments self->num_cached_rows
    <- null-field-bitmap as binary bytes
    FOREACH non-null FIELD
        Read int len
        FOR ascii len -= 4
        <- date (len bytes NOT TERMD)

Bitmap
    Bitmap has bits OFF! for null vals, then those values are skipped in data.
     (explanation logic is backwards in code comments).
    Size = fields / 8, rounded up
    Left-most (first) bit corresponds to first column.
    Filled out to right (value doesn't matter).

T  empty set (no row) reply

AN UPDATE
** IF NO AUTOCOMMIT, THEN sends another 6 bits of something
Q -->
update t set i = 3 (null-termd) -->
C BEGIN <--      (if not autocommit)

commit;  is just
commit ->
C COMMIT <-
Z <-
conn.commit() does the same exact thing, except it transmits "COMMIT" Q.

autocommit processing seems to be handled entirely in the client, apparently
ignoring the JDBC feature.

Exiting
Apparently just close the socket connection.  Nothing goes over the pipe.

'K' command, be_key, be_pid are only for authorizing a cancellation command
() from an independent connection.


DATA
    Last datum send in T field descriptions is 0 or 1 for text or binary format.
     Always use text for anything that can be displayed in text, such as
     decimals or integers.
    With our version, no distinction between data value lengths.
    All are prefixed by int(4) length.
    	 * Some Notes about Postgres Data Types:
		 *
		 * VARCHAR - the length is stored in the pg_attribute.atttypmod field
		 * BPCHAR  - the length is also stored as varchar is
		 *
		 * NUMERIC - the decimal_digits is stored in atttypmod as follows:
		 *
		 *	column_size =((atttypmod - VARHDRSZ) >> 16) & 0xffff
		 *	decimal_digits	 = (atttypmod - VARHDRSZ) & 0xffff
Rev. env. with aadb.
                 tblid  colid dtype typesiz siz_constr
    col/expr     relid  attid adtid adtsize attypmod
    ------------ ------ ----- ----- ------- --------
    bothtypes.i  346192     1    23       4       -1
    bothtypes.vc 346192     2  1043      -1       -1
    Joint str ex      0     0    25      -1       -1
    Str expr          0     0   705      -2       -1
    int expr          0     0    23       4       -1
Need to make use of colTypes[j].typeCode, colTypes[j].getNameString().
 colTypes[j].get*() also provide groupings such as integral types.
resultMetaData.getGeneratedColumnIndexes() contains real DB indexes.
 Need these for fancier queries.
Can reply with errors like so.
Sen 'E' instead of T.
Send Z with type E;


VERY DIFFICULT PROBLEM at the first PROTOCOL_74 test in
results.c:PGAPI_ColAttributes() PROTOCOL.  Need to eliminate this because
it causes the data dictionary ("select n.nspname...") query in
info.c:PGAPI_Columns().

FAQ seems to be completely wrong about distro formats.
    MM:  Something for re-packagers.
    DLL:  Just dll's.
    MSI:  Windows installer.  Larger.  (Not needed for upgrades).
          The *.msi distribution contains just a single 5M *.msi executable
           installer + (inappropriate) readme file and an upgrade script.
          Installs to \Program Files\psqlODBC by default.
          (*.msi files execute "msiexec" as the interpreter).

Configuring in Windows
    Control Panel / Administrative Tools / Data Sources (ODBC)

BUILDING in Windows
    Install the current MS Visual C++ Express edition from
     http://msdn.microsoft.com.
    Install the current NON-LIGHT! OpenSSL for platform from
     http://slproweb.com/products/Win32OpenSSL.html (misnomer, 64 binaries too).
     Install to default location, select to install shareds to bin, not win.
     ?Ignore warning about requiring Visual C++ redistributables.? TO VERIFY
    The installation adds global variable VS90COMNTOOLS.
    Run %VS90COMNTOOLS%\vsvars32.bat
BUILDING INSTALLER
    Editing ODBC.ini file has no effect.
    Regedit doesn't work.
    Just run both
        nmake /f win32.mak CFG=Release ALL
        nmake /f win32.mak ANSI_VERSION=yes CFG=Release ALL
    BEWARE!!!:  If nmake fails, do not expect the real failure details to
     appear near where it aborts.  You must examine all of the nmake output.
    (until clean up the Wix config!)
    Install Wix.  Don't need the extra products.  Just get Wix3.*-x86-setup.zip
     and execute it.  I did default install, other than changing install dir.
    Just put the WIS_HOME/bin into search path.
    Need to update just psqlodbc.wxs to not refer to old msvcr DLL resources.
    Cd to "installer" directory and run: .\Make VERSION
     (where version must be of dotted numerical format like 08.03.0401).
ws2_32.dll interceptor at
    http://codemagnet.blogspot.com/2007/10/winsock2-replacement.html , but it
    doesn't include the functions I need.
Windows driver upgrades
    The "Date" column in the ODBC Data Source Administrator shows the driver
     install date.
    Can uninstall by rerunning the same *.msi used to install; or run
      Control Panel / Programs and Features (Vista)
      Control Panel / Add/Remove Programs (other)
    Fortunately, DSNs for removed drivers are retained!
    I see no benefit to using the upgrade batch script, which just passes
     params to "msiexec".

BRANDING
    2 *.bmp files with Postgresql images for the Windows installer.
    psqlodbc.rc file (Developer Studio RC script) contains driver names
     (probably other files too).

LOCATIONS WITH POSSIBLE Postgresql-specific SELECT statements
!! List EXPLAIN, DESCRIBE, and SHOW commands for Fred too!
The info30.c file seems to be good (ODBC v.3 metadata)
connect.c
    select oid,... upon startup.
    current_schema()
convert.c
    Uses ctid pseudo-column
    convert_escape() generates queries.  Func. should probably be eliminated.
multibyte.c
    CC_lookup_cs_new()  Not run from Linux driver.  Test whether called from
     CC_lookup_characterset() from Windows ANSI client.
parse.c
    CheckHasOids()
    getCOLIfromTable()
results.c
    tupleExists()  uses ctid pseudo-column

EXTENDED QUERY PROTOCOL
    Prepared Statement:  Named or unnamed.
    Portal:  ready-to-execute or already-partially-executed statement,
             with missing parameter values filled in.
             Just like a cursor, but works for non-selects too.
             Named or unnamed.
    Prepared Statements and Portals are session-specific, but one session may
     have multiple of them.
    BEWARE:  unnamed prepared statements and portals are destroyed by simple
             queries!
    Steps
        + parse:  prepares text query  [query planning occurs here EXCEPT
                                        for "" prep. state. w/ params]
            P ->
            size (int) ->
            new prepared-statement-name ("" for unnamed) ->
            text query ->
            # params setting (short) ->  FOREACH
                [data type OID (int4) or 0 == unset ->; len < param-list]
            ParseComplete <-
                '1' <-
                4 (int) <-
        + bind:  prepared statement + param vals -> portal
                                       [query planning here for "" pre. state.
                                        w/ params]
            B ->
            size (int) ->
            new portal-name ("" for unnamed) ->
            prepared-statement name ("" for unamed)  ->
            # param format codes (short; 0 for all-text; 1 means applies to all)
                0|1 (short) text or binary format indicator ->
            # param values (short)
                param len (int; does not include self; -1== null) ->
                param val (var. size; not present if null)
            # output col. format codes (short; 0... same as for params)
                0|1 (short) text or binary format indicator ->
            BindComplete -<
                '2' <-
                4 (int) <-
        + execute:  for row-fetching queries, may fetch just next X rows
            E ->
            size (int) ->
            portal name ("" for unnamed) ->
            max row-count (int) ->  (0 == all rows; ignored for non-fetches)
            D, B records <-
            PortalSuspended <-  (if rows remain)
            {
                r.setPreparedExecuteProperties(paramValues,
                    maxRows, fetchSize);
                newRes = session.execute(r);
            }
            IF rows remain
                's' <-
                4 (int) <-
            ELSE for return type of StatementType.RETURN_COUNT no rows
                C retval
        + [close named prepared statement | portal] (can't re-use until closed)
            Not an error for non-existent ps/portal
            'C' ->
            size (int) ->
            S|P ->  indicate prepared statement or portal target
            null-termd-string -> ps/portal name
            CloseComplete <-
                '3' ->
                4 (int) ->
        + Sync -> (Server commits or rolls back UNLESS inside BEGIN block)
            'S' ->
            4 (int) ->
            Z -< (see 'Z', ReadyForQuery)
    ERROR HANDLING
        Server issues an ErrorResponse then gobbles until it consume a Sync.
    OPTIONS
        Description:  Fetches for specified ps or portal:
            'D' ->
            size (int) ->
            S|P ->  indicate prepared statement or portal target
            null-termd-string -> ps/portal name ->
            IF S
                ParameterDescription
                    't' <-
                    size (int) <-
                    param count (short) <-
                    FOREACH param
                        data type oid (int)
                RowDescription (T, with 0 format codes) or NoData (n) <-
            IF P
                RowDescription (T) or NoData (n)  <-
            ERROR-HANDLING:  Conflicts with rule that all Extended errors result
             in a swallowing up to a sync:  return E if handle not present.
        Flush.  Forces server to send what it has.  To minimize packets?
         I don't understand.  How could different packets be merged, other
         than execute output packets?
            'H' ->
            4 (int) ->
* are these specified in formats documented at
 http://www.postgresql.org/docs/8.3/interactive/protocol-message-types.html?


COPY FROM/TO STDIN/OUT sql commands.
FROM STDIN
    Flush and Syncs are ignored for the duration.  Others cause server error.
    COPY FROM STDIN sql executed on server.
    CopyInResponse <-
    CopyData* ->
    CopyDone ->
  OR
    CopyFail ->
    CopyComplete <-
ERROR HANDLING
    E <-
    Copy* -> (swallowed)
    * -> [If was in ext. qry mode]
    Sync -> [If was in ext. qry mode]
    Z <-
Not covering  TO STDOUT

ASYNC packets
    N and S messages may be received anytime
    A (async notification reponse) can too, but is not supported by psqlodbc

TYPES TO CONVERT


FIX metadata FUCKUPS
resultOut psResult
in ackResult
    ackResult.getStatementType()
    ackResult.metaData
    ackRestult.parameterMetaData
resultIn.getUpdateCount()

There is a bug with either Sun's jdbc:odbc or psqlodbc that causes any
fetchsize setting to say the value is prohibited.



Optimized UNIX build
./configure --with-gnu-ld
W/Out SSL:  --disable-openssl