From 672f9a971f6b2353f475e656d9e6789168861539 Mon Sep 17 00:00:00 2001 From: Dave Page Date: Sat, 27 Aug 2005 19:44:47 +0000 Subject: [PATCH] Move docs into the source tree. --- docs/config.html | 241 +++++++++++++++++++++++++ docs/howto-accessvba.html | 178 ++++++++++++++++++ docs/howto-bo.html | 45 +++++ docs/howto-ch.html | 139 ++++++++++++++ docs/howto-csharp.html | 156 ++++++++++++++++ docs/howto-debian.html | 156 ++++++++++++++++ docs/howto-redhat.html | 251 ++++++++++++++++++++++++++ docs/howto-vb.html | 128 +++++++++++++ docs/howto-vblo.html | 349 ++++++++++++++++++++++++++++++++++++ docs/index.html | 70 ++++++++ docs/win32-compilation.html | 76 ++++++++ 11 files changed, 1789 insertions(+) create mode 100644 docs/config.html create mode 100644 docs/howto-accessvba.html create mode 100644 docs/howto-bo.html create mode 100644 docs/howto-ch.html create mode 100644 docs/howto-csharp.html create mode 100644 docs/howto-debian.html create mode 100644 docs/howto-redhat.html create mode 100644 docs/howto-vb.html create mode 100644 docs/howto-vblo.html create mode 100644 docs/index.html create mode 100644 docs/win32-compilation.html diff --git a/docs/config.html b/docs/config.html new file mode 100644 index 0000000..84569bb --- /dev/null +++ b/docs/config.html @@ -0,0 +1,241 @@ + + + + + psqlODBC Configuration Options + + + + +

psqlODBC Configuration Options

+ +

Advanced Options (Driver) Dialog Box

+ + + +

Advanced Options (DataSource or Connection) Dialog Box

+ + + + + \ No newline at end of file diff --git a/docs/howto-accessvba.html b/docs/howto-accessvba.html new file mode 100644 index 0000000..15c7aab --- /dev/null +++ b/docs/howto-accessvba.html @@ -0,0 +1,178 @@ + + + + + psqlODBC HOWTO - Access VBA + + + + +

psqlODBC HOWTO - Access VBA

+ +

+ + +Author: Mark A. Taff (mark@libertycreek.net)
+Release Date: 12 February 2002
+Description: Example based Mini-Howto on using Microsoft Access VBA with PostgreSQL + +

+Here is some VBA code I have written as it can be hard to find +answers related to the PostgreSQL ODBC driver. Specifically, how to programmatically +link and unlink PostgreSQL relations in a MS Access database. This is tested with +Access 2000 on win2k with PostgreSQL 7.1.3 on Red Hat 7.2.

+ +The tricky thing here is the special way to specify the Connection parameters so +Access will accept them in the context of an Access table definition object (as +opposed to an ADODB connection object). The code is heavily commented to explain +it, and consists of two subroutines, one to link a new relation and another to +unlink it.

+ +I am making it available for public knowledge WITHOUT ANY WARRANTY, but I sure +hope it makes someone else's life a bit easier. + +

Code

+ +
+
+
+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
+
+Private Sub UnLink_ODBCTbl(rstrTblName As String, db As Database) 
+LogEvent "Entering " & APP_NAME & ": Form_Login.UnLink_ODBCTbbl", etDebug 
+On Error GoTo Err_Handler
+
+    StartWork "Removing revoked relation: " & rstrTblName
+
+    ' Delete the revoked relation...that'll teach 'em not to get on my bad side
+    ' I only call this sub after verifying the relation exists locally, so I 
+    ' don't check if it exists here prior to trying to delete it, however if you 
+    ' aren't careful...
+    db.TableDefs.Delete rstrTblName
+    db.TableDefs.Refresh
+
+    Debug.Print "Removed revoked relation: " & rstrTblName
+
+Exit Sub
+
+Err_Handler:
+    Debug.Print Err.Number & " : " & Err.Description
+    If Err.Number <> 0 Then LogError Err.Number, Err.Description, APP_NAME & _
+                                     ": Form_Login.UnLink_ODBCTbl"
+    Resume Next
+
+End Sub
+
+
+
+

+ + + \ No newline at end of file diff --git a/docs/howto-bo.html b/docs/howto-bo.html new file mode 100644 index 0000000..a8d666d --- /dev/null +++ b/docs/howto-bo.html @@ -0,0 +1,45 @@ + + + + + psqlODBC HOWTO - Business Objects + + + + +

psqlODBC HOWTO - Business Objects

+ +

+ + +Author: Peter S. Hamlen (phamlen@teachforamerica.org)
+Release Date: 26 May 2003
+Description: PostgreSQL and Business Objects + +

+Will Postgresql ODBC work with Business Objects? +

+Yes, with three caveats: + +
+

    +
  1. Technically, Business Objects does not support PostgreSQL. They do +support generic ODBC access to databases, however.
     
    2. + +
  2. You must change the ODBC parameter file on any machine that uses +Designer. The ODBC file is usually C:\Program Files\Business +Objects\Data Access X.X\ODBC\ODBC10EN.PRM. You must change the OWNER +parameter to N and the QUALIFIER parameter to N (ie, OWNER=N, +QUALIFIER=N).

    + NOTE: It is only necessary to change this file for people who use +Designer. BusinessObjects, WebIntelligence, and all other BO programs +that use existing Universes are unaffected.
     
    3. + +
  3. As with all other generic ODBC connections, you cannot do outer +joins in Designer.
     
    4. + + +

    + + + \ No newline at end of file diff --git a/docs/howto-ch.html b/docs/howto-ch.html new file mode 100644 index 0000000..de4c2f2 --- /dev/null +++ b/docs/howto-ch.html @@ -0,0 +1,139 @@ + + + + + psqlODBC HOWTO - Ch + + + + +

    psqlODBC HOWTO - Ch

    + +

    + + +Author: Wayne Cheng (wayne@softintegration.com)
    +Release Date: 23 September 2003
    +Description: Example based Mini-Howto on Accessing PostgreSQL from Ch +     +

    +Ch is a C/C++ interpreter from http://www.softintegration.com.
    +It is designed for cross-platform scripting, shell programming, +2D/3D plotting, numerical computing, and embedded scripting. + +

    +Requirements to get the code to work: + +
    +

    + +How to run ODBC code in Ch
    +You should be able to use the simple.c scripts, start Ch shell first. + +
    +
    +
    +% ch
+                                   Ch 
+                  Standard edition, version 4.0.0.11291 
+              (C) Copyright 2001-2003 SoftIntegration, Inc.
+                     http://www.softintegration.com
+/home/wcheng> cd $CHHOME/package/iodbc/demos
+/usr/local/ch/package/iodbc/demos> ls
+odbctest.c  simple.c
+/usr/local/ch/package/iodbc/demos> ./simple.c
+SQLAllocHandle() OK
+SQLSetEnvAttr() ok
+SQLAllocHandle() ok
+SQLSetConnectAttr() ok
+/usr/local/ch/package/iodbc/demos> cat simple.c
+
+/**************************** simple.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);
+     }
+}
+
+
    + + + \ No newline at end of file diff --git a/docs/howto-csharp.html b/docs/howto-csharp.html new file mode 100644 index 0000000..040341e --- /dev/null +++ b/docs/howto-csharp.html @@ -0,0 +1,156 @@ + + + + + psqlODBC HOWTO - C# + + + + +

    psqlODBC HOWTO - C#

    + +

    + + +Author: Dave Page (dpage@postgresql.org)
    +Release Date: 12 April 2002
    +Description: Example based Mini-Howto on Accessing PostgreSQL from C# +     +

    +This document provides some sample code to get you started with C# & PostgreSQL. +

    +Requirements to get the code to work: + +
    +

      +
    • A C# Compiler.
      • +
    • The Microsoft .NET Framework.
      • +
    • The Microsoft ODBC .NET Data Provider.
      • +
    • A PostgreSQL datasource.
      • +
    + +The example code shown below may need some modification to make it actually work in your environment. +There is one table used in the example: + +
    +
    +
    +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());
+
    +
    + +

    Code

    + +
    +
    +
+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 to continue...");
+    Console.Read();
+  }
+}
+
+
    +
    +

    + + + \ No newline at end of file diff --git a/docs/howto-debian.html b/docs/howto-debian.html new file mode 100644 index 0000000..5e1b07d --- /dev/null +++ b/docs/howto-debian.html @@ -0,0 +1,156 @@ + + + + + psqlODBC HOWTO - Debian + + + + +

    psqlODBC HOWTO - Debian

    + +

    + + +Author: Oliver Elphick (olly@lfix.co.uk)
    +Release Date: 17 April 2002
    +Amendments: 16 July 2002
    +Amendments: 17 March 2003
    +Amendments: 11 August 2005
    +Description: Debian Linux: HOWTO set up odbc-postgresql with unixodbc to allow access to other Unix programs +     +

    +

    +Please be aware that these notes were written for the Debian installation of +PostgreSQL and UnixODBC. With other distributions, or with the upstream +source, package names and pathnames will be different. + +

    +1. Install odbc-postgresql and the packages unixodbc and odbcinst1

    + +2. Add a PostgreSQL driver to /etc/odbcinst.ini:

    + +

    # odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template
    + +3. Add the template1 database (with read only access) to the default odbc.ini

    + +
    # cat /usr/share/doc/odbc-postgresql/examples/odbc.ini.template >>/etc/odbc.ini
    + + Edit /etc/odbc.ini as required, to add extra databases, change the read only + status and so on.

    + +4. For release 07 of psqlodbc, add ODBC required functions to the databases that you wish to access from ODBC:

    + +
    + # su -s /bin/bash - postgres
    + $ for dbname in database1 database2 ...
    + do
    +     psql -d $dbname < /usr/share/psqlodbc/odbc.sql
    + done
    +
    + + If you include template1 in the list, any database created in future will + automatically have these functions included when it is created. +

    +

    For release 08, step 4 is not required, because the driver does the conversion automatically. +

    +

    +At this point, you should be able to use the ODBC access features of any +application to connect to any of the listed databases. For example, in +StarOffice, you can click New->Database, select the ODBC database type +and browse the list of databases from /etc/odbc.ini and ~/.odbc.ini. +

    + +

    +Each user has or can have a file ~/.odbc.ini, whose structure is the same +as /etc/odbc.ini. This file is read together with the /etc/odbc.ini to give +a complete list of databases accessible to the user. (Of course, any +access restrictions imposed by pg_hba.conf are still applicable.) +

    + +

    +You can create templates for different databases and allow users to add +them to their own ~/.odbc.ini. Use the odbc.ini template file provided in +/usr/share/doc/odbc-postgresql/examples/odbc.ini.template as a model. +

    + +

    +A good and reliable way for a user to add a template to his ~/.odbc.ini +without risk of damaging his file is for him to use this command: +

    + +
    $ odbcinst -i -s -f /path/to/your/template/file
    + +

    +Note: I have had a report that OpenOffice objects to tabs and/or whitespace +in ~/.odbc.ini and /etc/odbc.ini. I have not found this problem with +StarOffice 5, however. Neither does it seem to be a problem in openoffice.org 1.4. +

    + +Sample odbcinst.ini file + +
    +
    +[PostgreSQL]
+Description=PostgreSQL ODBC driver for Linux and Windows
+Driver=/usr/lib/postgresql/lib/libodbcpsql.so
+Setup=/usr/lib/odbc/libodbcpsqlS.so
+Debug = 0
+CommLog = 1
+
    +
    + +Sample odbc.ini file + +
    +
    +[PostgreSQL]
+Description         = PostgreSQL template1
+Driver              = PostgreSQL
+Trace               = No
+TraceFile           = /tmp/odbc.log
+Database            = template1
+Servername          = localhost
+UserName            =
+Password            =
+Port                = 5432
+Protocol            = 6.4
+ReadOnly            = Yes
+RowVersioning       = No
+ShowSystemTables    = No
+ShowOidColumn       = No
+FakeOidIndex        = No
+ConnSettings        =
+
    +
    + +

    This additional sample connection sets the schema search path in ConnSettings, to give +access to a schema that would otherwise be inaccessible.

    + +
    +
    +[Production]
+Description         = Production control 
+databaseDriver      = PostgreSQL
+Trace               = NoTrace
+File                = /tmp/odbc.log
+Database            = production
+Servername          = localhost
+UserName            = 
+Password            =
+Port                = 5432
+Protocol            = 6.4
+ReadOnly            = No
+RowVersioning       = Yes
+ShowSystemTables    = No
+ShowOidColumn       = No
+FakeOidIndex        = No
+ConnSettings        = SET SEARCH_PATH TO prod, public
+
+
    +
    + +

    + + + \ No newline at end of file diff --git a/docs/howto-redhat.html b/docs/howto-redhat.html new file mode 100644 index 0000000..86ae93a --- /dev/null +++ b/docs/howto-redhat.html @@ -0,0 +1,251 @@ + + + + + psqlODBC HOWTO - Redhat + + + + +

    psqlODBC HOWTO - Redhat

    + +

    + + +Author: Pavel Cenek (xcenek@fi.muni.cz)
    +Release Date: 12 February 2002
    +Description: Installation and configuration of PostgreSQL & ODBC on Redhat Linux +     +

    + +0) Notes

    + +I tried the installation on Red Hat Linux 7.1 with PostgreSQL 7.1.3 Some tricks which are not +documented in one place (or are not documented at all) are written here. This file is not +intended to replace PostgreSQL documentation. If you have any trouble read the +manual!!

    + +Every rpm has version number in the name, I use names without versions to +refer to rpm files in the following text.

    + + +1) Installing and configuring PostgreSQL

    + +From ftp://ftp.postgresql.org or preferrably from a mirror site download the following rpms:

    + +

    + + + + + + +
    postgresqlclient binaries, docs, man pages
    postgresql-serverserver binaries, some docs
    postgresql-libsshared libraries
    postgresql-testfiles for testing DB functionality
    postgresql-develheader files and developers libraries
    +
    + +

    + +Type +
    +

    rpm -i

    +
    +

    for installing, or

    +
    +

    rpm -U

    +
    +

    for upgrade.

    + +After installing we ensure that PostgreSQL will be started at boot time.

    + +Add a symlink from

    +
    +

    /etc/rc.d/rcL.d/KXYpostgresql

    +
    +

    and

    +
    +

    /etc/rc.d/rcL.d/SXYpostgresql

    +
    +

    to

    +
    +

    /etc/rc.d/init.d/postgresql

    +
    +

    for every runlevel L on which you want postgres to be started (XY is a number). + +If you want to access the DB via TCP/IP (required for ODBC access), in the file + /etc/rc.d/init.d/postgresql +add to the line looking like this:

    +
    +

    su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D -p /usr/bin/postmaster start > /dev/null 2>&1" < /dev/null

    +
    +

    the parameters -o & -i + e.g.

    +
    +

    su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D -o -i -p /usr/bin/postmaster start > /dev/null 2>&1" < /dev/null + +

    +
    +

    The last thing to do is to set up access rights in file + /var/lib/pgsql/data/pg_hba.conf as required.

    +

    Now, type:

    +
    +

    /etc/rc.d/init.d/postgresql start

    +
    +

    to start the DB server. + +You can go to the directory + /usr/lib/pgsql/test/regress +and run tests of DB functionality (read README in that directory) if required.

    +

    2) Installing and configuring ODBC

    +

    From ftp://ftp.postgresql.org or from a mirror site get from the section "software" following rpm: + + +

    + +
    + + + + + +
    postgresql-odbclibraries and ini files
    +
    + +Type +
    +

    rpm -i

    +
    +

    for installing, or

    +
    +

    rpm -U

    +
    +

    for upgrade.

    + +Type:

    +
    +

    psql -d template1 -f /usr/share/pgsql/odbc.sql + + +

    +
    +

    to add some extra sql definitions to the template1 database. + + +

    +

    From the PostgreSQL interactive terminal (psql), in the template1 database, execute the following SQL:

    +
    +

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

    +
    +

    to add support for blobs accessible via ODBC. Quit psql with the \q command. Every created database inherits all +definitions from template1 unless specified otherwise, so you should have all definitions in every DB you create. + + +

    +

    3) Installing and configuring iODBC Driver Manager

    +

    From http://www.iodbc.org/ get from the section "software download", the +iODBC Driver Manager, i.e. following rpms: + +

    + +
    + + + + + + + + + +
    libiodbciODBC Driver Manager Runtime Package (libraries, config files)
    libiodbc-develiODBC Developers Kit (odbc header files and developers libraries)
    +
    + +Type +
    +

    rpm -i

    +
    +

    for installing, or

    +
    +

    rpm -U

    +
    +

    for upgrade.

    + +Now we have to configure /etc/odbc.ini and and /etc/odbcinst.ini if you only use PostgreSQL via ODBC, you can replace /etc/odbcinst.ini with /etc/pgsql/odbcinst.ini:

    +
    +

    cp /etc/pgsql/odbcinst.ini /etc/odbcinst.ini + +

    +
    +

    Otherwise you have to edit /etc/odbcinst.ini and copy the relevant parts from /etc/pgsql/odbcinst.ini. Be careful, this file is PARTIALLY case sensitive. It means some attributes are, some are not. If you have trouble with connection to PostgreSQL via ODBC, check if parameters in your /etc/odbc.ini have the same case as in the following example: + +

    +
    + +------ /etc/odbc.ini file begin -----
    + ;
    + ; odbc.ini
    + ;
    + [ODBC Data Sources]
    + test = PostgreSQL Test
    +
    + [test]
    + Driver=/usr/lib/libpsqlodbc.so
    + Description=Sample PostgreSQL DSN
    + DSN=test
    + Servername=localhost
    + Username=gin2
    + Database=mydb
    + ReadOnly=No
    + Servertype=postgres
    + Port=5432
    + FetchBufferSize=99
    + ServerOptions=
    + ConnectOptions=
    + Options=
    + ReadOnly=no
    + Trace=1
    + TraceFile=/home/gin2/odbc.trace
    + Debug=1
    + DebugFile=/home/gin2/odbc.debug
    + CommLog=1
    +

    +[Default]
    + Driver = /usr/lib/libpsqlodbc.so
    +
    + [ODBC]
    + InstallDir = /usr/lib/libiodbc.so
    +
    + ------ /etc/odbc.ini file end -----     +

    +
    +

    If you have done everything properly :-), the installation and setup is now finished.

    +

    4. Testing ODBC

    +

    With iODBC Driver Manager package should be distributed a small testing program odbctest. I haven't found it, so I took its source directly from CVS and compiled it myself. This command worked fine for me: + +

    +
    +

    gcc -c odbctest.c -o odbctest.o gcc -s -o odbctest odbctest.o -liodbc +

    +
    +

    Notice the -liodbc -- every program using ODBC must be linked with iodbc library. It is also not very well documented (at least I found it nowhere). You can now run the odbctest binary. If you run it without parameters, it asks you for an ODBC connect string. If you enter '?', it lists all dsn names (taken from /etc/odbc.ini. Be careful, the right ODBC connect string is not "name", but "dsn=name"!!! If you have trouble, look into ~/odbc.trace If you don't have trouble, continue reading...

    +

    Congratulations, now everything should work fine :)

    +

    I hope I saved you couple of days, which I spent looking for this information.

    + +

    The following additional notes were supplied by Dick Wieland

    +

    - The iODBC documentation species HOST, not SERVERNAME as the parameter used to +designate the remote server. If you use HOST, the odbctest program returns with nothing more than “Have a nice day”.

    +

    - The iODBC documentation lists /usr/local/etc as the default location for odbc.ini. /etc is the default location.

    + +

    + + + \ No newline at end of file diff --git a/docs/howto-vb.html b/docs/howto-vb.html new file mode 100644 index 0000000..4797dbd --- /dev/null +++ b/docs/howto-vb.html @@ -0,0 +1,128 @@ + + + + + psqlODBC HOWTO - Visual Basic + + + + +

    psqlODBC HOWTO - Visual Basic

    + +

    + + +Author: Dave Page (dpage@postgresql.org)
    +Release Date: 13 November 2001
    +Description: Example based Mini-Howto on Accessing PostgreSQL from Visual Basic +     +

    +This document provides some sample code to get you started with Visual Basic & PostgreSQL. +

    +Requirements to get the subroutines to work: + +
    +

      +
    • Visual Basic 5/6
      • +
    • A reference in the VB project to Microsoft ActiveX Data Objects
      • +
    • A PostgreSQL datasource.
      • +
    + +The example code shown below may need some modification to make it actually work in your environment. +There is one table used in the example: + +
    +
    + +CREATE TABLE vbtest(
    +   id int4,
    +   data text,
    +   accessed timestamp
    +); +     +
    + +

    Code

    +
    +
    +
+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>"
+ 
+  '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 FROM vbtest", cn, adOpenDynamic
+ 
+  '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.Refresh
+
+  '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
+
+
    +
    +

    + +

    Useful Functions

    +
    +
    +
+' The escapeString function can be used to fix strings for us in INSERT and 
+' UPDATE SQL statements. It will take a value, and return it ready for 
+' use in your statement as NULL, or quoted with backslashes and single quotes
+' escaped.
+
+Function escapeString(vValue As Variant) As String
+
+  If IsNull(vValue) Then
+    escapeString = "NULL"
+  else
+    escapeString = "'" & Replace(Replace(vValue, "", ""), "'", "''") & "'" 
+  end if
+
+End Function
+
+
    +
    +

    + + + \ No newline at end of file diff --git a/docs/howto-vblo.html b/docs/howto-vblo.html new file mode 100644 index 0000000..38db453 --- /dev/null +++ b/docs/howto-vblo.html @@ -0,0 +1,349 @@ + + + + + psqlODBC HOWTO - Visual Basic Large Objects + + + + +

    psqlODBC HOWTO - Visual Basic Large Objects

    + +

    + + +Author: Denis Gasparin (denis@edistar.com)
    +Release Date: 29 October 2001
    +Description: Example based Mini-Howto on Pgsql Large Objects Interface and Visual Basic +     +

    +This document tells about using Large Objects and Visual Basic. All the main connection +interfaces available in VB are discussed: DAO, ADO and RDO. +

    +Requirements to get the subroutines to work: + +
    +

      +
    • DAO, ADO and RDO interfaces available in VB
      • +
    • Created the lo type using the appropriate functions available in contrib/lo in the Postgresql source tree
      • +
    • Installed and properly configured the latest version of the Postgresql ODBC driver.
      • +
    + +In the example the database used has one table with only two fields. Here is the SQL definition: + +
    +
    + +CREATE TABLE MYTABLE(
    +   MAIN INTEGER,
    +   OBJECT LO
    +); +     +
    + +The ODBC DSN I used in the example is named pgsql_test_blob. To insert a record, I +suggest you to use the INSERT sql statement instead of the AddNew method (available +in DAO, RDO and ADO). The AddNew method force you to declare a Recordset and this is +bad because when you open it, VB creates a cursor and has to pass all the records in +the table, slowing your application significantly. +

    +I think the examples are very simple and self explanatory. Some tips about which +interface to use: + +
    +
      +
    • ADO and RDO are the best interfaces to connect to PostgreSQL. Personally, I think ADO + is better only because it is new and actively supported by Microsoft. RDO is the + old interface and it is not more developed.
      • +
    • DAO is very, very heavy and I suggest you not use it unless you are forced to do so.
      • +
    + +

    DAO (Data Access Objects)

    +
    +
    +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
+
    +
    + +

    ADO (ActiveX Data Objects)

    +
    +
    +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
+
    +
    + +

    RDO (Remote Data Objects)

    +
    +
    +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
+
    +
    +

    + + + \ No newline at end of file diff --git a/docs/index.html b/docs/index.html new file mode 100644 index 0000000..433d6a9 --- /dev/null +++ b/docs/index.html @@ -0,0 +1,70 @@ + + + + + psqlODBC + + + + +

    psqlODBC

    + +

    Introduction

    + +

    psqlODBC is the official PostgreSQL ODBC Driver.

    +

    The source code for this driver was originally adopted from version 0.20 of PostODBC. The +authors at that time were Christian Czezatke and Dan McGuirk. Later it was maintained by Julie Ann Case.

    + +

    As part of a commercial research project, +the developers at Insight Distributions +System overhauled the driver. Their goal was to make the driver +commercially viable. In keeping with the spirit with which +the original source was acquired, Insight published their work at this +location. Some time later and after some discussion with members +of the PostgreSQL organization, psqlODBC was adapted as the part of the +PostgreSQL source distribution. The driver continued to be maintained by +Byron Nikolaidis, the developer at Insight who overhauled the driver for some time.

    + +

    The driver is currently maintained by a number of contributors to the +PostgreSQL project. +It is developed and supported through the +pgsql-odbc@postgresql.org mailing list.

    + +

    psqlODBC is released under the Library General Public Licence, or LGPL.

    + +

    psqlODBC Documentation

    + +

    The following documents contain various bits of useful information. Please send any additional +documentation, or report errors/omissions to +pgsql-odbc@postgresql.org

    + + + +

    psqlODBC HOWTOs

    + +

    The following HOWTOs have been contributed by various people. If you wish to add to the +collection, please send your contribution to +pgsql-odbc@postgresql.org

    + + diff --git a/docs/win32-compilation.html b/docs/win32-compilation.html new file mode 100644 index 0000000..251cc79 --- /dev/null +++ b/docs/win32-compilation.html @@ -0,0 +1,76 @@ + + + + + Compiling psqlODBC on Windows + + + + +

    Compiling psqlODBC on Windows

    + +

    +This page describes how to build the PostgreSQL ODBC Driver (psqlodbc.dll) on Win32 platforms. +Microsoft Visual C++ version 4.0 or higher is required. Other compilers may work +but have not been formally tested. The psqlodbc.dll may be built either in the +VC++ IDE or from the command line: +

    + +

    IDE Method

    + +
      +
    1. Create a new project workspace with the type DLL. For the name, type in the + name "psqlodbc".
      2. + +
    2. The above step creates the directory "psqlodbc" under the + "\\<Visual C++ top level directory>\\projects" path to hold the source files. + (example, \\msdev\\projects\\psqlodbc). Now, either unzip the source code release + into this directory or just copy all the files into this directory.
      3. + +
    3. Insert all of the source files (*.c, *.h, *.rc, *.def) into the Visual project + using the "Insert files into project" command. You may have to do 2 inserts -- + the first to get the 'c' and header files, and the second to get the def file. + Note, that from 07.01.0009, the file md5.c should not included in the project, + this file is only used under *nix - the file win_md5.c is used instead in Win32. + The file md5.h is required. + Don't forget the .def file since it is an important part of the release. + You can even insert ".txt" files into the projects -- they will do nothing.
      4. + +
    4. Add the "wsock32.lib" library to the end of the list of libraries for linking + using the Build settings menu.
      5. + +
    5. Select the type of build on the toolbar (i.e., Release or Debug). This is + one of the useful features of the visual c++ environment in that you can + browse the entire project if you build the "Debug" release. For release + purposes however, select "Release" build.
      6. + +
    6. Build the dll by selecting Build from the build menu.
      7. + +
    7. When complete, the "psqlodbc.dll" file is under the "Release" subdirectory. + (i.e., "\\msdev\\projects\\psqlodbc\\release\\psqlodbc.dll")
      8. +
    + +

    + +

    Command Line Method

    + +

    + +

      +
    1. From a command prompt, run the vcvars32.bat file that comes with Visual C++. This will +setup the environment for using the VC++ compiler.
      2. + +
    2. CD to the directory containing the source code.
      3. + +
    3. Use NMAKE to build the dll eg:

      + + C:\\psqlodbc\\> nmake /f win32.mak CFG=Release ALL

      + + Possible configurations are Release and Debug.
      + Possible build types are ALL or CLEAN.
      4. +
    + +

    + + + \ No newline at end of file -- 2.39.5