This site has been taken over by the staff of www.ASPDeveloper.Net
Please report errors to suggest@aspdeveloper.net
|
|
Why Oracle?35% of the VB jobs advertised is reason enough. But there's more! Ø Oracle is the No 1 purveyor of RDB. At least 40% of the Fortune 100 use it more than any other brand. Ø Oracle is available on every shade of OS. NT, Unix and Mainframe. Ø This means SCALABILITY to the utmost Ø It is the fastest DB on most (some say every one) of these platforms. Ø It is a most reliable RDB Why VB?We're VB programmers and we love it! Connecting to OracleNow that we know why we're here, let us get into the nitty-gritty details. First how do we connect to an Oracle database?
As you can see in the diagram, this presentation concentrates on just three of the available methods, namely: ADO through ODBC, ADO through the OLE DB, and Oracle Objects For OLE. OLE DBMany of my clients use ADO to talk to their databases. They claim that they enjoy greater flexibility with ADO. Should they ever want to switch from Oracle, they will have less code to convert. The relative ease of conversion is a pipe dream, but it is true that we'll convert our VB pieces somewhat faster using ADO. We can use ODBC or OLE DB as the ADO conduits. The difference between the way we connect using either of them is minute. The speed of the connection is a different matter altogether. Going through OLE DB will cut the connect time in half. Public Sub OpenConnection(sDB As String, sUser As String, sPW As String)
msConnect = "Provider=MSDAORA.1;" & "Password=" & _ sPW & ";User ID=" & sUser & ";Data Source=" & _ sDB & "; Persist Security Info=True"
Set mADOConnection = New ADODB.Connection 'Let's instantiate the object! With mADOConnection .ConnectionString = msConnect .ConnectionTimeout = 10 .CursorLocation = adUseNone .Open End With End Sub The code that you see here highlights the connect-string for OLE DB. It is actually a part of the 'OpenConnection' public method in my homegrown connection class. Let's see how the same lines differ for ODBC. Public Sub OpenConnection(sDSN As String, sUser As String, sPW As String)
msConnect = "Data Source=" & msDSN & ";User ID=" & _ msUser & ";Password=" & msPW & ";"
Set mADOConnection = New ADODB.Connection 'Let's instantiate the object! With mADOConnection .ConnectionString = msConnect .ConnectionTimeout = 10 .CursorLocation = adUseNone .Open End With End Sub ODBCJust a few weeks ago, I was asked to write a batch program for a client. They receive membership information from clients and load it into an online database. They wanted the job done using ADO and ODBC. We had one little problem. At first the program was loaded on one desk and I've added a system DSN to that desk to match the system DSN in my connect string. The client later decided to install the program, on the fly, on many other desks. Loading the program was easy, they simply copied it from the network drive, but we still had to create the new system DSN on each desk. Many of the desks had pre-existing system DSNs, only they used a variety of different names. I solved the problem by creating a VB Class that finds the Oracle system DSN in the Registry, and uses it in the connection. Now it is much easier to distribute the program. Here is how the class is used. Private cConnection As clsConnection 'We're interested only in the OpenConnection Method Private cBlob As clsBlob 'just another data class Private Sub cmdOK_Click() Dim sDSN As String Dim cDSN As New clsDSN Set cConnection = New clsConnection sDSN = cDSN.FindDSN(DBName) If sDSN = "DB Name not found!!" Then MsgBox "You don't have a System DSN to the requested Database. Please call the helpdesk!" End End If cConnection.OpenConnection sDSN, sUser, sPW Set cBlob = New clsBlob cBlob.Connect cConnection End Sub I've written a sample program as a demonstration of this named DSN.vbp (and frmDSN.frm). I have since used this object and method on the site of a second client. A copy of the code is included on the Conference CD. The last method of connecting to Oracle is using 0040. We'll cover this method in a few minutes as a part of a general introduction to 0040. But first let's see some of the features unique to the Oracle database. Oracle – The Data Dictionary, DUAL, and Rownum = 1Oracle contains one general-purpose dummy table – DUAL. You use DUAL as the dummy table when you want to get information such as SYSDATE, which is the Date and Time, in the Oracle Server (as opposed to the Network server or your personal computer). During the presentation, I demonstrate running the next two SQL statements in SQL*PLUS while explaining how they work. However, this and other demonstrations require the Oracle Engine that can't be distributed. SELECT SYSDATE FROM DUAL; You may also use DUAL to get sequence information and to advance a sequence to its next value. SELECT Sequence_Name NEXTVAL AS BIG_NUMBER FROM DUAL; Will advance the sequence by its increment and return the result in the ALIAS BIG_NUMBER. Using ROWNUM = 1 is another trick. When I use an ADO or 0040 Resultset (dynaset) only to add rows to a table, I do the Rownum trick. Every row in every table has an implied Rownum. It is 1 for the first row in the table, 2 for the next and so on. Because a recordset is defined by its select statement, and because we really don't care which row we get before we add a new one, we use ROWNUM = 1 as the selection criterion. This, BTW, gives us an opportunity to see that ADO with Oracle looks almost like ADO with anything else. Set objConnection = New ADODB.Connection
objConnection.Open sDSN, sUSER_ID, sPASSWORD
sSQL = "Select Column1, " & _ "Column2, " & _ "Column3 " & _ "FROM Table1 " & _ "WHERE rownum = 1"
Set objRecordset = New ADODB.Recordset objRecordset.Open sSQL, objConnection, adOpenDynamic, adLockOptimistic objRecordset.AddNew objRecordset("Column1") = NewValue1 objRecordset("Column2") = NewValue2 objRecordset("Column3") = NewValue3 objRecordset.Update Finally let's inspect the Oracle Data dictionary. The Oracle SQL*PLUS window can be difficult, at times. You may try running, for example, a long query - this results in a flurry of activity with lines flying across the screen - it's difficult to see much of what's happening. I prefer a more controllable environment, where editing is easier and we enjoy better control of the screen. One cannot avoid SQL*PLUS altogether and most of the time it is pleasant enough. It is, however, too unforgiving. It was built for a printing terminal such as a Teletype machine and it is difficult to cut, paste and edit on the line itself. I keep my 'scripts' in '.txt' files where I edit and then just paste the results into the Oracle command line. I also built a very simple tool using VB and the Oracle Data Control. The very same query now looks quite different. See the code example oraView.vbp (and frmView.frm + the required frmView.frx)
Voila! The program is made of a single form and a few lines of code. Most of these are just adding items to a listbox. We'll get back to this program when we discuss the ODC. For now, let's see how we can learn about Oracle's Data Dictionary using this program. The Oracle data dictionary is a special set of tables that reside in the database itself. These are read only tables. These tables get updated by the system whenever the tables, sequences, etc. are changed The DBA can change them at will, but a smart one does not! Users can only see the objects in their own schema. Finally, here they are: Ø USER_OBJECTS - where all the objects are listed and described Ø USER_TABLES - where the tables are described Ø USER_TAB_COLUMNS - where the columns are described Ø USER_SOURCE - where code for stored procedures is held Ø USER_SEQUENCES - where all the Sequences are described Ø A sequence is a persistent counter that is “upped” every time it is used. It is usually used for creating unique keys. Ø USER_ROLE_PRIVS - Oracle allows the DBA to assign various users different roles. A role is a set of permissions or privileges that the user has. One user may only be able to SELECT or read a few tables, another may also be able to UPDATE a few tables. The privileges are given as a list of tables and actions. The actions are SELECT, UPDATE, INSERT, and DELETE. Each user may have many roles. The DBA has unlimited privileges over all the tables in the database. Each user (if he or she is SQL savvy) can "see" his roles and their privileges in the USER_ROLE_PRIVS. Many programs are written to behave according to the role the user assumes. Such programs benefit from the ability to read this table. Have a go through the program and view these structures in a standard DBGrid using oraView.vbp.
With user objects, we're mainly interested in the OBJECT_NAME, and OBJECT_TYPE columns. You can see all the tables listed in USER_TABLES. USER_TAB_COLUMNS is where the mother lode is found! COLUMN_NAMEs, DATA_TYPEs, DATA_LENGTH, DATA_PRECISION, and DATA_SCALE. Precision and scale apply to numeric columns. You'll note that the data length of numeric columns is a constant 22. The precision determines the total number of digits you'll get and the scale is the number of decimal places. 8 and 2 mean a total of 8 digits, 2 of which are after the decimal point. A copy of the code for this program is included on the Conference CD. The information in USER_TAB_COLUMNS may easily be viewed inside SQL*PLUS by using the DESC command. When we want to write programs that massage the data dynamically we use the data dictionary inside ADO or 0040. DESC, because it is not a DML statement, does not work in ADO or 0040. We can also look in the Fields collection of a Resultset, but only after the Resultset has been defined by its SELECT statement. Using the data dictionary allows us to investigate the structure beforehand. Take a look at the data classes for USER_TABLES and USER_TAB_COLUMNS. The code you see is used inside my own data class generator. It uses 0040 to read the data dictionary and it generates 0040 code. This is a good time to dive into 0040. Oracle Object For OLE (0040)
OraClient is just a figurehead. Its purpose is merely to contain the OraSession. It has no properties or methods that are exposed to the programmer. This means that there is nothing we can do to change its behavior. OraClient represents the OracleInProcessServer, which is introduced at the project level. In your project menu click on the references and select the OracleInProcessServer by clicking on its checkbox. That's it! Your project now includes an OraClient. OraConnection is automatically created when we make an OraSession object, and serves no other unique purpose. This, as we have already seen, is not the case with ADO. OraParameterArray is the smart manner by which 0040 passes multiple parameters to the server. Sadly, we do not have time to devote to this here. And now for the more important Objects... OraSession – when a user ‘talks’ to an Oracle database, the conversation takes place in a session. This is the real root of Objects for OLE. A session implies that the user’s computer is connected to the Oracle server. We only need two lines of code to make a session. The lines below were copied from clsConnection - a class that I include in all my VB and Oracle Projects, and which will be discussed at somewhat greater length later. Private mOraSession As OraSession Set mOraSession = CreateObject("OracleInProcServer.XOraSession") The table below shows the important methods of the OraSession object. Four of the five methods in the table deal with Transactions.
A Transaction is a very important concept. Think about what happens when you transfer money from your savings account to your checking account. First the balance in your savings is reduced, and then your checking balance is increased. This, by the way, is the order in which the bank prefers to do the transfer. Should the process fail midway, you end up short. But I digress. It would be much better if we could guarantee that the transfer is completely done or not at all. This is where Transactions help. Every relational database has a transaction mechanism that starts with the BeginTrans command and ends with a CommitTrans or a Rollback. We commit the transaction if all went well (no errors were encountered) or roll it back if we encounter an error. Using transactions is mandatory whenever you update more than a single row at a time - even if the rows are in the same table. The most important OraSession method is OpenDatabase. We need to open a database in order to be able to work with it. In the presentation, I shall demonstrate how clsConnection does it in detail. I have built an application generator that uses 0040. I intend to demonstrate how we can design a table and maintain it in VB, all in 5 minutes. I do not distribute the generator in source code form. The exe requires Oracle to run. I attach the .exe - DBClassGenerate.exe. I’ll then talk about two files; clsConnection.cls and clsUserColumns.cls. The DatabaseName, User, and Password are properties of the class, each with its Property Let. I normally use a SignOn form that passes the user entered values to the class. I construct the connect-string, from the user name followed by a slash and the password, and pass it to the OpenDatabase method. The last parameter in the OpenDatabase method is the database Options parameter. These options are explained in the table below. Note that each constant used in 0040 has been given a descriptive name. These names are included in the ORACONST.txt file that comes with the Oracle 0040 installation disk. I include them in a standard VB module as constants. Because the code in the clsConnection is written once and used forever, and because I prefer short lines of code, I put these options as numbers rather than descriptive constants. You may opt to go the other way. Be that as it may, here, at last, are the values:
OraDatabaseThe Database is where it all happens. This is where we store data and where we retrieve it. It is the Raison D'être of this lecture. The center of the OraDatabase object is the OraDynaset around which we build the various Data Classes. The creation of dynasets is the most important method in 0040, but also the most elaborate. I save it for last. Here are the Oradatabase properties. Most are superfluous, one should be avoided, and three are useful.
The LastServerErr, LastServerErrPos, and LastServerErrText go hand in hand in hand with the LastServerErrReset method of the OraDatabase. They give us the error number, the position in the SQL string where the error occurred, and the Oracle error text. Parameters are actually placeholders inside SQL statements. They belong to the database and not to any particular dynaset. In the following SQL statement ‘IdField’ is a parameter, and can be identified as such by the preceding colon (which is the equivalent of ADO's '?'. The advantage of using parameters is that we do not have to re-issue a SQL statement with every change in the UserId. We may simply assign a new value to IdField and rerun the existing statement (which is done by issuing the Refresh command to the dynaset – Dynaset.Refresh). SQL statements can be compiled in the database. Compiling makes them run faster because we save the time it takes to interpret them again and again. The compiled statements are our Static SQL, as opposed to the constantly interpreted Dynamic SQL. Using parameters increases performance and also eases our programming effort. Here are the OraDatabase Methods.
We only have time to discuss a couple of these methods. Here they are: LastServerErrReset The most important piece of any data object is the Recordset. In 0040 it is still named Dynaset. CreateDynasetA select statement produces a view. Views have rows and columns and are, therefore, virtual tables. The early Microsoft name for such a view was a snapshot. You could look at it to your hearts delight, but you couldn't change it. A dynaset is a view that allows you to change its contents. It is a snapshot with a photo editor. Better still, while a photo editor allows you to change the photo, the dynaset allows you to change the landscape itself. Any change you introduce to the dynaset is immediately reflected in the underlying database. This is true for all dynasets that are based on a single table. Changing values in dynasets that result from more complex searches, such as Joins would be very difficult if not impossible, and they are read-only! Dynasets are the equivalent of RDO's Resultsets and ADO's Recordsets. Here is how we create a dynaset in 0040. Set mOraDynaset = mOradatabase.CreateDynaset (sSQL, Options) The method has two arguments (or parameters): Ø sSQL – A SELECT statement. This statement, unlike standard SQL statements, must not end with a semicolon. 0040 inserts the semicolon. Ø Options – A long integer that assumes any combination (sum) of the following:
As you surmised from the way I described the options, I only use option 4 or option 16. The two are obviously mutually exclusive. When I only need to read data I use 4, otherwise I use option 16. OraDynasetCovering all the OraDynaset properties and methods is an arduous undertaking. It is also not necessary. Most are hardly ever used. I’ll talk about four properties. You can review the rest in the 0040 help file.
An empty dynaset –when there are no records matching the query - returns both EOF and BOF as true. And here are a few of the OraDynaset methods.
Last but not least, the OraDynaset has a built-in collection of fields. The fields correspond to the names of the columns in the underlying SELECT statement. These fields are referenced in the code rather frequently. The syntax for referencing a field is as follows: Xyz = mdynTestDemo.Fields!FIELD_1.Value or Xyz = mdynTestDemo.Fields(“FIELD_1”).Value To assign a new value to the field: mdynTestDemo.Fields!FIELD_1.Value = xyz Use Early binding: Private mfldField1 As OraField Set mfldField1 = mdynTestDemo.Fields!FIELD_1 Xyz = MfldField1 MfldField1 = xyz Use early binding for fields and parameters, whenever possible! OraSQLStmtAt times we need to execute SQL statements from the OraDatabase object. The simple way is to use the OraDatabase.ExecuteSQL(SQL) method. For SQL statements that are executed more than once, use the OraSQLStmt object where the SQL statement is compiled the first time it is used. Each additional usage is done with Oracle p-code, making it much faster. The SQL statement may be a stored procedure and may include parameters. The statement is not limited to 'SELECT' and is used most of the time for action queries (UPDATE, INSERT, DELETE). To execute the statement from within the OraSQLStmt we invoke the Refresh method. Executing SQL statements directly or in the OraSQLStmt causes an automatic Commit. This can cause problems if complex transactions are executed in the background. For this reason, we must use the OraSession.BeginTrans method before running any SQL. This not only improves safety, but also boosts performance. We create an OraSQLStmt object by: Private mMyQuery As OraSQLStmt Set mMyQuery = OraDatabase.CreateSQL(sSQL, Options) mMyQuery.Refresh Using a stored procedure:Here is how we use a stored procedure. Note that here the SQL statement is enclosed in a Begin-End structure and that the semicolon is used. mOraDatabase.Parameters.Add "Param1", Space(40), 1 mOraDatabase.Parameters.Add "Param2", Space(35), 1 'The next two parameters are output and thus the 2 mOraDatabase.Parameters.Add "ErrNum", 0, 2 'The 0 is the value of the number mOraDatabase.Parameters.Add "ErrText", Space(50), 2 SSQL = "Begin proct_name(:Param1, :Param2, :ErrNum, :ErrText); end;" mOraDatabase.Parameters("Param1").Value = NewValue mMyQuery.Refresh lErr = mOraDatabase.Parameters("ErrNum").Value If lErr <> 0 Then sErr = mOraDatabase.Parameters("ErrText").Value MsgBox CStr(lErr) & sErr, "Can't Update Name" End If Options are 1 to stop parameter binding and 2 to raise an error if the SQL statement is invalid. Option 2 is the option I regularly use. I haven't yet used a SQL without parameters in an OraSQLStmt object. I also have yet to use a parameter-less stored procedure. The most common use of an OraSQLStmt is to run a stored procedure. The Oracle Data ControlThe ODC is a standard bound data control. It behaves exactly like a standard data control. The connection string is that of 0040 and the SQL statement defines a Dynaset inside the control. All the standard 'move' methods apply, as do the AddNew, Delete, and Update. The OraStruct program that we've viewed before, and for which you have received the source code, goes on to bind the ODC to a standard grid. The fastest way to create a simple Oracle VB program is using RDC this way. I wish I could devote more time to 0040. Companies who are committed to using Oracle should try it. Those who do, find it an excellent tool. I want to use the remaining time to go back to ADO. ADO all over again!I have printed the connection class for ADO as well as a data class. Most of what you're going to see is mundane ADO code. I am using a parameterized SQL statement to read the database and an ADO.Command with parameters to update the very same table. The one place where we have to be very careful when doing ADO with Oracle is in declaring parameters. You must be very careful to declare parameters for VARCHAR2 columns with the maximal length. Date/Time parameters are declared with the adDBTimeStamp constant. Any parameter that does not match the length of the column in Oracle will be flagged and cause a runtime error. Take a look through the two classes clsSmpconnection.cls and clsSample.cls for further info. Back to 0040You can consider this a "Try it Out". Go to SQL*PLUS and create a new table. Go to VB and run my generator (DBClassGenerate.exe) on this table. The result is a short program, which you'll be able to use on the spot. The entire process should only take you about 10 minutes. Oracle and AccessThere is still another way to connect to oracle. This is especially important to power users who understand the basics of RDB and can work in Microsoft Access. We already have a system DSN to Oracle. We can use this ODBC connection from within Access. All you need to do is to link access to the oracle tables of interest. You may now view them and update them from within Access. This gives the power user an almost instant access (pun intended) to Oracle. 0040 and ADO – What shall I useIt is a tough decision. ADO seems to be the standard and we all love to join the crowd. William Golding was once asked by his then fiancée (she soon left him), why he should not become a Methodist like her and her Minister father. “There are millions of Methodists” she claimed, “how could all of them be wrong?” Golding replied that there are even more Buddhists so how could all of them be wrong. But I digress. ADO and Oracle were not exactly made for each other, so there are problems. Here are the ADO and Oracle major problems:Ø Oracle Bind parameters cannot be used. Ø Using ADO parameters in the select statement gives us a read-only recordset that forces us to do INSERT, UPDATE and DELETE in the command object, making programming more cumbersome. Ø Even though we’ve been promised otherwise that in Oracle 8 and up there are no more problems with In and Out parameters for Stored Procedures, this is not exactly the case. Also too many systems are still at level 7.x. Ø Cursor variables cannot be retrieved at all. 0040 has its own problemsØ 0040 is pure Oracle. If you switch to say SQL Server you have to do a switch to ADO as well. The switch itself is so painful that the 0040 – ADO conversion is dwarfed by it anyway. Ø We miss the wonderful GetRows method of ADO. We may however mimic it in data classes. Ø If we do not use VB (and in future may be even with VB) we’ll miss the ability to do asynchronous programs. 0040, for the time being, lacks events. (we may however program around it in the COM) Ø 0040 is slower (even though not very noticeably). Because 0040 is running in process (that is inside the same process space as your VB program) it is important to use early binding. The difference in performance will be astounding. Hardly anybody talks about 0040. It is a hidden treasure. I hope that in the future you’ll consider it. Much more is written about it in my upcoming book - VB and Oracle Programmers Reference by Wrox Press. |
|
|
|
|
|
| |
|
Email TopXML
|
|
Front Page Daily Stuff TopXML Forum XML blogs XML Newsgroups BizTalk Biztalk Utilities Biztalk Utilities Tutorial B2B SAP XML Microsoft .NET Dotnet System XML Soapformatter SQLXML XMLserializer XQuery PHP PHP SimpleXML PHP XML Dom PHP XML RPC PHP XSLT Java Java Java XML Xalan Microsoft ASP ASP Schemas XML SQL Server XML XMLDom XSL XSL Tutorial XSLT Stylesheets General Javascript CSS XHTML WAP |