Forget who is said, when there is no new thing to write, it is the time. Since I enter the company, I have been doing Work with VB through OO4O to read and write the database. I have a little understanding of OO4O, referring to the predecessor's article, I also summarize it, or always make my blog empty nice. Ok, let's go to the positive biography.
OO4O, Oracle Object For OLE, is a set of software layers, with Oracle sessions by calling Oracle interfaces.
Its design is to enable Microsoft COM Automation and ActiveX-compatible languages to access Oracle databases directly, including Visual Basic, Visual Basic For Application (VBA), IIS Active Server Pages (ASP), Windows Scripts Host and Visual C (here We only talk about how to implement it with VB). These layers are written directly on Oracle Call Interface (OCI), bypassing standard ODBC, OLE DB, and ADO layers. Although it is not compatible when using other databases, it is able to access Oracle databases directly, and it is difficult to access the Oracle database. This is difficult for any other database access. But it also has enough:
· In some instances, its speed is not the fastest. Part of the reason is because it is a service program in a process.
· OO4O does not support asynchronous operations or events.
· OO4O does not support GetRows mode. OO4O is mainly designed to update dynamic sets.
(See "Several VB Accessing Oracle Database Technology" Second Room, Second Room, No.1, Middle Section, Xi'an City, Zheng Hao Ma Xiaofei, Zhenzhi)
With this technology, we can easily access the content of the Oracle database without any controls in VB. Of course, you must have an Oracle's Oracle Object for Ole and Oracle's client or Personal Oracle, otherwise you will be able to build an object. "Error message. For the simple operation of OO4O, you can refer to the predecessors' articles.
Http://www.dbonline.cn/source/oracle/20040216/other_access 0 hac Database using ole(1).html
http://www.dbonline.cn/source/oracle/20040216/other_access 0 hac Database USING ole (2).html
Here, I will move directly in order to look at the convenience, and the coherence of the article.
We want to establish join with the Oracle database, here we use two objects ORASESSION objects and ORADATABASE objects. ORASESSION objects are used to manage ORADATABASE objects, which can create ORADATABASE objects with ORASESSION objects while establishing the contents of the Oracle database to access the database. The following example demonstrates how to use the ORASESSION object and ORADATABASE object to establish join with the Oracle database:
Private submmand_click ()
Dim ORASESSION As Object
Dim ORADATABASE AS Object
DIM LS_USERNAME AS STRING, LS_PASSWORD AS STRING, LS_SERVER AS STRING
LS_SERVER = "" join local Oracle database
Ls_username = "system"
'User named systemls_password = "manager"
'Password is Manager, build an ORASESSION object
Set ORASESSION = CreateObject ("OracleinProcser.xorative)
'Error handling
ON Error Goto Connect_err:
'Establish an ORADATABASE object to join the Oracle database
Set ORADATABASE = ORASESSION.DBOPENDATABASE (LS_SERVER, LS_USERNAME "/" LS_Password, 0 &&)
MSGBOX "Database Joints!", Vbokonly Vbinformation, "Tips"
ON Error Goto 0
EXIT SUB
Connect_err:
IF INSTR (ORASSION.LastServererrtext, "ORA-01017") THEN
"Invalid username and password, database connection is unsuccessful!", Vbokonly Vbcritical, "Tips"
Else
MsgBox "Database Union is unsuccessful! Please check the error message below:" chr (13) ORASESSION.LastServereRrtext, Vbokonly Vbcritical, "Tips"
END IF
End Sub
After establishing a database joint, we can run the corresponding SQL command to perform the corresponding operation of the content of the database. For non-query sql commands (non-select commands), you can use the EXECUTESQL method for ORADATABASE objects to perform the ORADYNASET object using the CreateDyNaset method for the Database, and then create an ORAFIELDS object to retrieve the query results. Below we exemplify how to create new users and query and list the contents of the UserName field in the DBA_USERS view, the program is as follows:
Private submmand_click ()
Dim ORASESSION As Object
Dim ORADATABASE AS Object
Dim ORADYNASET AS Object
Dim Orafields as Object
DIM LS_USERNAME AS STRING, LS_PASSWORD AS STRING, LS_SERVER AS STRING
DIM LS_MESS AS STRING
Dim li_i as integer
LS_SERVER = "" "
'Join local Oracle database
Ls_username = "system"
'User named System
Ls_password = "manager"
'Password is Manager
Set ORASESSION = CreateObject ("OracleinProcser.xorative)
ON Error Goto Connect_err:
Set ORADATABASE = ORASESSION.DBOPENDATABASE (LS_SERVER, LS_USERNAME "/" ls_password, 0 &&) 'Execute the SQL command After you jump to SQL_ERR
ON Error Goto SQL_ERR:
Set ORADYNASET = ORADATABASE.DBCREATEDYNASET ("SELECT UserName from DBA_USERS", 0 &&)
If ORADYNASET.Recordcount> 0 THEN
'Establish ORAFIELDS object to search for the contents of the query
Set Orafields = ORADYNASET.FIELDS
Ls_mess = "User List:" CHR (13)
LS_MESS = LS_MESS ORAFIELDS ("Username") .Value 'This can also be used directly with ORAFIELDS (0) .value
For Li_i = 1 to OradyNaset.Recordcount - 1
ORADYNASET. MOVENEXT
LS_MESS = LS_MESS CHR (13) ORAFIELDS ("Username") .value
Next li_i
MSGBOX LS_MESS, VBOKOKONLY VBINFORMATION, "Query Results"
Else
MsgBox "No Information!", Vbokonly Vbinformation, "Tips"
END IF
'Creating a new user test, password is TEST
ORADATABASE.EXECUTESQL ("Create User Test Identified by Test")
Msgbox "user creation!", Vbokonly vbinformation, "prompt information"
Set ORADYNASET = ORADATABASE.DBCREATEDYNASET ("SELECT UserName from DBA_USERS", 0 &&)
If ORADYNASET.Recordcount> 0 THEN
'Establish ORAFIELDS object to search for the contents of the query
Set Orafields = ORADYNASET.FIELDS
Ls_mess = "User List:" CHR (13)
LS_MESS = LS_MESS ORAFIELDS ("UserName") .value
For Li_i = 1 to OradyNaset.Recordcount - 1
ORADYNASET. MOVENEXT
LS_MESS = LS_MESS CHR (13) ORAFIELDS ("Username") .value
Next li_i
Msgbox ls_mess, vbokonly vbinformation, "User established after query results"
Else
MsgBox "No Information!", Vbokonly Vbinformation, "Tips"
END IF
EXIT SUB
Connect_err:
If INSTR (ORASSION.LASTSERVERRRTEXT, "ORA-01017") Thenmsgbox "invalid username and password, database unsuccessful!", Vbokonly vbcritical, "prompt information"
Else
MsgBox "Database Union is unsuccessful! Please check the error message below:" chr (13) ORASESSION.LastServereRrtext, Vbokonly Vbcritical, "Tips"
END IF
EXIT SUB
SQL_ERR:
If ORADATABASE.LASTSERVERRERR <> 0 THEN
MsgBox "Database Operation error! Please check the error message below:" chr (13) ORADATABASE.LASTSERVERRRTEXT, VBOKOMONLY VBCRITILAL, "Tips"
END IF
End Sub
Because the write operation on the database is not submitted, after the program is over, the database content does not change. To change the need to add ORASESSION.BEGINTRANS before writing, add an ORASESSION.COMMITTRANS or ORASESSION. ROLLBACK.
After two examples, I believe that everyone has a certain understanding of OO4O. Let me talk about the implementation of calling user-defined database functions.
