[unixODBC-support] OSX 10.7 and Postgres

Gallagher James jgallagher at opendap.org
Tue Aug 28 20:22:54 BST 2012


On Aug 28, 2012, at 12:57 PM, Nick Gorham wrote:

> On 28/08/2012 19:05, Gallagher James wrote:
>> Hi,
>> 
>> I'm trying to get unixODBC 2.3.1 to work with Postgres 9.1.5 on OSX 10.7.
>> 
>> I have tried using both the postgres driver bundled with unixODBC and one downloaded from EnterpriseDB at this location (http://www.enterprisedb.com/products-services-training/pgdownload#osx).
>> 
>> Here's my odbcinst.ini file:
>> [PostgreSQL]
>> Description=PostgreSQL driver
>> Driver=/usr/local/lib/libodbcpsql.dylib
>> Threading=2
>> UsageCount=1
>> 
>> [PostgreSQL_Enterprise]
>> Description=PostgreSQL driver from EnterpriseDB
>> Driver=/Library/PostgreSQL/psqlODBC/lib/psqlodbcw.so
>> Threading=2
>> UsageCount=1
>> 
>> Note that I don't have entries for Server because those libraries don't seem to have been installed. From what I've read they are not required unless I'm using the GUI to configure things.
>> 
>> My odbc.ini file is:
>> [unixODBC]
>> Description         = SQLHandler test for POSTGRES
>> Driver              = PostgreSQL
>> Trace               = Yes
>> TraceFile           = sql.log
>> Database            = postgres
>> Servername          = localhost
>> UserName            = postgres
>> Password            = x
>> Port                = 5432
>> Protocol            = 6.4
>> ReadOnly            = No
>> RowVersioning       = No
>> ShowSystemTables    = No
>> ShowOidColumn       = No
>> FakeOidIndex        = No
>> ConnSettings        =
>> 
>> [enterprise]
>> Description         = SQLHandler test for POSTGRES
>> Driver              = PostgreSQL_Enterpise
>> Trace               = Yes
>> TraceFile           = sql.log
>> Database            = postgres
>> Servername          = localhost
>> UserName            = postgres
>> Password            = x
>> Port                = 5432
>> Protocol            = 6.4
>> ReadOnly            = No
>> RowVersioning       = No
>> ShowSystemTables    = No
>> ShowOidColumn       = No
>> FakeOidIndex        = No
>> ConnSettings        =
>> jimg:src$
>> 
>> When I use isql ( < sqlh.sql) I get errors:
>> 
>> +---------------------------------------+
>> | Connected!                            |
>> |                                       |
>> | sql-statement                         |
>> | help [tablename]                      |
>> | quit                                  |
>> |                                       |
>> +---------------------------------------+
>> [ISQL]ERROR: Could not SQLExecute
>> [ISQL]ERROR: Could not SQLExecute
>>>> 
>> When I use the 'enterprise' DSN, I get:
>> 
>> [ISQL]ERROR: Could not SQLConnect
>> 
>> The file sqlh.sql has some commands to create a table and populate it with a handful of rows.
>> 
>> Thanks,
>> James
>> --
>> James Gallagher
>> jgallagher at opendap.org
>> 406.723.8663
> 
> Try
> 
> isql -v unixODBC postgres x
> 
> first to see if it connects, then try the command with -v so you get a display of any errors.

Thanks for the prompt reply!

It seems to be connecting!

SQL> quit
jimg:install.dir$ isql -v unixODBC postgres postgres
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit


jimg:install.dir$ isql -v unixODBC postgres postgres < sqlh.sql 
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
[01000]Error while executing the query (non-fatal);
ERROR:  syntax error at end of input at character 27
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  syntax error at or near ""int"" at character 3
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  syntax error at or near ""real"" at character 3
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  syntax error at or near ""varchar"" at character 3
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  syntax error at or near "CONSTRAINT" at character 3
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  syntax error at or near ")" at character 1
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  relation "sqlh_table" does not exist
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  relation "sqlh_table" does not exist
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  unterminated quoted string at or near "'comment ''real''" at character 40
[ISQL]ERROR: Could not SQLExecute
[01000]Error while executing the query (non-fatal);
ERROR:  unterminated quoted string at or near "';" at character 1
[ISQL]ERROR: Could not SQLExecute
jimg:install.dir$ 

So it looks like the SQL is hosed.

Here's the file:

CREATE TABLE "sqlh_table"(
  "int" integer NOT NULL DEFAULT 0,
  "real" real NOT NULL DEFAULT 0,
  "varchar" character varying(50),
  CONSTRAINT pk PRIMARY KEY ("int")
);
ALTER TABLE sqlh_table OWNER TO postgres;
COMMENT ON COLUMN sqlh_table."int" IS 'comment';
COMMENT ON COLUMN sqlh_table."real" IS 'comment ''real''
';

INSERT INTO sqlh_table("int", "real", "varchar") VALUES 
        ('1', '81.0', 'string_a'),
        ('2', '61.1', 'string_b'),
        ('3', '51.0', 'string_c'),
        ('4', '2100.0', 'string_d'),
        ('5', '21.0', 'string_e'),
        ('6', '4133.0', 'string_f'),
        ('7', '31.4', 'string_g'),
        ('8', '21.3', 'string_h'),
        ('9', '11.6', 'string_i'),
        ('10', '22.2', 'string_j')
    ;
\commit
\quit

Aside from the weird comments, this seems like it should work (but I don't know much about RDBs and know zero about SQL).

Thanks,
James
> 
> -- 
> Nick
> 
> _______________________________________________
> unixODBC-support mailing list
> unixODBC-support at mailman.unixodbc.org
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support

--
James Gallagher
jgallagher at opendap.org
406.723.8663

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 203 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-support/attachments/20120828/9ac8b704/attachment.bin>


More information about the unixODBC-support mailing list