RealBasic/Xojo: An ADODB Wrapper around the OLEObject framework

Note:
All problems seem to be solved for Xojo, thanks to John Hansen and Paul Lefebvre! Redownload the Wrapper at the end of the article if you had already downloaded it before.

For a project at OneTwo I needed ADODB instead of ODBC to connect to a database. Realbasic/Xojo can do this through the OLEObject, but non of the methods or properties are exposed to the programmer. If you only need it now and then that’s not a real problem, but in some cases you’ll use it all the time. So I decided to write a wrapper around the OLEObjects to make it easier. Also, all available Enums are available (ADOConstants) This way you can just program like this:

BlogArt1a

The code is also much more readable and familiar looking:

  dim CurrentPath as string
  CurrentPath = GetFolderItem("").AbsolutePath
  
  Dim conn as new ADOConnection
  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + CurrentPath + "Test.mdb"
  
  if conn.Open() then
    MsgBox "Database open!"
  else
    if conn.ErrorsCount > 0 then
      MsgBox str(conn.GetError(0).Number) + "- " + conn.GetError(0).Description
    end if
  end if
  
  dim SQL_str as string
  dim tmp_rs as ADORecordset
  dim ret as Boolean
  
  SQL_str = "INSERT INTO tData ( datText, datValue, datParentID ) SELECT tUren.Uur, 1 AS Expr1, 1 AS Expr2 FROM tUren ORDER BY tUren.Uur;"
  tmp_rs = conn.Execute(SQL_str)
  if tmp_rs <> nil then
    ret = tmp_rs.close
    tmp_rs = nil
    MsgBox "Records added!"
  else
    if conn.ErrorsCount > 0 then
      MsgBox str(conn.GetError(0).Number) + "- " + conn.GetError(0).Description
    end if
  end if
  
  SQL_str = "UPDATE tData SET datValue=datValue+1;"
  tmp_rs = conn.Execute(SQL_str)
  if tmp_rs <> nil then
    ret = tmp_rs.close
    tmp_rs = nil
    MsgBox "Records updated!"
  else
    if conn.ErrorsCount > 0 then
      MsgBox str(conn.GetError(0).Number) + "- " + conn.GetError(0).Description
    end if
  end if
  
  SQL_str = "SELECT datValue FROM tData WHERE datText='12:00';"
  tmp_rs = new ADORecordset
  ret = tmp_rs.Open(SQL_str, conn)
  if ret then
    MsgBox "Selected 12:00 value=" + str(tmp_rs.Field("datValue").IntegerValue)
    ret = tmp_rs.close
    tmp_rs = nil
  else
    if conn.ErrorsCount > 0 then
      MsgBox str(conn.GetError(0).Number) + "- " + conn.GetError(0).Description
    end if
  end if
  
  SQL_str = "DELETE * FROM tData;"
  tmp_rs = conn.Execute(SQL_str)
  if tmp_rs <> nil then
    ret = tmp_rs.close
    tmp_rs = nil
    MsgBox "Records deleted!"
  else
    if conn.ErrorsCount > 0 then
      MsgBox str(conn.GetError(0).Number) + "- " + conn.GetError(0).Description
    end if
  end if
  
  ret = conn.Close
  conn = nil
  
  MsgBox "Done!"

Much more like it! The framework contains the following classes:

BlogArt1b

Note:
You’ll notice that every class contains a inner… OLEObject. This is the real ADODB object, just in case I forgot something πŸ™‚

The fun part is that also objects like Stream are now available in a convenient way. You can program stuff like this with ease:

  dim objStream as new ADOStream()
  dim ret as Boolean
  ret = objStream.Open
  
  ret = objStream.WriteText("Dit is een Γ© text")
  
  objStream.Position = 0
  MsgBox objStream.ReadText + "(" + str(objStream.Size) + ")"
  
  objStream.Position = 0
  objStream.Type = StreamTypeEnum.adTypeBinary
  MsgBox  objStream.Read(2)
  
  ret = objStream.SaveToFile("c:\adodb.txt", SaveOptionsEnum.adSaveCreateOverWrite)
  
  ret = objStream.Close
  objStream = nil

Not all classes have been tested, so if you find an error please report them so I can make the needed changes for everybody.

All classes are open source and can be used for non-commercial and commercial use but you have to include the license.txt file.

Download classes + demo:
ADODBWrapper

Bye for now!

Advertisements

About Alwaysbusy

My name is Alain Bailleul and I'm the Senior Software Architect/Engineer at One-Two. I like to experiment with new technologies, Computer Vision and A.I. My projects are programmed in B4X , Xojo, C#, java, HTML, CSS and JavaScript. View all posts by Alwaysbusy

5 responses to “RealBasic/Xojo: An ADODB Wrapper around the OLEObject framework

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: