======================================================================
Using Visual Basic Data Controls with the SQL Engine 2.0
======================================================================
Introducing the R:BASE SQL Engine 2.0
With the release of the R:BASE SQL Engine 2.0, programming R:BASE
applications in a Windows environment using Visual Basic is easier
than ever. The new release of the SQL Engine is ODBC Level 1
compliant and supports Visual Basic's data control. The data control
makes it possible to create Visual Basic applications that connect to
an R:BASE database and retrieve records without programming. The data
control performs the following functions:
Connects an R:BASE database
Retrieves a group of records based on any valid SQL query
Passes data to bound controls for display or update
Facilitates built-in error checking
Disconnects from the database on exit
In the past, these tasks required programming code. The data control
eliminates much of the code and therefore reduces development time
significantly. Accessing R:BASE is very straightforward - you can
spend time developing the look of your application rather than writing
the code. You need the Professional edition of Visual Basic 3.0, since
the Standard edition does not have the data control.
This article demonstrates techniques for using the data control and
shows forms you can create with little or no Visual Basic code.
Set Up the ODBC Administrator
In order to use the data control, the data source (the R:BASE database
name), must be added through the ODBC Administrator. The ODBC
Administrator icon was installed into the SQL Engine program group.
Start the ODBC Administrator and select Add. Choose the driver named
"R:BASE45_16". Then enter the database name as prompted. Be sure to
specify the complete drive and path name for the database; for
example:
C:\rbsqlapi\samples\dbs\contact
The data source (the R:BASE database name) is added to the file
ODBC.INI. During installation of the SQL Engine, the driver
information was added to the ODBCINST.INI file. Refer to page 3-7 of
the SQL Engine User's Manual for more information about setting up
ODBC.
Define Properties for the Visual Basic Data Control
The data control is the Visual Basic object that connects to your
R:BASE database and identifies the data to retrieve. Text boxes are
the objects that hold the column data - one text box per column. The
properties of the text box refer back to the data control. A form can
have one or more data controls. You can even connect multiple R:BASE
databases by using different data controls.
Place a data control object on your form by double-clicking on the
data control icon on the tool bar. The database and table are set
through data control properties. To display the properties for a data
control, click on the data control, then choose Windows|Properties
from the Visual Basic menu. Or, if the Properties window is already
displayed, select it.
You must set three properties for the data control: Connect, Options
and RecordSource. These properties can be set at design time or
runtime. However, if they are not set at design time, some
programming code needs to be written. Refer to the Visual Basic
reference manuals for examples. The DatabaseName property is left
blank; it is not applicable to connecting an R:BASE database.
The Connect Property
The Connect property passes information to the Engine that is
necessary for connecting to the database, which is connected through
ODBC. The following arguments can be included in the Connect
property:
ODBC - Indicates that the database is an ODBC data source
DSN - The path and name of the database
UID - The userid of the person using the database
PWD - The userid password, if applicable
For example:
ODBC;DSN=c:\rbsqlapi\samples\dbs\contact;UID=none;PWD=none
Note that each argument is separated by a semi-colon (;). The only
argument that is required is ODBC;. Any of the remaining arguments
that are not specified at design time can be explicitly set during
run time. The arguments that are not set at design time or run time
will be prompted for at run time.
When you enter just "ODBC;" in the Connect property, the menu of
installed Data Sources displays and you can pick the database name.
When the userid/password box displays, enter a userid if applicable.
To set up your application so that it will not prompt but
automatically connect to a database, include all parameters as part
of the Connect property. If no userid or password is defined, enter
"NONE".
Since the RecordSource property specifies a table name, you should
include at least the database name in the Connect property. That
makes sure your users don't select the wrong database from the ODBC
Data Sources list. If you have no passwords assigned on the database,
include the UID and PWD arguments as well so that your users won't
be prompted at all.
The RecordSource Property
The RecordSource property defines a group of records from a table.
Valid record sources consist of the name of a database table or the
text of a SQL query. One of the nice features about the RecordSource
property is that it can be dynamically changed while an application is
running. This, of course, takes a couple of lines of code, but is
fairly straightforward.
The easiest way to set up the RecordSource is to simply enter a table
name. Alternatively, you can use a SELECT command. For example:
SELECT * FROM employees
You can use any SELECT command that you can enter at the R>, although
some require use of the SQL pass-through that is set with the Options
property. For complex, multi-table queries, it is better to create a
view in R:BASE and then select from the view in the RecordSource
property. A view must be used with the SQL pass-through.
The Options Property
The Options property sets properties for the data (recordset)
retrieved by the data control. The default value for the Options
property is 0. This value allows the recordset to be modified. Make
sure the Options property is set to 0 when designing an application
where users will insert new rows, delete rows, or edit data.
Setting the Options property to 64 enables SQL pass-through
(DB_SQLPASSTHROUGH). The SQL command is passed directly to the
underlying database engine for processing; Visual Basic does not try
to process or interpret the command. Using the SQL pass-through allows
you to build more complex SELECT commands, use R:BASE SuperMath
functions and even use R:BASE command abbreviations (such as SEL
instead of SELECT). Accessing data from an R:BASE view requires the
Options property be set to 64. The recordset cannot be modified when
the Options property is set to 64.
Bound Controls
Bound controls are Visual Basic objects that can receive information
passed to them via the data control. Bound control means that the
object is connected to another object. If the data displayed in these
objects is changed, the table is automatically updated when a new
record is selected (when Options property of the data control is set
to 0). This makes it possible to design forms that view and edit data
without program code. A text box is the bound control that is
generally used with a data control. The properties that must be set
for the text box are the DataField and the DataSource. Unlike the
properties of the data control, these properties must be set at
design time.
DataSource Property
The DataSource property contains the name of the data control
supplying the data. Specifying the DataSource binds the text box to
the data control. If the data control has been placed on the form,
double-clicking on the DataSource property will automatically fill in
a valid data control name. If more than one data control exists,
successively double-clicking will display additional data controls
associated with the form.
DataField Property
The DataField property contains the name of one of the columns
returned by the table specified in the RecordSource property of the
data control. Each bound object (text box) can contain only one field
from the record source. If the DataSource property is filled in,
double-clicking on the DataField produces a list of columns from the
table and database specified by the data control.
Example 1: A Multi-Table Form with a Two-Table Select
This first example is a multi-table form based on the customer and
contact tables in the Concomp sample database. This form contains
one data control and is easy to create with a multi-table SQL query
using SQL pass-through (Options=64). The best part about this example
is that it requires absolutely no programming code. Just follow the
steps below.
1. Begin Visual Basic and start with a new form.
2. Place a data control object on the form and define the properties
as follows:
Connect ODBC;DSN=C:\rbfiles\Concomp;UID="none";PWD="none"
Options 64 (Numeric option specifying DB_SQLPASSTHROUGH)
RecordSource SEL t1.custid, t1.company, t1.custaddress, +
t1.custcity,t1.custstate, t1.custzip, t1.custphone, +
(t2.contlname + ',' & t2.contfname), t2.contphone, t2.continfo FROM +
customer t1, contact t2 WHERE t1.custid = t2.custid
Notice the RecordSource property looks just like the SELECT command
you enter at the R> prompt in R:BASE, including command abbreviations,
expressions, and correlation names.
3. Add text boxes for each field returned by the query and adjust the
properties as follows:
DataSource Data1 (the Name of the data control)
DataField one of the fields returned by the query, for
example custid.
The DataField property indicates which field displays in the located
text box. Locate a text box and specify the appropriate DataField
property for each column selected in the RecordSource. When done,
your form should look similar to figure 1.
That's all there is to it. Run the form and test it out by scrolling
through the records. Because SQL pass-through was used, the data is
not editable.
Example 2: A Multi-Table Form that Links Two Data Controls
The first form example was fairly limited. In order to change to a new
customer and view the contacts associated with it, you must scroll
through all of the records. This second form, on the other hand,
allows the user to change to a different customer and view all
contacts associated with that customer. This second example is more
complex in that it requires two data controls to be set and a few
lines of program code. As each new company is selected by clicking on
the data control arrows, a new value for the RecordSource property is
set by using the Visual Basic REFRESH method. When the user clicks on
the data control associated with the customer table, the text box
containing the customer id number, custid, changes. To capture the new
id and retrieve the matching records from the contact table, enter
the following code as a change procedure for the custid text box. This
code executes whenever the custid value changes.
'Capture the new value of custid for the next record in the customer
table
Vcustid$ = Text1.text
'Redefine the RecordSource for Data2 using the new value of the
custid
Data2.Recordsource = "SEL * FROM contact WHERE custid = " + Vcustid$
'Create the new recordset
Data2.Refresh
1. Set up the form as in figure n and add the above code to the Change
action on the text box for custid
2. Define the RecordSource and Options for each data control:
Data1.RecordSource = customer
Data1.Options = 0
Data2.RecordSource = SEL (contlname + "," & contfname), contphone,
continfo FROM contact
Data1.Options = 64
The data in the Customer table can be modified; the data in the
Contact table is read only.
Example 3: A Visual Basic Gateway
For many years, users of R:BASE have wanted to be able to
simultaneously connect to two R:BASE databases and be able to transfer
data between them, but there was no way to connect to two databases at
the same time. The Visual Basic data control now makes this easy to
do.
This third example uses a data control to connect to one database and
custom code to connect to a second database. Using the data control, a
record in the source database is selected and subsequently inserted
into a destination table of the same name in a second database. Since
this form uses SQL Engine function calls, the HEADER.BAS and
SQLAPI.BAS files that come with the SQL Engine must be included in the
project. For information about the SQL Engine functions, refer to your
SQL Engine documentation. Follow the steps below to set up the form.
Set Up The Source Database
1. Starting with a new form, add the SQLAPI.BAS and HEADER.BAS files
to the project.
2. Set up a data control and edit the properties as described above in
Examples 1 and 2 to specify the database and table.
3. Set up the text boxes and edit the properties to bind the text
boxes to columns returned by the RecordSource.
Set Up The Destination Database
1. Add text boxes for each field that will be transferred to the
destination database. These text boxes are not associated with a data
control, so no special properties need to be set.
2. Add command buttons to connect the database, insert the row and
then disconnect the database. Add the associated code as shown below.
Code For Form Load action
Panel3D1.Caption = "C:\rbfiles\concomp OPEN"
Command2.Enabled = False
Command3.Enabled = False
Code for Connect command button
Sub Command1_Click ()
Mousepointer = 11
' First allocate an environment handle
retcode = SQLAllocEnv(phenv&)
If retcode <> SQLSuccess Then
MsgBox "SQLAllocEnv Failed"
End If
'Next allocate a connection handle
retcode = SQLAllocConnect(phenv&, hdbc&)
If retcode <> SQLSuccess Then
MsgBox "SQLAllocConnect Failed"
End If
'Get the name of the database and create the connect string
szDSN$ = InputBox$("Enter database name", "Connect Database",
"c:\rbfiles\transfer\concomp2")
szUID$ = "NONE"
cbUID% = Len(szUID$)
szAuthStr$ = "NONE"
cbAuthStr% = Len(szAuthStr$)
cbDSN% = Len(szDSN$)
Mousepointer = 0
'Connect the database
retcode = SQLConnect(hdbc&, szDSN$, cbDSN%, szUID$, cbUID%,
szAuthStr$, cbAuthStr%)
If retcode <> SQLSuccess Then
retcode = SQLError(phenv&, hdbc&, phstmt&, szSqlState$,
pfNativeError&, szErrorMsg$, cbErrorMsgMax%, pcbErrorMsg%)
Else
Command1.Enabled = False
Command2.Enabled = True
Command3.Enabled = True
Panel3D2.Caption = "c:\rbfiles\transfer\concomp2 OPEN"
End If
Mousepointer = 0
End Sub
Code For Insert command button
Sub Command2_Click ()
'If user selects "Insert Row", the values are displayed in the
'destination database section of the form
Text5.Text = Text1.Text
Text6.Text = Text2.Text
Text7.Text = Text3.Text
Text8.Text = Text4.Text
'Build the Values list used in the INSERT command. The current
'record in the source database is placed into variables
vcustid$ = "(" + Text1.Text + ","
vcontlname$ = Text2.Text + ","
vcontfname$ = Text3.Text + ","
vcontphone$ = Text4.Text + ")"
vresp% = MsgBox("Insert new row...are you sure?", 4)
If vresp% = 6 Then
'Allocate the statement handle
retcode = SQLAllocStmt(hdbc&, phstmt&)
If retcode <> SQLSuccess Then
MsgBox "SQLAllocStmt Failed"
Else
'Build the command
szSqlStr$ = "INSERT INTO CONTACT (custid, contlname, contfname,
contphone) VALUES " + vcustid$ + vcontlname$ + vcontfname$ +
vcontphone$ cbSqlStr& = Len(szSqlStr$)
'Insert the row
retcode = SQLExecDirect(phstmt&, szSqlStr$, cbSqlStr&)
If retcode <> SQLSuccess Then
MsgBox "INSERT Failed", 48
retcode = SQLError(phenv&, hdbc&, phstmt&, szSqlState$,
pfNativeError&, szErrorMsg$, cbErrorMsgMax%, pcbErrorMsg%)
Else
MsgBox "Successful Insert of 1 Row", 64
End If
'Free the statement handle
retcode = SQLFreeStmt(phstmt&, SQL_DROP)
If retcode <> SQLSuccess Then
MsgBox "SQLFreeStmt Failed"
Else
Text5.Text = ""
Text6.Text = ""
Text7.Text = ""
Text8.Text = ""
End If
End If
End If
End Sub
Code For Disconnect command button
Sub Command3_Click ()
retcode = SQLDisconnect(hdbc&)
If retcode <> SQLSuccess Then
MsgBox "SQLDisConnect Failed"
End If
retcode = SQLFreeConnect(hdbc&)
If retcode <> SQLSuccess Then
MsgBox "SQLFreeConnect Failed"
Else
Command1.Enabled = True
Command2.Enabled = False
Command3.Enabled = False
Panel3D2.Caption = "c:\rbfiles\transfer\concomp2 CLOSED"
End If
End Sub
3. Add error checking as necessary. The SQL Engine sample application
has an error checking sub-routine you can use as a template.
At runtime, the data control builds a recordset and binds the columns
to the text boxes. Code connects to the second database when the "Open
DB" command button is clicked. When the "Insert Row" command button is
clicked, values are transferred to the destination text boxes. Based
on these values, an SQLExecDirect command string is built that inserts
the row into the destination database. Of course, many different
variations of this form may be used to transfer data from a source
database to a destination database. Evaluate your business situation
and change the form and code accordingly.
Review the R:BASE SQL Engine 2.0 README.TXT file for additional
information on using Visual Basic data controls.