Tech Note 22: How to connect to databases (Oracle, etc)

February 04, 2005

© NS BASIC Corporation. All rights reserved.
NS Basic/Desktop
•  More Info •  Support •  Orders •  Downloads • 


NS Basic/Desktop makes it easy to connect to databases such as SQL Server, Access and Oracle. The ADODB object is designed just for this purpose. With NS Basic/Desktop's easy form design capability, it's easy and fast to create front end applications for databases.

Coupled with NS Basic's ability to deal directly with data in Excel, Word and other COM aware applications, NS Basic/Desktop can also be a powerful middleware solution.

The following deals with accessing an Oracle database. Much the same procedure is used for other databases. To see an example of an Access database, check out ADODEMO in the Samples.

The following is contributed by mizuno-ami in Japan

Connecting to databases is not difficult using NS Basic/Desktop. Using the connection method that provided by Microsoft called 'ADO', it can connect to not only Microsoft's databases (such as SQL Server, Access and ADO) but also Oracle Databases.

For example, imagine there is an Oracle database with the following connection information:

(It would be defined by the file of 'tnsnames.ora' or others as standard method.)

And imagine there is a data table that is named 'M_USERLIST' as follows:

Here I'll make the sample for tiny database read.
At first, make the program form by NS Basic/Desktop.
I designed the following form: Next, Write the following code for the command button.
It'll be a simple sample for connecting to database.
Sub CommandButton1_Click

Dim objADO
Dim objRs
Dim strSQL
Dim strUserID

Listbox1.Clear

strUserID=TextBox1.Text

'Create ADO Object
Set objADO = CreateObject("ADODB.Connection")

'Open Oracle DB by ADO
objADO.Open "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=TESTDB; UID=USER001; PWD=PASS001;"

'Set SQL
strSQL="SELECT USERID || ':' || USERNAME FROM M_USERLIST WHERE USERID LIKE '" & stUserID & "%' ORDER BY USERID"

'Set DynaSet(RecordSet) Object
Set objRS = objADO.Execute(strSQL)


'Show records data
Do Until objRS.Eof = True

    'Show the field
    Listbox1.addItem objRS(0) 

    'move next record
    objRS.MoveNext

Loop


'Close ADO
objADO.Close

End Sub


Compile and run the program, you'll get these results.



This sample worked well. I tested it at :

INSERT, UPDATE and Delete

Explanation about operation of the addition, updating, and deletion to a database is given here.

The operations of addition, updating and deletion use the 'INSERT','UPDATE' and 'DELETE' operatinos of SQL.
They use the method of 'Execute'. It is the same as 'SELECT'.
But they do not return the Recordset object, so you need prepare no variable.

As general method, the order of operating database is the following:
  1. declare to begin the Transaction
  2. do database operation (addition, updating or deletion)
  3. commit this Transaction.
For example, here is a simple addition:
(The database structure is the same as above.)
Sub CommandButton2_Click

Dim strSQL
Dim UserID
Dim UserName

'Create ADO Object by ADDOBJECT: it is the same method by using CreateObject()
AddObject "ADODB.Connection","objADO"

'Open Oracle DB by ODBC
objADO.Open "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=TESTDB; UID=USER001; PWD=PASS001;"

'Get TextBox data
UserID=TextBox2.Text
UserName=TextBox3.Text

'SQL
strSQL="INSERT INTO M_USERLIST(USERID,USERNAME) VALUES('" & UserID & "','" & UserName & "')"

'Begin Transaction
objADO.BeginTrans

objADO.Execute strSQL

'Commit Transaction
objADO.CommitTrans

'Close ADO
objADO.Close

End Sub

The method is very easy but its process is very important if some changes are given to the database.

At the first, the code which processes it is needed when an error occurs. Usually,'Rollback' is used for database when errors occur. And the order of operating database is renewed as following:
  1. declare to begin the Transaction
  2. database operations (addition, updating or deletion)
  3. If some error occur, this transaction must be canceled by using Rollback. other case, commit this transaction.
If you want to know if the error occurred, 'Errors' object will be used.
You can know the error status of ADOConnection by it.
But if the error occurred, your program is aborted immediately,so you must add 'On Error Resume Next' in your code.
It ignores the error and continues the program. 
Sub CommandButton2_Click

Dim strSQL
Dim UserID
Dim UserName

On Error Resume Next

'Create ADO Object by ADDOBJECT: it is the same method by using CreateObject()
AddObject "ADODB.Connection","objADO"

'Open Oracle DB by ODBC
objADO.Open "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=TESTDB; UID=USER001; PWD=PASS001;"

'Get TextBox data
UserID=TextBox2.Text
UserName=TextBox3.Text

'SQL
strSQL="INSERT INTO M_USERLIST(USERID,USERNAME) VALUES('" & UserID & "','" & UserName & "')"

'Begin Transaction
objADO.BeginTrans

objADO.Execute strSQL

'Check the error
If objADO.Errors.Count=0 Then
    'Commit Transaction
    objADO.CommitTrans
Else
    'Rollback Transaction
    objADO.RollbackTrans
End if

'Close ADO
objADO.Close

End Sub

The accident of database is not all errors.
For example, if you missed or lost the condition of 'WHERE', there should be only horrible results.
So it is better counting the number of affected records before 'CommitTrans'. 
Sub CommandButton2_Click

Dim strSQL
Dim UserID
Dim UserName
Dim lngRecAff

On Error Resume Next

'Create ADO Object by ADDOBJECT: it is the same method by using CreateObject()
AddObject "ADODB.Connection","objADO"

'Open Oracle DB by ODBC
objADO.Open "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=TESTDB; UID=USER001; PWD=PASS001;"

'Get TextBox data
UserID=TextBox2.Text
UserName=TextBox3.Text

'SQL
strSQL="INSERT INTO M_USERLIST(USERID,USERNAME) VALUES('" & UserID & "','" & UserName & "')"

'Begin Transaction
objADO.BeginTrans

objADO.Execute strSQL,lngRecAff

'Check the error
If objADO.Errors.Count=0 Then

    If lngRecAff=1 Then
        'Commit Transaction
        objADO.CommitTrans
    Else
        'Rollback Transaction
        objADO.RollbackTrans
    End if

Else
    'Rollback Transaction
    objADO.RollbackTrans
End if

'Close ADO
objADO.Close

End Sub

In this case, the affected record numbers will be stored to the variable of 'lngRecAff'.
In this sample (it's not that good a sample...), if 2 or more records are updated or no record is updated, it does 'Rollback'.



By using these methods, you will be able to operate the database with more safety.
AS these samples are for ADOConnection object, you will be able to use them for not only Oracle but also SQLServer or Access.