Using Oracle ADW with Xojo (OracleDatabase)

Databases
1

Hello guys,

if you need to connect your application to an ADW Oracle DB via Wallet simply pass your database connection name to the Xojo OracleDatabase Object

Platform: Windows
Xojo Version: Version 2022 Release 3

//CREATE TABLE "DB202104200933_DEV"."TEST" 
//(    "CKEY" NUMBER(*,0) DEFAULT 0 Not NULL ENABLE, 
//     "CVALUE" VARCHAR2(100 Byte) COLLATE "USING_NLS_COMP" Not NULL ENABLE, 
//     "AMOUNT" NUMBER(18,5) Not NULL ENABLE, 
//     "CHANGED_AT" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP Not NULL ENABLE, 
//     "ID" NUMBER(*,0) Not NULL ENABLE, 
//     "INTERNALID" NUMBER(*,0) GENERATED ALWAYS As IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START With 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  Not NULL ENABLE
//)

// -- Data
//REM INSERTING into EXPORT_TABLE
//SET DEFINE OFF;
//Insert into EXPORT_TABLE (CKEY,CVALUE,AMOUNT,CHANGED_AT,ID,INTERNALID) values (44,'88',55.0006,to_timestamp('15-JUL-22 01.39.30.910847000 PM','DD-MON-RR HH.MI.SSXFF AM'),100,1);
//Insert into EXPORT_TABLE (CKEY,CVALUE,AMOUNT,CHANGED_AT,ID,INTERNALID) values (1,'Value1',1.1,to_timestamp('24-MAY-21 01.03.33.283078000 PM','DD-MON-RR HH.MI.SSXFF AM'),101,2);
//Insert into EXPORT_TABLE (CKEY,CVALUE,AMOUNT,CHANGED_AT,ID,INTERNALID) values (2,'Value1',2.2,to_timestamp('24-MAY-21 01.03.43.781985000 PM','DD-MON-RR HH.MI.SSXFF AM'),102,3);
//Insert into EXPORT_TABLE (CKEY,CVALUE,AMOUNT,CHANGED_AT,ID,INTERNALID) values (3,'Value3',33.33,to_timestamp('24-MAY-21 01.03.55.789599000 PM','DD-MON-RR HH.MI.SSXFF AM'),103,4);


Var db As OracleDatabase = New OracleDatabase

db.DatabaseName = "[connection description]_high" // ADW Wallet tnsnames.ora connection name
db.UserName = "[username]"
db.Password = "[password]"
db.Debug = 1

Try
  db.Connect
  // MessageBox("Connected to Oracle!")
  
  Var stmt As OracleSQLPreparedStatement
  
  // note in a prepared statement you DO NOT put in the quotes
  stmt = OracleSQLPreparedStatement(db.Prepare("SELECT t.* FROM TEST t WHERE t.ID >= :ID"))
  
  // have to tell sqlite what types the items being bound are so it does the right thing
  stmt.BindType(0, OracleSQLPreparedStatement.SQL_TYPE_INTEGER)
  stmt.Bind(0, 100)
  
  Var rs As RowSet = stmt.SelectSQL
  
  ListBox1.RemoveAllRows
  ListBox1.ColumnCount = rs.ColumnCount
  ListBox1.HasHeader = True
  
  Var hasHeadings As Boolean
  
  While rs.AfterLastRow <> True
    ListBox1.AddRow("")
    
    For i As Integer = 0 To rs.ColumnCount-1
      If Not hasHeadings Then ListBox1.HeaderAt(i) = rs.ColumnAt(i).Name
      ListBox1.CellTextAt(ListBox1.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
    Next
    
    rs.MoveToNextRow
    hasHeadings = True
  Wend
  
  
Catch error As DatabaseException
  MessageBox("Error connecting to Oracle: " + error.Message)
End Try

Note: Your Oracle Instantclient and Wallet must be correctly installed to make this snippet work.
SQL for “playing” shipped with. :slight_smile:

I put this lines into a Pressed() event to populate a ListBox1 Control

Hope this snippet helps. :hammer_and_wrench:

1 Like