ADO: Listing tables from a database

[Antonio Linares]

Code: Select all  Expand view

#include "FiveWin.ch"
#include "xbrowse.ch"
#include "xhb.ch"

function Main()

   local oCon := TOleAuto():New( "ADODB.Connection" )
   local oCat := TOleAuto():New( "ADOX.Catalog" )
   local oRs  := TOleAuto():New( "ADODB.Recordset" )
   local aTables := {}, cTable, oError

   try
      oCon:Open( "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source='xbrtest.mdb';" )
   catch oError
      MsgInfo( oError:Description )
   end  

   oCat:ActiveConnection = oCon
   oRs = oCon:OpenSchema( 20 ) // adSchemaTables
   
   while ! oRs:Eof()
      AAdd( aTables, oRs:Fields:Item( "Table_Name" ):Value )
      oRs:MoveNext()
   end  
   
   XBROWSER aTables SELECT cTable := aTables[ oBrw:nArrayAt ]

   oRs:Close()
   oRs:CursorType     = 1        // opendkeyset
   oRs:CursorLocation = 3        // local cache
   oRs:LockType       = 3        // lockoportunistic

   if ! Empty( cTable )
      try
         oRs:Open( "SELECT * FROM " + cTable, oCon ) // Password="abc" )
      catch oError
         MsgInfo( oError:Description )
      end  

      XBrowser( oRS )
   endif
   
return nil

[Antonio Linares]

This is an alternative way of doing it using the most recent FWH functions:

Code: Select all  Expand view

#include "FiveWin.ch"
#include "xbrowse.ch"

function Main()

   local oCn, oRs, cTable

   oCn   := FW_OpenAdoConnection( "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source='xbrtest.mdb';" )

   if oCn != nil
      oRs   := oCn:OpenSchema( 20 )

      #ifdef __XHARBOUR__
         oRs:cClassName := "RECORDSET"  // due to a bug in xbrowse.prg. Fixed in FWH13.05
      #endif

      XBROWSER oRs TITLE "Select a table" ;
         COLUMNS { "TABLE_NAME" } SELECT ( cTable := oBrw:aCols[ 1 ]:Value )
         
      oRs:Close()
     
      if ! Empty( cTable )
         if ' ' $ cTable
            cTable   := '[' + cTable + ']'
         endif
         if ( oRs := FW_OpenRecordSet( oCn, cTable ) ) != nil
            XBROWSER oRs AUTOSORT
            oRs:Close()
         else
            ? "Failed to open " + cTable
         endif
      endif
      oCn:Close()
   else
      ? "Connection Fail"
   endif

return nil

[Rick Lipkin]

Antonio

I like where you are going with ADO here ..

One thing that needs to be done is add the ability for a password to the connection string. Let me dig deeper and see if I can help.

Rick Lipkin

[Rick Lipkin]

Antonio

I am using fwh1303 and xHarbour .. Trying to compile and getting an error on EXECUTE .. I rem'd it out temporarily to get get a good build.

Rick Lipkin

Code: Select all  Expand view

DEFINE BUTTON OF oBar PROMPT "Run" RESOURCE "run" ACTION nil GROUP //Execute( ( cAlias )->Code )
 

[Antonio Linares]

Rick,

What compiling error do you get ? syntax error only ? There is no DEFINE BUTTON in my code

I really would appreciate your cooperation in adding ADO support to FWH FiveDBU.prg:
https://code.google.com/p/fivewin-contributions/downloads/detail?name=fivedbu_20130503_2.zip

We already open databases and tables, Add a record, and we get an error deleting one (the browse painting after it). Rao is also helping on this.

If we are able to provide all the required ADO functionality from FiveDBU I think that we will be providing a great help for users wanting to easily use ADO

[Rick Lipkin]

Antonio

Let me see what I can do with your code .. here is the line where my compile fails on Execute()

Rick Lipkin

[Antonio Linares]

Rick,

I am afraid that Execute() portion of FiveDBU will not work with xHarbour and we use the great Harbour capability to compile and execute from memory in runtime. I am going to review the syntax of that line.

We have just published an enhanced FiveDBU with Add and Delete from an ADO browse
viewtopic.php?f=3&t=26229&p=144053#p144053

[Antonio Linares]

Rick,

An enhanced version already published that supports editing:
viewtopic.php?p=144063#p144063

It compiles fine here with xHarbour, except that function Execute() has to be declared dummy. Maybe we will supply it by default.

[Marc Vanzegbroeck]

Antonio,

As soon as you implement the option to define a connection-string, I will test it on MySQL/MariaDB & SQLite-databases.

I can't modify the program myself, since I still use an older version of FW (7.10) that is not able to compile the source.
With my old version I can use and browse MySQL & SQLite-database via ADO without any problem. That's the reason wy I didn't upgrade yet to a newer version.

[Antonio Linares]

Marc,

Could you email me the connection string ? I will build it for you and email it to you.

There were lots of OLE errors that have been fixed in the recent years, thats why I suggest you to upgrade and don't block yourself on an old version