FiveTech Software tech support forums

www.FiveTechSoft.com
https://forums.fivetechsupport.com/

Fivewin and ADO

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=43275

Page 1 of 3

Fivewin and ADO

PostPosted: Fri Jun 02, 2023 2:13 am
by 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 :?:

Re: Fivewin and ADO

PostPosted: Fri Jun 02, 2023 3:01 am
by nageswaragunupudi
All versions should work.

Re: Fivewin and ADO

PostPosted: Fri Jun 02, 2023 3:01 am
by nageswaragunupudi
We tried to make FWH ADO functions work with all versions and keep updating.
Will you please try and let us know?

Re: Fivewin and ADO

PostPosted: Mon Jun 05, 2023 5:00 am
by 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

// 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
FW_OpenADOExcelSheet( cFile, cSheet, cRange )


Microsoft SQL Server
Code: Select all  Expand view
  // 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.

Re: Fivewin and ADO

PostPosted: Wed Jun 07, 2023 4:08 pm
by Jimmy
hi,

Ok thx i will try

Re: Fivewin and ADO

PostPosted: Wed Jun 07, 2023 10:21 pm
by Jimmy
hi,

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

these ConnectionString are from ADORDD
Code: Select all  Expand view
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 :?:

Re: Fivewin and ADO

PostPosted: Wed Jun 07, 2023 11:56 pm
by nageswaragunupudi
can i use these ConnectionString with Fivewin


Yes

Code: Select all  Expand view
oCn := FW_OpenAdoConnection( cYourConnectionString, .t. )


Syntax:
Code: Select all  Expand view
FW_OpenAdoConnection( cFWConnectSpec[or]cYourConnectionString, [lShowError],[@oError]) --> oCn
// oCn is NIL if failed
 


FW Connection Spec is an array:
Code: Select all  Expand view
{ cRDBMS, cServer, cDB, cUser, cPassword}


Example:
Code: Select all  Expand view
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.

Re: Fivewin and ADO

PostPosted: Thu Jun 08, 2023 4:39 am
by 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
  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

Re: Fivewin and ADO

PostPosted: Thu Jun 08, 2023 8:55 am
by 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
  oRs   := FW_OpenADOExcelSheet( "customer.xlsx" )
   XBROWSER oRs SETUP ( oBrw:AutoFit() )


Image

Re: Fivewin and ADO

PostPosted: Thu Jun 08, 2023 11:21 am
by 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
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
XBROWSER GetExcelRange( cExcelBookNameFullPath )


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

Re: Fivewin and ADO

PostPosted: Thu Jun 08, 2023 2:30 pm
by 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
  if ValType( uData ) == "H" .or. ( ValType( uData ) == "O" .and. uData:IsKindOf( "TLINKLIST" ) )


Substitute the above code with:
Code: Select all  Expand view
  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

Re: Fivewin and ADO

PostPosted: Thu Jun 08, 2023 7:14 pm
by Jimmy
hi,

thx for Answer

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

Re: Fivewin and ADO

PostPosted: Thu Jun 08, 2023 8:21 pm
by Jimmy
hi,

there seems to be NO "Microsoft.ACE.OLEDB.12.0" on Microsoft Windows [Version 10.0.25387.1] (latest Canary Version)
Image
Code: Select all  Expand view

      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]

Re: Fivewin and ADO

PostPosted: Thu Jun 08, 2023 11:15 pm
by Jimmy
hi,

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

Code: Select all  Expand view
    @  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
     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
Image
what do i need to let "FASTEDIT" show "Input" in XBROWSE ... and if possible "write" back into Excel Sheet :?:

Re: Fivewin and ADO

PostPosted: Fri Jun 09, 2023 2:38 am
by 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.
All times are UTC
Page 1 of 3
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/