Oracle dOOdads for VB.NET has shipped

[mike.griffin]

I already have the dynamic query portion working, so I can use dOOdads on Oracle to do things like:

Code: Select all

Dim job As New Jobs
job.ConnectionString = \"Password=sa;User ID=user;Data Source=dbMeta;\"
job.QuerySource = \"HR.JOBS\"
job.Where.JOB_TITLE.Value = \"President\"

If job.Query.Load() Then

End If

I'm working with one of our users on this, all I have to do now is create the stored proc template for Oracle and a few more things, hopefully by Monday morning Oracle dOOdads will release.

[mike.griffin]

Using the stored procedures generated by my Oracle Stored procedure template (which now by the way supports sequenced fields) I was able to execute this code and save it back to the Oracle database.

Code: Select all

      Dim job As New Jobs
      job.ConnectionString = \"Password=sa;User ID=GRIFFO;Data Source=dbMeta;\"
      job.QuerySource = \"HR.JOBS\"
      job.Where.JOB_TITLE.Operator = WhereParameter.Operand.Like_
      job.Where.JOB_TITLE.Value = \"%e%\"

      If job.Query.Load() Then

         job.s_JOB_TITLE = \"Mikes\"
         job.Save()

      End If

If I really crank on it tonight it ought to be alpha ready, I am still targeting this Monday for the first release of Oracle dOOdads.

[mike.griffin]

Last night I did a full functional test using an Oracle based dOOdad. I tested the 5 stored procs. It was a little disappointing to find that Oracle stored procedures names have a maximum length of 30 characters. The stored procedure names are as follows and truncated at 30 characters if they run over in name.

1) PK_<TableName> = LoadByPrimaryKey
2) PL_<TableName> = LoadAll
3) PU_<TableName> = Update
4) PI_<TableName> = Insert
5) PD_<TableName> = Delete

And most of the dynamic query capabilities.

Also, sequenced fields are supported through user meta data. Here's how to declare a sequenced field in the user meta data. Suppose your table is called MIKES_TABLE and it's in the HR schema. You define the user meta data for sequenced fields on the table, so, on the \"MIKES_TABLE\" table I would add this in the user meta data to hookup a sequence to a column.

Key=\"SEQ:THEKEY\" Value=\"HR.EMPLOYEES_SEQ\"

Where \"THEKEY\" is a column in the MIKES_TABLE table and I want it's value during an INSERT to be derived from the EMPLOYEES_SEQ Oracle Sequence. Notice also that the \"p_THEKEY\" parameter is marked OUT and available in your dOOdad immediately after save.

Code: Select all

CREATE OR REPLACE PROCEDURE \"HR\".\"PI_MIKES_TABLE\"
(
   p_FIRST_NAME IN MIKES_TABLE.FIRST_NAME%type,
   p_LAST_NAME IN MIKES_TABLE.LAST_NAME%type,
   p_THEKEY OUT MIKES_TABLE.THEKEY%type
)
IS
BEGIN

   SELECT HR.EMPLOYEES_SEQ.NextVal INTO p_THEKEY FROM DUAL;

   INSERT
   INTO MIKES_TABLE
   (
      FIRST_NAME,
      LAST_NAME,
      THEKEY
   )
   VALUES
   (
      p_FIRST_NAME,
      p_LAST_NAME,
      p_THEKEY
   );
END PI_MIKES_TABLE;
/

[mike.griffin]

User Meta data syntax for using a sequence in a SQL UPDATE statement

KEY=SEQ:U:EMPLOYEE_ID VALUE=\"HR.EMPLOYEES_SEQ\"

User Meta data syntax for using a sequence in a SQL INSERT statement

KEY=SEQ:I:EMPLOYEE_ID VALUE=\"HR.EMPLOYEES_SEQ\"

You can have more than one per update or insert. You simply need to add these to your local user meta data as key/value pairs on the parent table not the column itself.