Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 154046 - Last Review: July 13, 2004 - Revision: 3.1
How To Use the ODBC API Function SQLExecDirect with RDO
This article was previously published under Q154046
Because RDO exposes the hEnv, hDbc, and hStmt properties of the ODBC API to
the programmer, you are able to utilize ODBC API calls in conjunction with
RDO. This can be helpful if RDO does not implement a feature of the ODBC
API that you would like to use.
This article gives an example of how to use the ODBC API SQLExecDirect
function in combination with the RDO rdoConnection.hDbc property to execute
SQL without the use of the rdoConnection.Execute method.
Programming directly to the ODBC API gives you an alternative to using RDO
and DAO code to access your ODBC database. This direct method allows the
programmer to have low-level control over the ODBC datasource, but
introduces the Visual Basic 4.0 programmer to a complex programming model
that requires in-depth knowledge of the ODBC 2.0 specification.
If you feel that you can benefit from this direct approach, before you
proceed you should obtain a copy of the ODBC 2.0 Programmer's Reference and
SDK Guide that fully documents the ODBC API.
Sample Program
This example uses the ODBC API SQLExecDirect function in combination with
the rdoConnection.hDbc property to execute SQL without the use of the RDO
Execute method. It uses SQL Server and the Pubs database, but you can
change the DSN and SQL to match your ODBC database.
- Start a new project in Visual Basic. Form1 is created by default.
- Add a Command button, Command1, to Form1.
- Paste the following code into the General Declarations section of Form1:
Option Explicit
Private Declare Function SQLAllocStmt Lib "odbc32.dll" _
(ByVal hdbc&, phstmt&) As Integer
Private Declare Function SQLExecDirect Lib "odbc32.dll" _
(ByVal hstmt&, ByVal szSqlStr$, ByVal cbSqlStr&) As Integer
Private Sub Command1_Click()
Dim cn As rdoConnection
Set cn = rdoEnvironments(0).OpenConnection( _
dsName:="MyServer", _
Prompt:=rdDriverNoPrompt, _
ReadOnly:=False) 'get hDbc
Dim rv As Integer 'return value
Dim lhStmt As Long 'hStmt
Dim szSql As String 'SQL string
szSql = "Select * into newtable from titles" 'create table SQL
rv = SQLAllocStmt(cn.hdbc, lhStmt) 'allocate a
statement
Debug.Print GetRetCodeText(rv) 'print return value
'text
rv = SQLExecDirect(lhStmt, szSql, Len(szSql)) 'execute the SQL
Debug.Print GetRetCodeText(rv) 'print return value
'text
End Sub
Private Function GetRetCodeText(retcode As Integer)
'get the return value text using ODBC32.TXT constants
Dim x As String
Select Case retcode
Case -1: x = "SQL_ERROR"
Case -2: x = "SQL_INVALID_HANDLE"
Case 100: x = "SQL_NO_DATA_FOUND"
Case 0: x = "SQL_SUCCESS"
Case 1: x = "SQL_SUCCESS_WITH_INFO"
End Select
GetRetCodeText = x
End Function
NOTE: You will need to change the DSN to match yours before continuing.
- Start the program or press the F5 key.
- Click the Command1 button to execute the RDO and ODBC API code. If it
executes successfully, SQL_SUCCESS will be output twice to the debug
window and you will have a new table named newtable.
Building Client/Server applications with Visual Basic Manual, Chapter 10,
"Using the ODBC API."
ODBC 2.0 Programmer's Reference and SDK Guide, MS Press.
Hitchhiker's Guide to Visual Basic and SQL Server, Microsoft Press,
ISBN: 1-55615-906-4.
APPLIES TO
- Microsoft Visual Basic 5.0 Control Creation Edition
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please