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

最新推荐文章于 2022-05-16 23:51:29 发布

weixin_34302561

最新推荐文章于 2022-05-16 23:51:29 发布

阅读量349

点赞数

文章标签：操作系统数据库

原文链接：http://www.cnblogs.com/he11o-liu/p/7503232.html

版权

安装`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

测试与unixODBC连接

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>           
int main() {
  SQLHENV env;
  char driver[256];
  char attr[256];
  SQLSMALLINT driver_ret;
  SQLSMALLINT attr_ret;
  SQLUSMALLINT direction;
  SQLRETURN ret;

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

  direction = SQL_FETCH_FIRST;
  while(SQL_SUCCEEDED(ret = SQLDrivers(env, direction,
                       (unsigned char *)driver, sizeof(driver), &driver_ret,
                       (unsigned char *)attr, sizeof(attr), &attr_ret))) {
    direction = SQL_FETCH_NEXT;
    printf("%s - %s\n", driver, attr);
    if (ret == SQL_SUCCESS_WITH_INFO) printf("\tdata truncation\n");
  }
}

测试结果如下:

$ ./odbc 
PostgreSQL - Description=PostgreSQL driver for Linux

测试ODBC功能

#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
#include <stdio.h>
#include <sqlucode.h>
#include <odbcinst.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);
     }
}

功能测试通过

$ ./simple 
SQLAllocHandle() OK
SQLSetEnvAttr() ok
SQLAllocHandle() ok
SQLSetConnectAttr() ok

测试获取全部数据

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>


SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;

void extract_error(
    char *fn,
    SQLHANDLE handle,
    SQLSMALLINT type)
{
    SQLINTEGER   i = 0;
    SQLINTEGER   native;
    SQLCHAR  state[ 7 ];
    SQLCHAR  text[256];
    SQLSMALLINT  len;
    SQLRETURN    ret;

    fprintf(stderr,
            "\n"
            "The driver reported the following diagnostics whilst running "
            "%s\n\n",
            fn);

    do
    {
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len );
        if (SQL_SUCCEEDED(ret))
            printf("%s:%ld:%ld:%s\n", state, i, native, text);
    }
    while( ret == SQL_SUCCESS );
}


int main()
{
    SQLRETURN ret;       /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    SQLSMALLINT rows; /* number of columns in result-set */
    int row = 0;
    int j = 0,i = 0;
    SQLINTEGER indicator;
    char buf[512];

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    /* Connect to the DSN mydsn */
    /* You will need to change mydsn to one you have created and tested */
    ret = SQLDriverConnect(dbc, NULL, "DSN=Liu;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret)){ printf("Connected\n");}
    /* Allocate a statement handle */
    ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    if (ret == -1) { printf("fail\n");}
    /* Retrieve a list of tables */
    // SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
    ret = SQLExecDirect(stmt, "select * from student", SQL_NTS);
    // printf("%d\n",ret);
    /* How many columns are there */
    SQLNumResultCols(stmt, &columns);
    printf("Columns:%d\n",columns);
    // /* How many rows are there */
    // ret = SQLFetch(stmt);
    // ret = SQLRowCount(stmt,&rows);
    // printf("%d\n",rows);
    /* Loop through the rows in the result-set */

    while (SQL_SUCCEEDED(ret = SQLFetch(stmt))){
        printf("Row %d\n", row++);
        j  = 1;
        /* Loop through the columns */
        while(j <= columns){
            i = j;
            /* retrieve column data as a string */
            ret = SQLGetData(stmt, i, SQL_C_CHAR, buf, sizeof(buf), &indicator);
            if (SQL_SUCCEEDED(ret)){
                /* Handle null columns */
                if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
                printf("\tColumn %u : %10s len:%d\n",i, buf,indicator);
            }
            j++;
        }
    }
}

测试输出如下

$ ./fetch   
Connected
Columns:2
Row 0
    Column 0 :          0 len:1
    Column 0 : stu_0                len:20
Row 1
    Column 0 :          1 len:1
    Column 0 : stu_1                len:20
Row 2
    Column 0 :          2 len:1
    Column 0 : stu_2                len:20
Row 3
    Column 0 :          3 len:1
    Column 0 : stu_3                len:20
Row 4
    Column 0 :          4 len:1
    Column 0 : stu_4                len:20
Row 5
    Column 0 :          5 len:1
    Column 0 : stu_5                len:20

测试修改数据

int main()
{
    SQLRETURN ret;       /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    SQLSMALLINT rows; /* number of columns in result-set */
    int row = 0;
    int j = 0,i = 0;
    SQLINTEGER indicator;
    char buf[512];

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    /* Connect to the DSN mydsn */
    /* You will need to change mydsn to one you have created and tested */
    ret = SQLDriverConnect(dbc, NULL, "DSN=Liu;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret)){ printf("Connected\n");}
    /* Allocate a statement handle */
    ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    if (ret == -1) { printf("fail\n");}
    /* Retrieve a list of tables */
    ret = SQLExecDirect(stmt, "update student set sname = 'Liu' where sno = 1;", SQL_NTS);
    if (ret == -1) { printf("fail\n");}
    ret = SQLExecDirect(stmt, "insert into student values(1010,'test');", SQL_NTS);
    if (ret == -1) { printf("fail\n");}
    ret = SQLExecDirect(stmt, "delete from student where sno = 1010;", SQL_NTS);
    if (ret == -1) { printf("fail\n");}
    return 0;
}

连接`SQLite3`

这个地方很坑

下载SQLiteODBC

$ brew info sqliteodbc
sqliteodbc: stable 0.9995 (bottled)
SQLite ODBC driver
http://www.ch-werner.de/sqliteodbc/
/usr/local/Cellar/sqliteodbc/0.9995 (11 files, 356.7KB) *
  Poured from bottle on 2017-05-22 at 20:52:10
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/sqliteodbc.rb
==> Dependencies
Required: sqlite ✔, unixodbc ✔

配置`odbc.ini`与`odbcinst.ini`

odbc.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

[SQLlite]
Description=ODBC for SQLite
Driver=/usr/local/Cellar/sqliteodbc/0.9995/lib/libsqlite3odbc.so
Setup=/usr/local/Cellar/sqliteodbc/0.9995/lib/libsqlite3odbc.so
UsageCount=5

odbcinst.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

[Nian]
Description=Nian
Driver=SQLlite
Trace=Yes
Database=student.db
TraceFile=sql.log
Servername=localhost
Port=1433
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ShowOidColumn=No
FakeOidIndex=No

这里要注意 SQLite的数据库要选择具体文件

SQLite测试连接

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

int main()
{
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */
    SQLCHAR outstr[1024];
    SQLSMALLINT outstrlen;

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    /* Connect to the DSN mydsn */
    ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)"DSN=Nian;", SQL_NTS,
                           outstr, sizeof(outstr), &outstrlen,
                           SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret))
    {
        printf("Connected\n");
        printf("Returned connection string was:\n\t%s\n", outstr);
        if (ret == SQL_SUCCESS_WITH_INFO){
            printf("Driver reported the following diagnostics\n");
        }
        SQLDisconnect(dbc); /* disconnect from driver */
    }
    else
    {
        fprintf(stderr, "Failed to connect\n");
    }
    /* free up allocated handles */
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
}

测试fetch数据

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;

int main()
{
    SQLRETURN ret;       /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    SQLSMALLINT rows; /* number of columns in result-set */
    int row = 0;
    int j = 0,i = 0;
    SQLINTEGER indicator;
    char buf[512];

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    /* Connect to the DSN mydsn */
    /* You will need to change mydsn to one you have created and tested */
    ret = SQLDriverConnect(dbc, NULL, "DSN=Nian;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret)){ printf("Connected\n");}
    /* Allocate a statement handle */
    ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    if (ret == -1) { printf("fail\n");}
    /* Retrieve a list of tables */
    SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);

    ret = SQLExecDirect(stmt, "select * from student;", SQL_NTS);
    printf("return %d\n",ret);
    // printf("%d\n",ret); 
    /* How many columns are there */
    SQLNumResultCols(stmt, &columns);
    printf("Columns:%d\n",columns);
    // /* How many rows are there */
    // ret = SQLFetch(stmt);
    // ret = SQLRowCount(stmt,&rows);
    // printf("%d\n",rows);
    /* Loop through the rows in the result-set */

    while (SQL_SUCCEEDED(ret = SQLFetch(stmt))){
        printf("Row %d\n", row++);
        j  = 1;
        /* Loop through the columns */
        while(j <= columns){
            i = j;
            /* retrieve column data as a string */
            ret = SQLGetData(stmt, i, SQL_C_CHAR, buf, sizeof(buf), &indicator);
            if (SQL_SUCCEEDED(ret)){
                /* Handle null columns */
                if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
                printf("\tColumn %u : %10s len:%d\n",i, buf,indicator);
            }
            j++;
        }
    }

}

简单数据库迁移

这里只是最简单的数据库迁移演示，直接用字符串缓冲区进行操作。

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
char readdata[200][2][30];
int row;

void read_data();
void write_data();

int main(){
    read_data();
    write_data();
}


void write_data(){
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret;       /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    SQLSMALLINT rows; /* number of columns in result-set */
    int j = 0,i = 0;
    SQLINTEGER indicator;
    char buf[512];

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    /* Connect to the DSN mydsn */
    /* You will need to change mydsn to one you have created and tested */
    ret = SQLDriverConnect(dbc, NULL, "DSN=Nian;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret)){ printf("Connected\n");}
    /* Allocate a statement handle */
    ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    if (ret == -1) { printf("fail\n");}
    /* Retrieve a list of tables */
    printf("row%d\n",row);

    for(i = 0; i< row;i++){
        printf("Row %d\n",i);
        sprintf(buf,"insert into student values(%s,\'%s\');",readdata[i][0],readdata[i][1]); 
        printf("%s\n",buf);
        ret = SQLExecDirect(stmt,buf, SQL_NTS);
        if (ret == -1) { printf("fail\n");}
    }
}
void read_data(){
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret;       /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    SQLSMALLINT rows; /* number of columns in result-set */
    int j = 0,i = 0;
    SQLINTEGER indicator;
    char buf[512];
    row = 0;
    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    /* Connect to the DSN mydsn */
    /* You will need to change mydsn to one you have created and tested */
    ret = SQLDriverConnect(dbc, NULL, "DSN=Liu;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret)){ printf("Connected\n");}
    /* Allocate a statement handle */
    ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    if (ret == -1) { printf("fail\n");}
    /* Retrieve a list of tables */
    // SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
    ret = SQLExecDirect(stmt, "select * from student", SQL_NTS);
    // printf("%d\n",ret);
    /* How many columns are there */
    SQLNumResultCols(stmt, &columns);
    printf("Columns:%d\n",columns);
    // /* How many rows are there */
    // ret = SQLFetch(stmt);
    // ret = SQLRowCount(stmt,&rows);
    // printf("%d\n",rows);
    /* Loop through the rows in the result-set */

    while (SQL_SUCCEEDED(ret = SQLFetch(stmt))){
        printf("Row %d\n", row++);
        j  = 1;
        /* Loop through the columns */
        while(j <= columns){
            i = j;
            /* retrieve column data as a string */
            ret = SQLGetData(stmt, i, SQL_C_CHAR, buf, sizeof(buf), &indicator);
            if (SQL_SUCCEEDED(ret)){
                /* Handle null columns */
                if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
                buf[indicator] = '\0';
                strcpy(readdata[row][j-1],buf);
                printf("\tColumn %u :%s len:%d\n",i, readdata[row][j-1],indicator);
            }
            j++;
        }
    }
}

SQLite

$ sqlite3 student.db
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> select * from student;
1|Liu
0|stu_0               
2|stu_2               
3|stu_3               
4|stu_4               
5|stu_5               
6|stu_6               
7|stu_7               
8|stu_8               
9|stu_9  
...

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 odbc connect fetch update
simple:simple.c
    gcc -o simple simple.c  $(ODBCLIB) $(ODBCINC)
odbc:odbc.c
    gcc -o odbc odbc.c  $(ODBCLIB) $(ODBCINC)
connect:connect.c
    gcc -o connect connect.c  $(ODBCLIB) $(ODBCINC)
connectlite:connectlite.c
    gcc -o connectlite connectlite.c  $(ODBCLIB) $(ODBCINC)
fetch:fetchresult.c
    gcc -o fetch fetchresult.c  $(ODBCLIB) $(ODBCINC)
fetchlite:fetchresultlite.c
    gcc -o fetchlite fetchresultlite.c  $(ODBCLIB) $(ODBCINC)
update:updatedata.c
    gcc -o update updatedata.c  $(ODBCLIB) $(ODBCINC)
trans:transdata.c
    gcc -o trans transdata.c  $(ODBCLIB) $(ODBCINC)
clean:
    -rm odbc
    -rm simple
    ...

转载于:https://www.cnblogs.com/he11o-liu/p/7503232.html

weixin_34302561

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
macOS上的ODBC-利用unixODBC连接PostgreSQL与SQLite并进行数据迁移

安装UnixODBC & PSQLODBC driver for UnixODBC$ brew install psqlodbcUpdating Homebrew...==> Installing dependencies for psqlodbc: postgresql, unixodbc==> Installing psqlodbc depe...
复制链接

扫一扫