Interpretation of common functions of ODBC API

odbc| function
Interpretation of common functions of ODBC API
Remember Kony once in my this jar to turn a post, explain the use of ODBC data source to connect the database to the slow speed of the database operation, and the post also proposed to improve the ODBC data source connection database database operation speed of two methods, one is to use the visiual Basic in the RDO component, one is directly invoke the ODBC API functions for the relevant database operations, should have promised to write some of the little brother and sister about the ODBC API functions, so can only spot;
The following are commonly used 14 functions of the ODBC API, listing the external function reference declarations in PowerBuilder:
function integer sqlallocenv (ref long phenv) library "Odbc32.dll"
function integer sqlfreeenv (long henv) library "Odbc32.dll"
function integer sqldatasources (long henv,int fdirection,ref string szdsn,&
Integer cbdsnmax,ref integer pcbdsn,ref string szdescription,integer cbdescriptionmax,ref integer pcbdescription) Library "Odbc32.dll"
function integer sqlallocconnect (long henv,ref long hdbc) library "Odbc32.dll"
function integer SQLConnect (long hstmt,ref string Szdsn,integer dsnlen,ref string
Szuid,integer uidlen,ref string Szpwd,integer pwdlen) Library "Odbc32.dll"
function integer sqldisconnect (long hdbc) library "Odbc32.dll"
function integer sqlallocstmt (long hdbc,ref long hstmt) library "Odbc32.dll"
function integer sqltables (long hstmt,ref string Sztablequalifier,integer tablequalifierlen,ref string Szowner,integer Owerlen,ref string Szname,integer Namelen,
Ref string Sztype,integer Typelen) Library "Odbc32.dll"
function integer sqlcolumns (long hstmt,ref string Sztablequalifier,integer tablequalifierlen,ref string Szowner, Integer owerlen,ref string Szname,integer namelen,
Ref string Sztype,integer Typelen) Library "Odbc32.dll"
function integer SQLBindCol (long hstmt,integer colnum,integer datatype,ref string name,long maxlen,ref long Actlen) libra Ry "Odbc32.dll"
function integer SQLFetch (long hstmt) library "Odbc32.dll"
function integer SQLError (long lenv,long hdbc,long hstmt,ref string sqlstate,ref long nativeerror,ref string errormsg,int Eger errormsgmax,ref integer errormsglen) library "Odbc32.dll"
function integer sqlfreestmt (long hstmt,integer Options) library "Odbc32.dll"
1. function integer sqlallocenv (ref long phenv) library "Odbc32.dll"
Parameter: A long type parameter passed by a ref long phenv that holds the handle to the OBDC environment returned by the function.
The value stored in phenv becomes the unique identifier of the subsequent OBDC API function call.
Return value: When the integer succeeds, it returns 0, and the failed return value is less than 0.
function function: Gets the ODBC environment handle.
2. Function integer sqlallocstmt (long hdbc,ref long hstmt) library "Odbc32.dll"
Parameter: Long HDBC the handle of the ODBC environment.
Ref long hstmt saves the SQL statement handle.
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Gets the SQL statement handle.
3. Function integer SQLConnect (long hstmt,ref string Szdsn,integer dsnlen,ref string Szuid,integer Uidlen,
Ref string Szpwd,integer Pwdlen) Library "Odbc32.dll"
Parameters: Long hstmt, which invokes the handle obtained by the sqlallocstmt () function.
Ref string SZDSN,ODBC the data source name.
Integer Dsnlen, the length of the ODBC data source name.
Ref string szuid, user account.
Integer Uidlen, user account length.
Ref string szpwd, user password.
Ref integer pwdlen, user password length.
Return value: Integer, when successful, the return value is greater than 0, and the failure return value is less than 0.
function function: Connect the ODBC data source and return the connection handle.
4. Function integer sqldisconnect (long hdbc) library "Odbc32.dll"
Parameter: Long HDBC connection handle.
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Disconnect ODBC data source.
5. Function integer SQLTables (long hstmt,ref string Sztablequalifier,integer tablequalifierlen,ref string Szowner, Integer owerlen,ref string szname,integer namelen,ref string Sztype,integer typelen) Library "Odbc32.dll"
Parameter: Long HSTMT SQL statement handle
Ref string szTableQualifier The qualifier name of the table.
The length of the qualifier name of the integer tablequalifierlen table.
Ref string Szowner The owner name of the table.
The owner name length of the integer Owerlen table.
Ref string szname table name.
Integer namelen the table name length.
Ref string Sztype The type name of the table.
Integer Typelen The type name length of the table.
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Gets the information of the table.
6. Function integer SQLColumns (long hstmt,ref string Sztablequalifier,integer tablequalifierlen,ref string Szowner, Integer owerlen,ref string Szname,integer namelen, ref string Sztype,integer Typelen) Library "Odbc32.dll"
Parameter: Long HSTMT SQL statement handle
Ref string szTableQualifier The qualifier name of the table.
The length of the qualifier name of the integer tablequalifierlen table.
Ref string Szowner The owner name of the table.
The owner name length of the integer Owerlen table.
Ref string szname table name.
Integer namelen the table name length.
Ref string Sztype The type name of the table.
Integer Typelen The type name length of the table.
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Gets the column information for the specified table.

7. Function integer SQLBindCol (Long hstmt,integer colnum,integer datatype,ref string Name,long maxlen,
Ref long Actlen) library "Odbc32.dll"
Parameter: Long HSTMT SQL statement handle
Integer Colnum 1-5 (Qualified, owner name, type, comment).
Integer datatype 1-8,12,99.
Ref string name program string variable.
Long MaxLen variable.
Ref long Actlen variable.
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Binding result set.

8. Function integer SQLFetch (long hstmt) library "Odbc32.dll"
Parameter: Long HSTMT SQL statement handle
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Binding result set.

9, function integer SQLError (long lenv,long hdbc,long hstmt,ref string sqlstate,ref long nativeerror,ref string errormsg,i Nteger errormsgmax,ref integer errormsglen) library "Odbc32.dll"
Parameter: Long lenv ODBC Environment handle
Long HDBC Connection handle
Long HSTMT SQL statement handle
Ref string SQLState is used to accept strings containing SQL error IDs
Ref long NativeError is used to accept the inclusion of a SQL error identification code
Ref string errormsg is used to accept strings containing SQL error messages
The maximum number of characters returned by the integer Errormsgmax function
The actual number of characters returned by the ref integer Errormsglen function
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Returns an error calling an ODBC API function.
10. Function integer sqlfreeenv (long henv) library "Odbc32.dll"
Parameter: Long lenv ODBC Environment handle
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Releases the ODBC environment handle.
11. Function integer sqlfreestmt (long hstmt,integer Options) library "Odbc32.dll"
Parameter: Long HSTMT SQL statement handle.
Options related to the integer options.
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Frees the SQL statement handle.
12, function integer sqldatasources (long henv,int fdirection,ref string szdsn, integer cbdsnmax,ref integer pcbdsn,ref str ing szdescription,integer cbdescriptionmax,ref integer pcbdescription) library "Odbc32.dll"
Parameter: Long henv ODBC data source environment handle.
int fdirection Direction Flag 1 (next), 2 (first), 3 (last), 4 (previous)
Ref string SZDSN Data source Name
Integer Cbdsnmax The maximum length of a data source name
Ref integer PCBDSN The actual length of the data source name
Ref string Szdescription Data Source description name
Integer Cbdescriptionmax The maximum length of a data source description string
Ref integer pcbdescription The actual length of the data source description string
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Frees the SQL statement handle.
13. Function integer sqlallocconnect (long henv,ref long hdbc) library "Odbc32.dll"
Parameter: Long henv ODBC data source environment handle.
Ref long HDBC Saves the ODBC connection handle.
Return value: Integer, when successful, returns 0, the failure return value is less than 0.
function function: Gets the ODBC connection handle.
--The above finally describes the several ODBC API functions listed, and so have time to combine specific examples to illustrate how to use the ODBC API functions to access the database-:
Routines:
To define an instance variable:
Protected
Long Henv//sql Environment handle
Long Hstmt//sql statement handle
Long Hdbc//sql Connection handle
To define a handle external function reference:
function integer sqlallocenv (ref long phenv) library "Odbc32.dll"
function integer sqlfreeenv (long henv) library "Odbc32.dll"
function integer sqldatasources (long henv,int fdirection,ref string szdsn,&
Integer cbdsnmax,ref integer pcbdsn,ref string szdescription,integer cbdescriptionmax,ref integer pcbdescription) Library "Odbc32.dll"
function integer sqlallocconnect (long henv,ref long hdbc) library "Odbc32.dll"
function integer SQLConnect (long hstmt,ref string Szdsn,integer dsnlen,ref string
Szuid,integer uidlen,ref string Szpwd,integer pwdlen) Library "Odbc32.dll"
function integer sqldisconnect (long hdbc) library "Odbc32.dll"
function integer sqlallocstmt (long hdbc,ref long hstmt) library "Odbc32.dll"
function integer sqltables (long hstmt,ref string Sztablequalifier,integer tablequalifierlen,ref string Szowner,integer Owerlen,ref string Szname,integer Namelen,
Ref string Sztype,integer Typelen) Library "Odbc32.dll"
function integer sqlcolumns (long hstmt,ref string Sztablequalifier,integer tablequalifierlen,ref string Szowner, Integer owerlen,ref string Szname,integer namelen,
Ref string Sztype,integer Typelen) Library "Odbc32.dll"
function integer SQLBindCol (long hstmt,integer colnum,integer datatype,ref string name,long maxlen,ref long Actlen) libra Ry "Odbc32.dll"
function integer SQLFetch (long hstmt) library "Odbc32.dll"
function integer SQLError (long lenv,long hdbc,long hstmt,ref string sqlstate,ref long nativeerror,ref string errormsg,int Eger errormsgmax,ref integer errormsglen) library "Odbc32.dll"
function integer sqlfreestmt (long hstmt,integer Options) library "Odbc32.dll"
Declare a function wf_sqlerror ()
The code is as follows:
String ls_sqlstate,ls_errormsg
Integer Li_errormsgmax,li_ret
Long NativeError
Ls_errormsg=space (255)
Ls_sqlstate=space (255)
Li_ret=sqlerror (Henv,hdbc,hstmt,ls_sqlstate,li_nativeerror,ls_errormsg,255,li_errormsgmax)
MessageBox ("ODBC:" +ls_sqlstate,ls_errormsg)
Return
The Open event of the window, which writes the following code:
String ls_dsn,ls_description
Integer Li_direction,li_dsnmax,li_dsn_len
Integer Li_descriptionmax,li_description_len,li_retval
Ls_dsn=space (255)
Li_dsnmax=len (LI_DSN)
Ls_description=space (255)
Li_descriptionmax=len (ls_description)
If SQLAllocEnv (henv) =-1 Then
Wf_sqlerror ()
Else
Li_driection=1
Do While Sqldatasources (henv,li_direction,ls_dsn,li_dsnmax,li_dsn_len,ls_description,li_descriptionmax,li_ Description_len) =0
Lb_datasources.additem (ls_dsn0
Loop
End If
The Close event of the window writes the following code:
SQLDisconnect (HDBC)
Sqlfreeenv (henv)
Write the following code in the Cliked event of the Cb_connect button:
Integer Li_ret
String ls_dsn,ls_uid,ls_pwd
String ls_qualifer,ls_owner,ls_name,ls_type,ls_table
Long Ll_len
Ls_dsn=lb_datasources.selecteditem ()
Ls_uid=sle_uid.text
Ls_pwd=sle_pwd.text
Li_ret=sqlallocconnect (HENV,HDBC)
If Li_ret<0 Then
Wf_sqlerror ()
Else
Li_ret=sqlconnect (Hdbc,ls_dsn,len (LS_DSN), Ls_uid,len (Ls_uid), Ls_pwd,len (LS_PWD))
If Li_ret<0 Then
Wf_sqlerror ()
Else
Li_ret=sqlallocstmt (HDBC,HSTMT)
If Li_ret<0 Then
Wf_sqlerror ()
Else
Ls_type= "' TABLE ', ' VIEW '"
If Cbx_systemtables.checked Then
Ls_type=ls_type+ ", ' SYSTEM TABLE '"
End If
Li_ret=sqltables (Hstmt,ls_qualifier,len (Ls_qualifier), Ls_owner,len (Ls_owner), Ls_name,len (Ls_name), Ls_type,len ( Ls_type))
If Li_ret<0 Then
Wf_sqlerror ()
Else
Ls_table=space (255)
ll_len=255
Sqlbincol (hstmt,3,1,ls_table,ll_len,ll_en)
Lb_tables.setredraw (False)
Do While SQLFetch (HSTMT) =0
Lb_tables.additem (ls_table)
Loop
Lb_tables.setredraw (True)
Li_ret=sqlfreestmt (hstmt,0)
End If
End If
End If
End If
This.enabled=false
This.default=false
Cbx_systemtables.enabled=false
Write the following code in the Lb_datasources selectionchanged event:
Sle_uid.text= ""
Sle_pwd.text= ""
SQLDisconnect (HDBC)
Cbx_systemtables.enabled=true
Cb_connect.enabled=true
Lb_columns.reset ()
Lb_tables.reset ()
Sle_uid.setfocus ()
Cb_connect.default=true
Write the following code in the Lb_tables selectionchanged event:
Integer Li_ret,li_len
String Ls_qualifier,ls_owner,ls_name,ls_column,ls_colname,ls_type
Long Ll_maxlen
Lb_columns.reset ()
Ls_qualifier=space (255)
Ls_owner=space (255)
Ls_column=space (255)
Ls_colname=space (255)
Ls_type=space (255)
Ls_name=space (255)
Ls_name=this.selecteditem ()
Li_ret=sqlcolumns (Hstmt,ls_quanlifier,0,ls_owner,0,ls_name,len (ls_name), ls_column,0)
If Li_ret<0 Then
Wf_sqlerror ()
Else
Ls_colname=space (255)
Ls_type=space (255)
ll_maxlen=255
SQLBindCol (Hstmt,4,1,ls_colname,ll_maxlen,ll_maxlen)
SQLBindCol (Hstmt,6,1,ls_type,ll_maxlen,ll_maxlen)
Lb_columns.setredraw (False)
Do While SQLFetch (HSTMT) =0
Lb_columns.additem (ls_colname+ "~t" +ls_type)
Loop
Lb_columns.setredraw (True)
Li_ret=sqlfreestmt (hstmt,0)
End If
Write the following code in the clicked event of the button Cb_exit:
Close (parent)
Write the following code in the Application object's Open event:
Open (W_odbc_data_sources)