Page 1 of 3
Fivewin and ADO
Posted: 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/downloa ... x?id=54920
https://www.microsoft.com/de-de/downloa ... x?id=54920
Microsoft Access Database Engine 2010 Redistributable
https://www.microsoft.com/en-us/downloa ... n&id=13255
https://www.microsoft.com/de-de/downloa ... n&id=13255
but which ADO Version is to use with Fivewin 22/07
Re: Fivewin and ADO
Posted: Fri Jun 02, 2023 3:01 am
by nageswaragunupudi
All versions should work.
Re: Fivewin and ADO
Posted: 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
Posted: 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
// 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:
Microsoft SQL Server
Code: Select all | Expand
// 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
Posted: Wed Jun 07, 2023 4:08 pm
by Jimmy
hi,
Ok thx i will try
Re: Fivewin and ADO
Posted: 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
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
Posted: Wed Jun 07, 2023 11:56 pm
by nageswaragunupudi
can i use these ConnectionString with Fivewin
Yes
Code: Select all | Expand
oCn := FW_OpenAdoConnection( cYourConnectionString, .t. )
Syntax:
Code: Select all | Expand
FW_OpenAdoConnection( cFWConnectSpec[or]cYourConnectionString, [lShowError],[@oError]) --> oCn
// oCn is NIL if failed
FW Connection Spec is an array:
Example:
Code: Select all | Expand
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
Posted: 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
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
Posted: 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
oRs := FW_OpenADOExcelSheet( "customer.xlsx" )
XBROWSER oRs SETUP ( oBrw:AutoFit() )
Re: Fivewin and ADO
Posted: 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
cSheet defaults to ActiveSheet and cRange to UsedRange.
Please try with any other excel book on your disk, using this single line code:
Function FW_OpenADOExcelSheet() uses ADO using Jet or ACE OleDB provider
and
Function GetExcelRange() uses Excel Ole.
Re: Fivewin and ADO
Posted: 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
if ValType( uData ) == "H" .or. ( ValType( uData ) == "O" .and. uData:IsKindOf( "TLINKLIST" ) )
Substitute the above code with:
Code: Select all | Expand
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
Posted: 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
Posted: 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)
Code: Select all | Expand
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
Posted: Thu Jun 08, 2023 11:15 pm
by Jimmy
hi,
have now a "normal" XBROWSE and can "show" Excel Sheet
Code: Select all | Expand
@ 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
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
Re: Fivewin and ADO
Posted: 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.