使用odbc驱动
使用ODBC¶
本章节给出了一些使用ODBC的简单示例，你可以从中了解在几种常见的编程语言中如何使用ODBC驱动来访问数据库。
从C#中访问¶
需要的环境准备
-
C# 编译器
-
Microsoft .NET Framework
-
Microsoft ODBC .NET Data Provider.
-
SeaboxSQL数据库源
下面的代码可能需要根据实际环境进行适当的修改才能工作，用于示例的表数据如下
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());
通过ODBC访问数据的代码
using System;
using System.Data;
using Microsoft.Data.Odbc;
class ssqlODBC_Howto
{
[STAThread]
static void Main(string[] args)
{
// Setup a connection string
string szConnect = "DSN=dsnname;" +
"UID=seabox;" +
"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 ssqlODBC 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();
}
}
从C/C++中访问¶
需要的环境准备
-
Ch标准版或专业版
-
Ch ODBC Toolkit
-
IODBC, UNIXODBC或者Microsoft ODBC管理器
-
SeaboxSQL数据库源
如何在Ch中执行ODBC代码
你可以在Ch Shell环境中执行下面的simple.c脚本
% 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);
}
}
从VBA中访问¶
下面是一段用VBA通过ODBC驱动访问Seabox数据据库的方法。 特别使用编程的方式链接和取消链接MS Access数据库中的SeaboxSQL关系。 代码中通过注释对各个步骤进行了解释，它包含两个子例程，一个子例程链接一个新关系，另一个子例程取消链接。
如何在VBA中执行ODBC代码
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 SeaboxSQL 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, SeaboxSQL on Red Hat 7.2
'
' The connection string begins as usual, for example:
'
' "ODBC;DRIVER={SeaboxSQL};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:
'
' SD_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={SeaboxSQL};" & 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
从Visual Basic中访问¶
需要的环境准备
-
Visual Basic 6或更高版本
-
在VB项目中存在到Microsoft ActiveX数据对象的连接
-
SeaboxSQL数据库源
下面的代码可能需要根据实际环境进行适当的修改才能工作，用于示例的表数据如下
CREATE TABLE vbtest(
id int4,
data text,
accessed timestamp
);
如何在Visual Basic中执行ODBC代码
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>"
'Clear the table
cn.Execute "DELETE FROM vbtest;"
'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, accessed FROM vbtest", cn, adOpenDynamic, adLockOptimistic
'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.Requery
'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
函数调用
' 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