Fivewin and ADO - FiveTech Support Forums

Board index FiveWin for Harbour/xHarbour Fivewin and ADO

Page: 1 2 3 (37 posts)

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Fivewin and ADO
Posted: Fri Jun 02, 2023 02:13 AM

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 :?:

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Fri Jun 02, 2023 03:01 AM

All versions should work.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Fri Jun 02, 2023 03:01 AM

We tried to make FWH ADO functions work with all versions and keep updating.

Will you please try and let us know?

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Mon Jun 05, 2023 05:00 AM

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 (fw): Select all Collapse

// 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 (fw): Select all Collapse

FW_OpenADOExcelSheet( cFile, cSheet, cRange )

Microsoft SQL Server

Code (fw): Select all Collapse

   // 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.

Regards

G. N. Rao.

Hyderabad, India

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: Fivewin and ADO
Posted: Wed Jun 07, 2023 04:08 PM

hi,

Ok thx i will try

greeting,

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: Fivewin and ADO
Posted: Wed Jun 07, 2023 10:21 PM

hi,

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

these ConnectionString are from ADORDD

Code (fw): Select all Collapse

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 :?:

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Wed Jun 07, 2023 11:56 PM

can i use these ConnectionString with Fivewin

Yes

Code (fw): Select all Collapse

oCn := FW_OpenAdoConnection( cYourConnectionString, .t. )

Syntax:

Code (fw): Select all Collapse

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

FW Connection Spec is an array:

Code (fw): Select all Collapse

{ cRDBMS, cServer, cDB, cUser, cPassword}

Example:

Code (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: Fivewin and ADO
Posted: Thu Jun 08, 2023 04:39 AM

hi,

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

i have try

Code (fw): Select all Collapse

   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

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Thu Jun 08, 2023 08:55 AM

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 (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Thu Jun 08, 2023 11:21 AM

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 (fw): Select all Collapse

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 (fw): Select all Collapse

XBROWSER GetExcelRange( cExcelBookNameFullPath )

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

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Thu Jun 08, 2023 02:30 PM

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

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

Code (fw): Select all Collapse

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

Substitute the above code with:

Code (fw): Select all Collapse

   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

Regards

G. N. Rao.

Hyderabad, India

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: Fivewin and ADO
Posted: Thu Jun 08, 2023 07:14 PM

hi,

thx for Answer

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

greeting,

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: Fivewin and ADO
Posted: Thu Jun 08, 2023 08:21 PM

hi,

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

Code (fw): Select all Collapse

      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]

greeting,

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: Fivewin and ADO
Posted: Thu Jun 08, 2023 11:15 PM

hi,

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

Code (fw): Select all Collapse

     @  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 (fw): Select all Collapse

      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 :?:

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Fivewin and ADO
Posted: Fri Jun 09, 2023 02:38 AM

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.

Regards

G. N. Rao.

Hyderabad, India

Page: 1 2 3 (37 posts)