how to use ODBC binding for Dart

问题:

I wanted to use the ODBC binding for DART this one in my app, I'm using ORACLE XE database, but did not get how to set it up, though I read the basic example in the read me :(

Attached a print screen of my ORACLE XE ODB setup, suceed connection, and simple SQL statement,

Appreciate to know the following: 1. Hoe to set-up my ODBC in DART, 2. How to get the same SQL statement executed.

I wrote the below:

void main() {
  // Allocate an environment handle.
 var hEnv = new SqlHandle();   
 sqlAllocHandle(SQL_HANDLE_ENV, null, hEnv);    
 // Set ODBC version to be used.
 var version = new SqlPointer()..value = SQL_OV_ODBC3;    
 sqlSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, version, 0);    
 // Allocate a connection handle.
 var hConn = new SqlHandle();

sqlAllocHandle(SQL_HANDLE_DBC, hEnv, hConn);

// Connect.
sqlConnect(hConn, "XE", SQL_NTS, "SYSTEM", SQL_NTS, "myPasswords", SQL_NTS);  // so, I need to change this data only!

// Allocate a statement handle.
var hStmt = new SqlHandle();

sqlAllocHandle(SQL_HANDLE_STMT, hConn, hStmt);

// Prepare a statement.
sqlPrepare(hStmt, "select * from Vendors", SQL_NTS);   // I need to print the output of this SQL!!

// Execute and fetch some data.
sqlExecute(hStmt);

sqlFetch(hStmt);


// Free statement handle.
sqlFreeHandle(SQL_HANDLE_STMT, hStmt);

// Disconnect.
sqlDisconnect(hConn);

// Free connection handle.
sqlFreeHandle(SQL_HANDLE_DBC, hConn);

// Free environment handle.
sqlFreeHandle(SQL_HANDLE_ENV, hEnv); 

}

thanks

回答1:

Thanks for Juan, the pub author. my mistake was, I'm using Windows x64, and defined my ODBC using std ODBC, did not notice, that this pub is for 32 bit, and I've to define the ODBC using:

(YOUR WINDOW DIRECTORY)\SysWOW64\odbcad32.exe

the below code worked perfectly

import 'package:odbc/odbc.dart';

void main() {
     var hEnv = new SqlHandle();
     sqlAllocHandle(SQL_HANDLE_ENV, null, hEnv);

     var version = new SqlPointer()..value = SQL_OV_ODBC3;
    sqlSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, version, 0);

    var hConn = new SqlHandle();
    sqlAllocHandle(SQL_HANDLE_DBC, hEnv, hConn);

    sqlConnect(hConn, "<DSN>", SQL_NTS, "<USER>", SQL_NTS, "<PASS>", SQL_NTS);

    var hStmt = new SqlHandle();

    sqlAllocHandle(SQL_HANDLE_STMT, hConn, hStmt);

    sqlPrepare(hStmt, "SELECT * FROM vendors", SQL_NTS);

    var col1 = new SqlLongBuffer();
    var flags1 = new SqlIntBuffer();
    sqlBindCol(hStmt, 1, col1.ctype(), col1.address(), 0, flags1.address());

    var col2 = new SqlLongBuffer();
    var flags2 = new SqlIntBuffer();
    sqlBindCol(hStmt, 2, col2.ctype(), col2.address(), 0, flags2.address());

    var col3 = new SqlStringBuffer(255);
    var flags3 = new SqlIntBuffer();
    sqlBindCol(hStmt, 3, col3.ctype(), col3.address(), col3.length(), flags3.address());

    var col4 = new SqlStringBuffer(255);
    var flags4 = new SqlIntBuffer();
    sqlBindCol(hStmt, 4, col4.ctype(), col4.address(), col4.length(), flags4.address());

    sqlExecute(hStmt);

    while (sqlFetch(hStmt) != SQL_NO_DATA_FOUND) {
          print("${col1.peek()} ${col2.peek()} ${col3.peek()} ${col4.peek()} ");
     }

   sqlFreeHandle(SQL_HANDLE_STMT, hStmt);

   sqlDisconnect(hConn);

   sqlFreeHandle(SQL_HANDLE_DBC, hConn);

   sqlFreeHandle(SQL_HANDLE_ENV, hEnv);
}

Table created:

      CREATE TABLE vendors (
             vendor_id NUMBER,
             vCode NUMBER,
             vName VARCHAR(255),
             vEmail VARCHAR(255),
             PRIMARY KEY (vendor_id));

Input data:

 INSERT INTO vendors VALUES(1, 111, 'One', '[e-mail]')
 INSERT INTO vendors VALUES(2, 222, 'Two', '[e-mail]');
 INSERT INTO vendors VALUES(3, 33, 'Three', '[e-mail]');

Console output:

     Observatory listening on http://127.0.0.1:49433
 1 111 One [e-mail] 
 2 222 Two [e-mail] 
 3 33 Three [e-mail] 

回答2:

This code line of the example code from the ODBC package README shows that you have to pass cutom <YOUR DSN>, <YOUR USER ID>, <YOUR PASSWORD>

sqlConnect(hConn, "<YOUR DSN>", SQL_NTS, "<YOUR USER ID>", SQL_NTS, "<YOUR PASSWORD>", SQL_NTS);

The <YOUR DSN> of your screenshot is XE. I don't know your user name and password :-P