macOS上的ODBC-利用unixODBC连接PostgreSQL与SQLite并进行数据迁移

安装UnixODBC & PSQLODBC driver for UnixODBC

$ brew install psqlodbc
Updating Homebrew...
==> Installing dependencies for psqlodbc: postgresql, unixodbc
==> Installing psqlodbc dependency: postgresql
==> Downloading https://homebrew.bintray.com/bottles/postgresql-9.6.3.sierra.bottle.ta
######################################################################## 100.0%
==> Pouring postgresql-9.6.3.sierra.bottle.tar.gz
==> Using the sandbox
==> /usr/local/Cellar/postgresql/9.6.3/bin/initdb /usr/local/var/postgres
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/legacy-homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.0) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.6/static/upgrading.html

To migrate existing data from a previous minor version (9.0-9.5) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.6/static/pgupgrade.html

  You will need your previous PostgreSQL installation from brew to perform `pg_upgrade`.
  Do not run `brew cleanup postgresql` until you have performed the migration.

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Summary
��  /usr/local/Cellar/postgresql/9.6.3: 3,259 files, 36.6MB
==> Installing psqlodbc dependency: unixodbc
==> Downloading https://homebrew.bintray.com/bottles/unixodbc-2.3.4.sierra.bottle.1.ta
######################################################################## 100.0%
==> Pouring unixodbc-2.3.4.sierra.bottle.1.tar.gz
��  /usr/local/Cellar/unixodbc/2.3.4: 43 files, 2.0MB
==> Installing psqlodbc 
==> Downloading https://homebrew.bintray.com/bottles/psqlodbc-09.06.0310.sierra.bottle
######################################################################## 100.0%
==> Pouring psqlodbc-09.06.0310.sierra.bottle.tar.gz
��  /usr/local/Cellar/psqlodbc/09.06.0310: 6 files, 796.1KB

$ brew services start postgresql
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

其中unixODBC的库在

/usr/local/Cellar/unixodbc/2.3.4

postgreSQLODBC库在

/usr/local/Cellar/psqlodbc/09.06.0310

配置UnixODBC

查看PSQL的默认配置

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "He11o_Liu")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

配置unix ODBC

odbcinst.ini

[PostgreSQL]  
Description=PostgreSQL driver for Linux  
Driver=/usr/local/Cellar/psqlodbc/09.06.0310/lib/psqlodbcw.so
Setup=/usr/local/Cellar/psqlodbc/09.06.0310/lib/psqlodbcw.so
UsageCount=1  

odbc.ini

[Liu]  
Description=Liu
Driver=PostgreSQL  
Trace=Yes  
TraceFile=sql.log  
Database=student  
Servername=localhost  
UserName=root  
Password=  
Port=5432
Protocol=6.4
ReadOnly=No  
RowVersioning=No  
ShowSystemTables=No  
ShowOidColumn=No  
FakeOidIndex=No

测试UnixODBC链接PSQL

利用isql测试UnixODBC与PSQL的连接

$ isql -v Liu         
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

出现的问题

坑人的Mac中的动态库dylib直接链接到目录不能够正常读出。必须手动一个个加上。

书写Makefile如下

ODBCLIB = /usr/local/Cellar/unixodbc/2.3.4/lib/libodbc.2.dylib /usr/local/Cellar/unixodbc/2.3.4/lib/libodbcinst.2.dylib /usr/local/Cellar/unixodbc/2.3.4/lib/libodbccr.2.dylib
ODBCINC = -I/usr/local/Cellar/unixodbc/2.3.4/include/
all:simple
simple:simple.c
    gcc -o simple simple.c  $(ODBCLIB) $(ODBCINC)
odbc:odbc.c
    gcc -o odbc odbc.c  $(ODBCLIB) $(ODBCINC)
clean:
    -rm odbc
    -rm simple

测试与u