CN103646047A - Method for VB to quickly access SQL database - Google Patents

Abstract

The invention provides a method for VB to quickly access an SQL database. Aiming at using the VB to access the SQL database, an ODBC API function is called in a VB application program to carry out data grouping binding, so that system overhead and network transmission overhead caused by data conversion are reduced when the application program accesses the SQL database, and the application program can access the SQL database at a high speed.

Description

The method of VB fast access SQL database

A kind of method of VB fast access SQL database

The present invention is directed to and use this particular problem of VB access SQL database, in VB application program, call ODBCAPI function, carry out data and become group binding, when minimizing application program is visited vexed SQL database, system overhead and the Internet Transmission expense of data-switching, make application program high speed access SQL database.

For solving the problems of the technologies described above, technical scheme of the present invention is:

Because some parameter in the suction parameter table of ODBC api function need to be transmitted pointer, and VB supporting pointer not, therefore in VB, calling ODBC api function is subject to certain limitation.The string memory storage format of VB is different from C language, and ODBC api function C language development, this has also limited the exchanges data between VB and ODBC api function.Author analyzes ODBC api function, and the feature of comprehensive VB, writes a dynamic link library, can directly in VB, call ODBC api function, and can carry out exchanges data under the support of dynamic link library.

1, VC++6.0 exploitation dynamic link library

VC++6.0 can develop the dynamic link library of three types: Win32 DLL, conventional MFC DLL, expansion MFC DLL.Expansion MFC DLL supports C++ interface.In other words, DLL can derive whole class, and client can set up these classes or by the object of the class of its derivation.Win32DLL, conventional MFC DLL can be by Win32 programmed environment (comprising Visual Basic 6.0 editions) loading uses arbitrarily.Conventional MFC DLL must attach MFC42.DLL storehouse when distribution, and Win32DLL can issue separately.If only offer VB programmed environment, use, can set up Win32 DLL.

2, set up Win32 DLL

Utilize the AppWizard of VC++6.0 programmed environment to create a simple Win32 DLL engineering, engineering is called VBSQL, adds on this basis the code of oneself.Code is as follows:

#include“stdafx.h”

#include<string.h>

#define DLLEXPORT extern " C " _ declspec (dllexport) // definition is derived grand

BOOL APIENTRY DllMain(HANDLE hModule,DWORD ul_reason_for_call,

LPVOID lpReserved)

{ switch(ul_reason_for_call)

{

case DLL_PROCESS_ATTACH:

case DLL_THREAD_ATTACH:

case DLL_THREAD_DETACH:

case DLL_PROCESS_DETACH:

break；

}

return TURE；

}

DLLEXPORT long_stdcall GetAddress (void*Address) // transmit by address

{ return (long) Address}; // long shaping is forced to be converted in address

DLLEXPORT long_stdcall strCopyToByte(char*BytesAddress, char*strAddress)

{

return(long)strcpy(BytesAddress, strAddress)

; // character string is sent to assigned address

Through compiling link, generate VBSQLDLL dynamic link library, copied to C: WINDOWS under system directory, so just can in VB, call.Utilize Microsoft Visual Studio 6.0 tool software Depends to check the function name deriving in VBSQLDLL.

3, in VB, state external function

In module file, do following statement:

VBSQL.DLL function declaration:

Public Declare GetAddress Lib“VBSQL.DLL” Alias “_GetAddress@4” (ByRefAddress as Any) as Long

Public Declare strCopyToByte Lib“VBSQL.DLL” Alias “_strCopyToByte@8” (ByRefByte ArrayAdd ress as Any) as Long

With reference to sql.h, constant and function declaration in sqltypes.h header file, transfer its content to VB assertion format.Due to ODBCAPI function and constant more, at this, can not enumerate.

Type identification constant:

Public Const SQL_IS_POINTER=(-4)

Public Const SQL_IS_UINTEGER=(-5)

SQL data type constant

Public Const SQL_CHAR=1

Public Const SQL_FLOAT=6

SQL C type constant

Public Const SQL_C_CHAR=SQL_CHAR

Public Const SQL_C_FLOAT=SQL_REAL

Function declaration

Public Declare Function SQLSetStrntAttr Lib“odbe32.dll”

(ByVal StatementHandle As Long, ByVal Attributes As Long, ByVal ValuePtr As Long. ByVal StringLength As Long)As Integer

Public Declare Function SQLBindParame Lib“odbe32.dll”

(ByVal StatementHandle As Long, ByVal ParameterNumber As Integer, ByVal Inputoutput Type As Integer, ByVal ValueType As Integer, ByVal ParameterNumber As Integer, ByVal ColumnSize As Long, ByVal DecimalDigits As Integer, ByRef Parameter ValuePtr As Any, ByVal BufferLength As Long, ByRef StrLen_or_IndPtr As Any)As Intrger

Public Declare Function SQLExecDirect Lib “odbc32.dll”(ByVal StatementHandle As Long, ByVal StatementText As String, ByVal TextLength As long)As Long

Public Declare Function SQLEXecute Lib “odbc32.dll”(ByVal StatementHandle As long)As Long

The wherein variable pass by value after ByVal keyword, the variable after ByRef keyword transmits by address.

4, VB calls external function

After statement, in VB, can as calling other VB functions, call these functions.In the parameter list of attention ODBC api function, some variable is a VOID type pointer, by sign constant, identifies.As:

The 3rd parameter (SQLPOINTER Value) of SQLSetStmtAttr function he can transmit numerical value and also can transmit address, by the 4th parameter (SQLINTEGER StringLength), identify, be (ValuePtr As Long) in VB.

Transmit numerical value: Call SQLSetStmtAttr (statement handles, attribute, property value, SQL_IS_UINTEGER)

Transmit address: Call SQLSetStmtAttr (statement handles, attribute, GetAddress (variable), 0) GetAddress gets address by as above regular, directly calls ODBC api function in VB.

Compared with prior art, the invention has the beneficial effects as follows:

1), in the actual debugging of native system, with about time that needs 1 second of 720 data of ADO renewal.Utilize method herein, in VB, call ODBC api function, and carry out burst data binding, upgrade same data and be about 0.1 second, greatly accelerate the speed of access SQL.

2), " Subsystem in Marine Engine Simulator based on virtual reality " is the distributing emulation system centered by SQL database.By l platform sql database server, 14 estrade system emulation computing machines, (client computer, switchboard industrial computer, machine control platform industrial computer, trainer's computing machine (monitoring equipment) and 15 student's computing machines form Fast Ethernet by the 100M network switch to native system.Subsystem need be uploaded real-time simulation data high-speed to database, and other subsystem and machine for student also need the required emulated data of high-speed downloads.Therefore how to accelerate accessing database, reduce network traffics, each subsystem real time high-speed exchange emulated data is that this distributing emulation system is successfully crucial.This invention is achieved the fast access between each system of simulator.

Claims (1)

1. because some parameter in the suction parameter table of ODBC api function need to be transmitted pointer, and VB supporting pointer not, therefore in VB, calling ODBC api function is subject to certain limitation; The string memory storage format of VB is different from C language, and ODBC api function C language development, this has also limited the exchanges data between VB and ODBC api function; Author analyzes ODBC api function, and the feature of comprehensive VB, writes a dynamic link library, can directly in VB, call ODBC api function, and can carry out exchanges data under the support of dynamic link library;

VC++6.0 develops dynamic link library

VC++6.0 can develop the dynamic link library of three types: Win32 DLL, conventional MFC DLL, expansion MFC DLL;

Expansion MFC DLL supports C++ interface;

In other words, DLL can derive whole class, and client can set up these classes or by the object of the class of its derivation; Win32DLL, conventional MFC DLL can be by Win32 programmed environment (comprising Visual Basic 6.0 editions) loading uses arbitrarily; Conventional MFC DLL must attach MFC42.DLL storehouse when distribution, and Win32DLL can issue separately; If only offer VB programmed environment, use, can set up Win32 DLL;

Set up Win32 DLL

Utilize the AppWizard of VC++6.0 programmed environment to create a simple Win32 DLL engineering, engineering is called VBSQL, adds on this basis the code of oneself; Code is as follows:

#include“stdafx.h”

#include<string.h>

#define DLLEXPORT extern " C " _ declspec (dllexport) // definition is derived grand

BOOL APIENTRY DllMain(HANDLE hModule,DWORD ul_reason_for_call,

LPVOID lpReserved)

{ switch(ul_reason_for_call)

{

case DLL_PROCESS_ATTACH:

case DLL_THREAD_ATTACH:

case DLL_THREAD_DETACH:

case DLL_PROCESS_DETACH:

break；

}

return TURE；

}

DLLEXPORT long_stdcall GetAddress (void*Address) // transmit by address

{ return (long) Address}; // long shaping is forced to be converted in address

DLLEXPORT long_stdcall strCopyToByte(char*BytesAddress, char*strAddress)

{

return(long)strcpy(BytesAddress, strAddress)

; // character string is sent to assigned address

Through compiling link, generate VBSQLDLL dynamic link library, copied to C: WINDOWS under system directory, so just can in VB, call; Utilize Microsoft Visual Studio 6.0 tool software Depends to check the function name deriving in VBSQLDLL;

In VB, state external function

In module file, do following statement:

VBSQL.DLL function declaration:

Public Declare GetAddress Lib“VBSQL.DLL” Alias “_GetAddress@4” (ByRefAddress as Any) as Long

Public Declare strCopyToByte Lib“VBSQL.DLL” Alias “_strCopyToByte@8” (ByRefByte ArrayAdd ress as Any) as Long

With reference to sql.h, constant and function declaration in sqltypes.h header file, transfer its content to VB assertion format;

Due to ODBCAPI function and constant more, at this, can not enumerate;

Type identification constant:

Public Const SQL_IS_POINTER=(-4)

Public Const SQL_IS_UINTEGER=(-5)

SQL data type constant

Public Const SQL_CHAR=1

Public Const SQL_FLOAT=6

SQL C type constant

Public Const SQL_C_CHAR=SQL_CHAR

Public Const SQL_C_FLOAT=SQL_REAL

Function declaration

Public Declare Function SQLSetStrntAttr Lib“odbe32.dll”

(ByVal StatementHandle As Long, ByVal Attributes As Long, ByVal ValuePtr As Long. ByVal StringLength As Long)As Integer

Public Declare Function SQLBindParame Lib“odbe32.dll”

(ByVal StatementHandle As Long, ByVal ParameterNumber As Integer, ByVal Inputoutput Type As Integer, ByVal ValueType As Integer, ByVal ParameterNumber As Integer, ByVal ColumnSize As Long, ByVal DecimalDigits As Integer, ByRef Parameter ValuePtr As Any, ByVal BufferLength As Long, ByRef StrLen_or_IndPtr As Any)As Intrger

Public Declare Function SQLExecDirect Lib “odbc32.dll”(ByVal StatementHandle As Long, ByVal StatementText As String, ByVal TextLength As long)As Long

Public Declare Function SQLEXecute Lib “odbc32.dll”(ByVal StatementHandle As long)As Long

The wherein variable pass by value after ByVal keyword, the variable after ByRef keyword transmits by address;

VB calls external function

After statement, in VB, can as calling other VB functions, call these functions; In the parameter list of attention ODBC api function, some variable is a VOID type pointer, by sign constant, identifies; As:

The 3rd parameter (SQLPOINTER Value) of SQLSetStmtAttr function he can transmit numerical value and also can transmit address, by the 4th parameter (SQLINTEGER StringLength), identify, be (ValuePtr As Long) in VB;

Transmit numerical value: Call SQLSetStmtAttr (statement handles, attribute, property value, SQL_IS_UINTEGER)

Transmit address: Call SQLSetStmtAttr (statement handles, attribute, GetAddress (variable), 0) GetAddress gets address by as above regular, directly calls ODBC api function in VB.