ADORDD

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
AUGE_OHR
Posts: 947
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 147 times
Been thanked: 247 times

ADORDD

Post by AUGE_OHR »

hi,

under c:\hmg.3.4.4\HARBOUR\addons\rddado\ i found ADORDD Source

Question : is it still valid :?:

does someone have try ADORDD with *.XLSx :?:
have fun
Jimmy

User avatar
danielmaximiliano
Posts: 2320
Joined: Fri Apr 09, 2010 4:53 pm
Location: Argentina
Has thanked: 374 times
Been thanked: 74 times
Contact:

Post by danielmaximiliano »

In C:\Harbour\extras\rddado , adordd.prg, STATIC FUNCTION ADO_OPEN( nWA, aOpenInfo ), line 292

Code: Select all

  CASE Lower( Right( aOpenInfo[ UR_OI_NAME ], 4 ) ) == ".xls"
         aWAData[ WA_CONNECTION ]:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + aOpenInfo[ UR_OI_NAME ] + ";Extended Properties='Excel 8.0;HDR=YES';Persist Security Info=False" )
connection with .xls files is generated, a syntax addition would be needed to see if it is likely to use this extra harbour library
*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
.·`. Harbour/HMG : It's magic !
(¸.·``··*

Saludos / Regards
DaNiElMaXiMiLiAnO

Whatsapp. := +54901169026142
Telegram Name := DaNiElMaXiMiLiAnO

User avatar
AUGE_OHR
Posts: 947
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 147 times
Been thanked: 247 times

Post by AUGE_OHR »

hi,

i saw that file but before use it i want to ask if it is still valid ... not like PGRDD :roll:

now Demo use existing Data but i try to create a XLSx and write Data from DBF using ADO.
i try CREATE TABLE but this seem not to work with Excel :(
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 947
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 147 times
Been thanked: 247 times

Post by AUGE_OHR »

hi,

i found out i need to create a Table in Catalog before fill it.

Code: Select all

   oConnect := CreateObject( "ADODB.Connection" )
   oCatalog := CreateObject( "ADOX.Catalog" )
   oTable   := CreateObject( "ADOX.Table" )
   FOR ii := 1 TO iMax
      cField := aStructure[ ii ] [ DBS_NAME ]
      cType  := aStructure[ ii ] [ DBS_TYPE ]

      oColumn  := CreateObject( "ADOX.Column")
      oColumn:Name := cField

      DO CASE
         CASE cType = "C" ; oColumn:Type := adVarWChar
         CASE cType = "M" ; oColumn:Type := adLongVarWChar
         CASE cType = "N" ; oColumn:Type := adDouble
         CASE cType = "D" ; oColumn:Type := adDate
         CASE cType = "L" ; oColumn:Type := adBoolean
      ENDCASE

      oTable:Columns:Append(oColumn)
   NEXT
   oCatalog:Tables:Append(oTable)
than i try use INSERT INTO and i got a *.XLSx which Excel can open :)
CREXLSX.ZIP
(1.45 KiB) Downloaded 10 times
but i can´t open same *.XLSx with ADO again ...
AdoXLSx.jpg
AdoXLSx.jpg (27.74 KiB) Viewed 301 times
so something still not working :(
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 947
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 147 times
Been thanked: 247 times

Post by AUGE_OHR »

hi,

i have try ADO Recordset instead of INSERT INTO

Code: Select all

   objRS := CreateObject( "ADODB.Recordset" )
   objRS:Open( "Select * from [Sheet$]", oConnect, adOpenKeyset, adLockOptimistic )
   DO WHILE !EOF()
      aField := {}
      aValue := {}
      ii := 1
      FOR ii := 1 TO iMax
         cField := aStructure[ ii ] [ DBS_NAME ]
         cType  := aStructure[ ii ] [ DBS_TYPE ]
         nPosi  := FIELDPOS( cField )
         xValue := FIELDGET( nPosi )

         AADD(aField,cField)
         AADD(aValue,xValue)
      NEXT
      objRS:AddNew(aField,aValue)
      objRS:Update()
      SKIP
   ENDDO
   objRS:Close()
   objRS := NIL
XLSx is create and Excel can open it ... but same when i try to read it with ADO :evil:

---

while both seems to work i guess Problem is before ... how to create Table ... hm
who have a Idea :idea:

p.s. it work also with 64 Bit
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 947
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 147 times
Been thanked: 247 times

Post by AUGE_OHR »

hi,

got it :D

instead of

Code: Select all

      cSql := "INSERT INTO [Sheet1$] "+ cSelect + "VALUES ( "
use

Code: Select all

      cSql := "INSERT INTO [Sheet1] "+ cSelect + "VALUES ( "
it was the "$" which was wrong ( have use it when read XLSx )
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 947
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 147 times
Been thanked: 247 times

Post by AUGE_OHR »

hi,

here working Sample
CREXLSX2.ZIP
(11.85 KiB) Downloaded 13 times
Source include both Version : INSERT INTO or Recordset

Recordset work with "Update", not with "UpdateBatch" yet
INSERT INTO have Problem with DATE Format

Code: Select all

   xValue := DTOC( xValue )
   xValue := DTOS( xValue ) + "000000"
   xValue := HB_STOT( DTOS( xValue ) + "000000" )
none of them seems to work. :(
if someone find right Syntax please post it
have fun
Jimmy

User avatar
bpd2000
Posts: 1160
Joined: Sat Sep 10, 2011 4:07 am
Location: India
Has thanked: 248 times
Been thanked: 142 times

Post by bpd2000 »

Hi Jimmy
Error while I check your code
missing, but unknown function(s): SP_ADBFSTRUCT(),
ONDUMMY(), SETCURSORWAIT(), CALCPOS()
BPD
Convert Dream into Reality through HMG

User avatar
AUGE_OHR
Posts: 947
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 147 times
Been thanked: 247 times

Post by AUGE_OHR »

bpd2000 wrote:
Sat Aug 01, 2020 5:22 am
Error while I check your code
missing, but unknown function(s): SP_ADBFSTRUCT(),
ONDUMMY(), SETCURSORWAIT(), CALCPOS()
SP_ADBFSTRUCT() -> DBFSTRUCT()
ONDUMMY() is only for debug, delete it

Code: Select all

PROCEDURE SetCursorWait( cForm, lOn )
LOCAL nHwnd
   DEFAULT lOn TO .F.
   nHwnd := GetformHandle( cForm )
   IF lOn = .T.
      SetWindowCursor( nHwnd, IDC_WAIT )
   ELSE
      SetWindowCursor( nHwnd, IDC_ARROW )
   ENDIF
RETURN

Code: Select all

FUNCTION CalcPos( nValue, iMax )
LOCAL nRet   := 0
LOCAL nEvery
LOCAL xScale

   IF iMax < 100
      nEvery := 100 / iMax
      nRet := nValue * nEvery
   ELSE
      nEvery := INT( iMax / 100 )
      xScale := iMax * nEvery                                         // Scale to 100%
      nRet := iMax / xScale * nValue
   ENDIF
RETURN ROUND( nRet, 0 )
have fun
Jimmy

User avatar
bpd2000
Posts: 1160
Joined: Sat Sep 10, 2011 4:07 am
Location: India
Has thanked: 248 times
Been thanked: 142 times

Post by bpd2000 »

Hi
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
So convert date to serial and store to excel

check code :

Code: Select all

#include "hmg.ch"

Function main()
 
   SET DATE BRITI
   SET CENT ON

msginfo( serial2dt(39448),"Date serial 44180 to date"+space(10))
msginfo( dt2serial(date()),"Today's Date Serial"+space(10))

return


Function serial2dt(xdt)
   Local x_dateorigin, x_datenew
   SET DATE BRITI
   SET CENT ON
   x_dateorigin := ctod("01/01/1900")
   x_datenew :=( x_dateorigin + xdt ) - 2 
Return dtoc( x_datenew )
   

Function dt2serial(xdt)
   Local x_dateorigin, x_datenew
   SET DATE BRITI
   SET CENT ON
   x_dateorigin := ctod("01/01/1900")
   x_datenew := (    xdt -x_dateorigin ) + 2 
Return  x_datenew 
BPD
Convert Dream into Reality through HMG

Post Reply