Fivewin and ADO

[Jimmy]

hi,

Fivewin seem to use ADO to make Connection to SQL Server (or Excel) and use ADO Record-Set

but ADO is not "on-Board" in Windows.
Office seem to include ADO

---

when search i found

Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=54920
https://www.microsoft.com/de-de/download/details.aspx?id=54920

Microsoft Access Database Engine 2010 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
https://www.microsoft.com/de-de/download/details.aspx?displaylang=en&id=13255

but which ADO Version is to use with Fivewin 22/07

[nageswaragunupudi]

All versions should work.

[nageswaragunupudi]

We tried to make FWH ADO functions work with all versions and keep updating.
Will you please try and let us know?

[nageswaragunupudi]

but ADO is not "on-Board" in Windows.

By default, every Windows OS comes with Jet OLEDB.
This OLEDB supports ADO with DBase and mdb files with MSAccess and Excel. We do not need to install anything else.

We can try this code, without specially installing any specific software for ADO/OLEDB

Code: Select all  Expand view  RUN

// DBase III
   c  := "c:\fwh\samples\states.dbf"
   oCn   := FW_OpenAdoConnection( cFilePath( c ) )
   oRs   := FW_OpenRecordSet( oCn, cFileNoExt( c ) )
   XBROWSER oRs
   oRs:Close()
   oCn:Close()
// MSAccess (*.mdb only)
   oCn   := FW_OpenAdoConnection( "c:\fwh\samples\xbrtest.mdb" )
   oRs   := FW_OpenRecordSet( oCn, "customer" )
   XBROWSER oRs
   oRs:Close()
   oCn:Close()
 

To open an excel sheet:

Code: Select all  Expand view  RUN

FW_OpenADOExcelSheet( cFile, cSheet, cRange )

Microsoft SQL Server

Code: Select all  Expand view  RUN

  // Microsoft SQL Server
   // Uses SQLOLEDB driver the comes bundled with Windows
   oCn   := FW_MSSQLDB() // FWH Cloud server
   oRs   := FW_OpenRecordset( oCn, "customer" )
   XBROWSER oRs
   oRs:Close()
   oCn:Close()
 

All above work with the OLEDB drivers that come bundled with Windows by default.
No downloads and installations are required.

[Jimmy]

hi,

Ok thx i will try

[Jimmy]

hi,

i want to use PostgreSQL with ADO (Xbase++ v2.x use PostgreSQL)

these ConnectionString are from ADORDD

Code: Select all  Expand view  RUN

STATIC FUNCTION ADOOPENCONNECT( cDB, cServer, cPort, cEngine, cUser, cPass, oCn )
LOCAL oCatalog

   oCn:ConnectionTimeOut := 60                                        //26.5.15 28800 //24.5.15 added by lucas de beltran

   DO CASE
      CASE cEngine = "DBASE"
         oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cDB + ;
                   ";Extended Properties=dBASE IV;User ID=" + cUser + ";Password=" + cPass + ";" )

      CASE cEngine = "FOXPRO"
         oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cDB + ;
                   ";Extended Properties=Foxpro;User ID=" + cUser + ";Password=" + cPass + ";" )

      CASE cEngine = "ACCESS"
         IF !FILE( cDB )
            oCatalog := ADOCLASSNEW( "ADOX.Catalog" )                 //TOleAuto():New( "ADOX.Catalog" )
            oCatalog:Create( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cDB )
         ENDIF

         IF EMPTY( cPass )
            oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cDB )

         ELSE
            oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cDB + ";Jet OLEDB:Database Password=" + ALLTRIM( cPass ) )

         ENDIF

      CASE cEngine = "ADS"
         oCn:Open( "Provider=Advantage OLE DB Provider;User ID=" + cUser + ;
                   ";Password=" + cPass + ";Data Source=" + cDB + ";TableType=ADS_VFP;" + ;
                   "Advantage Server Type=ADS_LOCAL_SERVER;" )

      CASE cEngine == "MYSQL"
         IF( cPort == NIL, cPort := "3306", cPort )
         oCn:Open( "Driver={mySQL ODBC 5.3 ANSI Driver};" + ;
                   "server=" + cServer + ;
                   ";Port=" + cPort + ;
                   ";database=" + cDB + ;
                   ";uid=" + cUser + ;
                   ";pwd=" + cPass + ";" )

      CASE cEngine == "MARIADB"
         t_cEngine := "MYSQL"                                         //ITS THE SAME SHOULD WORK LIKE THIS IN ALL ROUTINES
         IF( cPort == NIL, cPort := "3306", cPort )
         oCn:Open( "Driver={mySQL ODBC 5.3 ANSI Driver};" + ;
                   "server=" + cServer + ;
                   ";Port=" + cPort + ;
                   ";db=" + cDB + ;
                   ";uid=" + cUser + ;
                   ";pwd=" + cPass + ";" )

      CASE cEngine == "MSSQL"
         oCn:Open( "Provider=SQLOLEDB;" + ;
                   "server=" + cServer + ;
                   ";database=" + cDB + ;
                   IIF( EMPTY( cUser ), ";Trusted_Connection=yes", ;
                   ";uid=" + cUser + ;
                   ";pwd=" + cPass ) )

      CASE cEngine == "ORACLE"
         oCn:Open( "Provider=MSDAORA.1;" + ;
                   "Persist Security Info=False" + ;
                   IIF( EMPTY( cServer ), ;
                   "", ";Data source=" + cServer ) + ;
                   ";User ID=" + cUser + ;
                   ";Password=" + cPass )

      CASE cEngine == "FIREBIRD"
         oCn:Open( "Driver=Firebird/InterBase(r) driver;" + ;
                   "Persist Security Info=False" + ;
                   ";Uid=" + cUser + ;
                   ";Pwd=" + cPass + ;
                   ";DbName=" + cDB )

      CASE cEngine == "SQLITE"
         oCn:Open( "Driver={SQLite3 ODBC Driver};" + ;
                   "Database=" + cDB + ;
                   ";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;" )

      CASE cEngine == "POSTGRE"
         IF( cPort == NIL, cPort := "5432", cPort )
         //PostgreSQL ANSI //ODBC Driver(ANSI)
         oCn:Open( "Driver={PostgreSQL ANSI};Server=" + cServer + ";Port=" + cPort + ";" + ;
                   "Database=" + cDB + ;
                   ";Uid=" + cUser + ";Pwd=" + cPass + ";" )

      CASE cEngine == "INFORMIX"
         oCn:Open( "Dsn='';Driver={INFORMIX 3.30 32 BIT};" + ;
                   "Host=" + "" + ";Server=" + cServer + ";" + ;
                   "Service=" + "" + ";Protocol=olsoctcp;" + ;
                   "Database=" + cDB + ";Uid=" + cUser + ";" + ;
                   "Pwd=" + cPass + ";" )

      CASE cEngine == "ANYWHERE"
         IF( cPort == NIL, cPort := "2638", cPort )
         oCn:Open( "Driver={SQL Anywhere 12};" + ;
                   "Host=" + cServer + ";Server=" + cServer + ";port=" + cPort + ";" + ;
                   "db=" + cDB + ;
                   IIF( EMPTY( cUser ), ";Trusted_Connection=yes", ;
                   ";uid=" + cUser + ;
                   ";pwd=" + cPass ) )
      OTHERWISE
         MSGALERT( "Connection failed DB engine " + cEngine + " its unknown to ADORDD!" )

   ENDCASE

RETURN oCn

can i use these ConnectionString with Fivewin

[nageswaragunupudi]

can i use these ConnectionString with Fivewin

Yes

Code: Select all  Expand view  RUN

oCn := FW_OpenAdoConnection( cYourConnectionString, .t. )

Syntax:

Code: Select all  Expand view  RUN

FW_OpenAdoConnection( cFWConnectSpec[or]cYourConnectionString, [lShowError],[@oError]) --> oCn
// oCn is NIL if failed
 

FW Connection Spec is an array:

Code: Select all  Expand view  RUN

{ cRDBMS, cServer, cDB, cUser, cPassword}

Example:

Code: Select all  Expand view  RUN

oCn   := FW_OpenAdoConnection( { "MYSQL","209.250.245.152","fwh","fwhuser","FiveTech@2022" }, .t. )

FWH function generates an appropriate connection string and connects to the database.

[Jimmy]

hi,

is there any Sample how to use FW_OpenADOExcelSheet()
! Note : i want to "read" Excel Sheet NOT "write"

i have try

Code: Select all  Expand view  RUN

  oRs := FW_OpenADOExcelSheet ( cFile, cSheet, cRange )
   XBROWSER oRs

but it crash

Error description: (DOS Error -2147352570) WINOLE/1009 No exported method: ISKINDOF
Args:
[ 1] = C TLINKLIST

Stack Calls
===========
Called from: => TOLEAUTO:ISKINDOF( 0 )
Called from: .\source\function\XBROWSER.PRG => XBROWSE( 101 )
Called from: .\DUALGRID.PRG => OPENADOEXCEL( 6126 )
Called from: .\DUALGRID.PRG => VIEWER( 2565 )

---

i found c:\fwh\samples\xbrxls.prg

but it crash ...

Error description: Error BASE/1004 Message not found: TXBROWSE:SALES

Stack Calls
===========
Called from: .\source\function\HARBOUR.PRG => _CLSSETERROR( 247 )
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:SALES( 11834 )
Called from: xbrxls.prg => MAIN( 28 )

---

can somebody show me a working Fivewin Sample how to "read" Excel Sheet and show it in XBROWSER please

[nageswaragunupudi]

Error description: (DOS Error -2147352570) WINOLE/1009 No exported method: ISKINDOF

This was due to a bug in xbrowser.prg in a few versions prior to FWH2210.
This was fixed in FWH2210.
The bug was only when we use XBROWSER command, But when we normally build our own xbrowse in a dialog/window there should be no problems

Code: Select all  Expand view  RUN

  oRs   := FW_OpenADOExcelSheet( "customer.xlsx" )
   XBROWSER oRs SETUP ( oBrw:AutoFit() )

[nageswaragunupudi]

i found c:\fwh\samples\xbrxls.prg

but it crash ...

Though we provided this program in the samples folder, we omitted to include the "xbrtest.xls" file in this folder.
It is our oversight and we regret the inconvenience.
This is the reason for the crash.

This program demonstrates the function

Code: Select all  Expand view  RUN

oRange   := GetExcelRange( cBook, [cSheet], [cRange] )

cSheet defaults to ActiveSheet and cRange to UsedRange.

Please try with any other excel book on your disk, using this single line code:

Code: Select all  Expand view  RUN

XBROWSER GetExcelRange( cExcelBookNameFullPath )

Function FW_OpenADOExcelSheet() uses ADO using Jet or ACE OleDB provider
and
Function GetExcelRange() uses Excel Ole.

[nageswaragunupudi]

Here is the fix for versions prior to FWH2210 in xbrowser.prg:

\fwh\source\function\xbrowser.prg
Please locate this code:

Code: Select all  Expand view  RUN

  if ValType( uData ) == "H" .or. ( ValType( uData ) == "O" .and. uData:IsKindOf( "TLINKLIST" ) )

Substitute the above code with:

Code: Select all  Expand view  RUN

  if ValType( uData ) == "H" .or. ( ValType( uData ) == "O" .and. ;
      __ObjHasMethod( uData, "ISKINDOF" ) .and. uData:IsKindOf( "TLINKLIST" ) )
 

But we advise you to user FWH2210 or later, preferably FWH2304

[Jimmy]

hi,

thx for Answer

i have 22.06 so i got the BUG and can´t use Fivewin Function

[Jimmy]

hi,

there seems to be NO "Microsoft.ACE.OLEDB.12.0" on Microsoft Windows [Version 10.0.25387.1] (latest Canary Version)

Code: Select all  Expand view  RUN

      strHeader := "HDR=YES;"
      oConnect := CreateObject( "ADODB.Connection" )

      oConnect:ConnectionString := 'Provider=Microsoft.ACE.OLEDB.12.0;' + ;
              'Data Source=' + cFile + ';' + ;
              'Extended Properties="Excel 12.0 Xml;' + strHeader + 'IMEX=1' + '";'

      bError := ERRORBLOCK( { | oErr | BREAK( oErr ) } )
      BEGIN SEQUENCE
         oConnect:Open()
      RECOVER USING oError
         ERRORBLOCK( bError )
         MsgStop( "Operation: " + oError:Operation + " - Description: " + oError:Description, "Error ConnectionString" )
         RETURN .F.
      END SEQUENCE
      ERRORBLOCK( bError )

p.s. same on fresh installed Microsoft Windows [Version 10.0.19045.3031]

[Jimmy]

hi,

have now a "normal" XBROWSE and can "show" Excel Sheet

Code: Select all  Expand view  RUN

    @  1,  1 XBROWSE oBrw SIZE - 1, - 1 PIXEL OF oDlg ;
              RECORDSET objRS ;
              HEADERS aHead ;
              AUTOCOLS ;
              CELL LINES NOBORDER FASTEDIT ;
              FONT oFontDefault COLOR BFcolor, BGcolor

now i want to "FASTEDIT" Sheet but how to configure XBROWSE and/or "ADODB.Recordset"

Code: Select all  Expand view  RUN

     objRS := CreateObject( "ADODB.Recordset" )

      bError := ERRORBLOCK( { | oErr | BREAK( oErr ) } )
      BEGIN SEQUENCE
*        objRS:Open( "Select * from [" + strRange + "]", oConnect, adOpenStatic )
         objRS:Open( "Select * from [" + strRange + "]", oConnect, adOpenKeyset, adLockOptimistic )

when use "FASTEDIT" i got a (big) Input

what do i need to let "FASTEDIT" show "Input" in XBROWSE ... and if possible "write" back into Excel Sheet

[nageswaragunupudi]

there seems to be NO "Microsoft.ACE.OLEDB.12.0" on Microsoft Windows [Version 10.0.25387.1] (latest Canary Version)

Jet OLEDB comes bundled with Windows OS.
If we want to use ACE provider, we need to download and install it.