ADOmate - use OLE-DB datasources via ADO - (BLOBs added)

[Kiffi]

i don't have Access 2007. For accdb you must use another Connectionstring:

Take a look here: http://www.connectionstrings.com/access-2007

Greetings ... Kiffi

[Liqu]

ow yeah, it works great,
thank you very much Kiffi

[Liqu]

Hi, i use this to set and view database value in real time and threaded ( thread safe on )
but i encountered some issues,

the database got delayed about 1-5 seconds before it get the latest value.

here's the code to demonstrate it :

Code: Select all

XIncludeFile "COMatePLUS.pbi"
XIncludeFile "ADOmate.pbi"

Global DB1, DB2

Procedure Qtest_Update(init=0)
  Protected RNUM$
  Repeat 
    RNUM$ = Str(Random(1000))
    Query$ = "UPDATE qtable SET table1='QTEST  "+RNUM$+"' WHERE ID=1"
    ADOmate_DatabaseUpdate(DB1,Query$)
    Debug "SET : "+RNUM$
    Delay(500)
  ForEver
EndProcedure

Procedure Qtest_Select(init=0)
  Repeat 
    ADOmate_DatabaseQuery(DB2,"SELECT table1 FROM QTABLE WHERE ID=1")
    Debug "GET : "+ADOmate_GetDatabaseString(DB2,0)
    Delay(500)
  ForEver
EndProcedure

;// Open DB
DBFile$ = GetCurrentDirectory()+"QueryTest.accdb"
connectionString$ = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBFile$
DB1 = ADOmate_OpenDatabase(connectionString$)
DB2 = ADOmate_OpenDatabase(connectionString$)

;// Open Set and Read Threads
If DB1 And DB2
  CreateThread(@Qtest_Select(),1)
  CreateThread(@Qtest_Update(),1)
EndIf

;// Main Process wait
Repeat
  Delay(1000)
ForEver

and the result :

Code: Select all

SET : 191
GET : QTEST  354
SET : 816
GET : QTEST  354
SET : 907
GET : QTEST  354
SET : 481
GET : QTEST  354
SET : 638
GET : QTEST  354
SET : 861
GET : QTEST  354
SET : 13
GET : QTEST  354
SET : 163
GET : QTEST  354
SET : 774
GET : QTEST  354
SET : 82
GET : QTEST  354
SET : 181
GET : QTEST  82
SET : 167
GET : QTEST  82
SET : 90
GET : QTEST  82
SET : 973
GET : QTEST  82
SET : 599
GET : QTEST  82
SET : 249
GET : QTEST  82

Thank you very much

[srod]

Well, the continual updating, delaying, re-querying, delaying is of course going to add up. ActiveX is not entirely threadsafe anyhow and so you are doing something quite risky here with that code.

I would use ODBC if I were you for this; quicker than ADO.

[Liqu]

@srod

the ODBC got 1 - 5 second at the beginning and 1 sec delay after that.

Code to test

Code: Select all

Global DB1, DB2

Procedure Qtest_Update(init=0)
  Protected RNUM$
  Repeat 
    RNUM$ = Str(Random(1000))
    Query$ = "UPDATE qtable SET table1='QTEST  "+RNUM$+"' WHERE ID=1"
    DatabaseUpdate(DB1,Query$)
    Debug "SET : "+RNUM$
    Delay(500)
  ForEver
EndProcedure

Procedure Qtest_Select(init=0)
  Repeat 
    DatabaseQuery(DB2,"SELECT table1 FROM QTABLE WHERE ID=1")
    NextDatabaseRow(DB2)
    Debug "GET : "+GetDatabaseString(DB2,0)
    Delay(500)
  ForEver
EndProcedure

Procedure Qtest_Open(init=0)
  Protected OpenDB = 2, Result
  Repeat
    Result = OpenDatabase(#PB_Any,"QueryTest","","")
    If Result
      OpenDB + 1
      Debug "DB OPENED : "+Str(OpenDB)
    Else
      Break
    EndIf
  ForEver
EndProcedure

UseODBCDatabase()
;// Open DB

DB1 = OpenDatabase(#PB_Any,"QueryTest","","")
DB2 = OpenDatabase(#PB_Any,"QueryTest","","")

Debug DB1
Debug DB2

;// Open Set and Read Threads
If DB1 And DB2
  CreateThread(@Qtest_Select(),1)
  CreateThread(@Qtest_Update(),1)
  ;CreateThread(@Qtest_Open(),1)
EndIf

;// Main Process wait
Repeat
  Delay(1000)
ForEver

Result

Code: Select all

GET : QTEST  726
SET : 131
SET : 227
GET : QTEST  726
SET : 592
GET : QTEST  726
SET : 423
GET : QTEST  726
SET : 812
GET : QTEST  726
SET : 628
GET : QTEST  726
SET : 138
GET : QTEST  726
GET : QTEST  726
SET : 726
SET : 827
GET : QTEST  726
SET : 747
GET : QTEST  726
SET : 926
GET : QTEST  747
SET : 403
GET : QTEST  747
SET : 731
GET : QTEST  403
SET : 464
GET : QTEST  731
SET : 189
GET : QTEST  464
SET : 803
GET : QTEST  189
SET : 93
GET : QTEST  803
SET : 558
GET : QTEST  93
GET : QTEST  558
SET : 186
SET : 299
GET : QTEST  186
SET : 674
GET : QTEST  299
SET : 895
GET : QTEST  674
SET : 912
GET : QTEST  895
SET : 586
GET : QTEST  912
SET : 664
GET : QTEST  586
SET : 785
GET : QTEST  664
SET : 770
GET : QTEST  785
SET : 112
GET : QTEST  770
SET : 248
GET : QTEST  112
SET : 966
GET : QTEST  248
SET : 331
GET : QTEST  966
SET : 897
GET : QTEST  331
SET : 162
GET : QTEST  897

maybe it is the limit of access db,

PostgreSQL result with delay(5) in the UPDATE and SELECT

Code: Select all

GET : QTEST  172
SET : 165
GET : QTEST  165
SET : 685
GET : QTEST  685
SET : 290
GET : QTEST  290
SET : 864
GET : QTEST  864
SET : 400
GET : QTEST  400
SET : 67
GET : QTEST  67
SET : 680
GET : QTEST  680
SET : 955
GET : QTEST  955
SET : 329
GET : QTEST  329
SET : 321
GET : QTEST  321
SET : 428
GET : QTEST  428
SET : 447
GET : QTEST  447
SET : 8
GET : QTEST  8
SET : 173
GET : QTEST  173
SET : 461
GET : QTEST  461
SET : 481
GET : QTEST  481
SET : 555
GET : QTEST  555
SET : 425
GET : QTEST  425
SET : 231
GET : QTEST  231
SET : 173
GET : QTEST  173
SET : 592
GET : QTEST  592
SET : 77
GET : QTEST  77
SET : 6
GET : QTEST  6
SET : 376
GET : QTEST  376
SET : 456
GET : QTEST  456
SET : 424
GET : QTEST  424
SET : 661
GET : QTEST  661
SET : 657
GET : QTEST  657
SET : 315
GET : QTEST  315
SET : 797
GET : QTEST  797

thank you very much srod.

[Kwai chang caine]

Is it normal, when i read a big EXCEL file of 15 000 lines, ADOMATE is slower against the code of ABBKlaus ?
http://www.purebasic.fr/english/viewtop ... 26#p217726

ADOMATE = 11.9849996567 seconds for 14910 records
ABBKlaus code = 5.5859999657 seconds for 14910 enregistrements

SROD code

Code: Select all

Canal = CreateFile(#PB_Any, FichierCollection)
   
If Canal

 PbIdWin = OpenWindow(#PB_Any, x, y, 300, 100, "Conversion", #PB_Window_ScreenCentered|#PB_Window_BorderLess|#WS_BORDER)
 PbIdLabel = TextGadget(#PB_Any, 0, 0, WindowWidth(PbIdWin), WindowHeight(PbIdWin), "", #PB_Text_Center|#SS_CENTERIMAGE)
 StickyWindow(PbIdWin, #True)
 NumLigne = 0
 MaxColonne = ADOmate_DatabaseColumns(Connection)
 MaxLignes = 0
 
 ; Calculate the number of records
 While ADOmate_IsEOF(Connection) = 0
  MaxLignes + 1
  ADOmate_NextDatabaseRow(Connection)
 Wend
 
 ADOmate_FirstDatabaseRow(Connection)
 
 ; Writing the record  
 While ADOmate_IsEOF(Connection) = 0
  
  NumLigne + 1
  Ligne$ = ""
  
  For i = 0 To MaxColonne
   Ligne$ + ADOmate_GetDatabaseString(Connection, i) + "|"
  Next
  
  SetGadgetText(PbIdLabel, "Conversion de la donnée n° " + Trim(Str(NumLigne)) + "/" + Trim(Str(MaxLignes + 1)))   
  WriteStringN(Canal, Ligne$)
  ADOmate_NextDatabaseRow(Connection)
        
 Wend
 
 ADOmate_FinishDatabaseQuery(Connection) 
 ADOMATE_DeconnecteEXCEL(Connection)
 CloseWindow(PbIdWin)
    
EndIf

CloseFile(Canal)

ABBKlaus code

Code: Select all

Driver$ = "Microsoft Excel Driver (*.xls)"
 DSN$ = "DSN-Connect_Excel"
 Description$ = "TEST Excel"
 DBQ$ = FichierExcel
 UID$ = "Admin" ; Username
 PWD$ = ""      ; Password
  
 Attrib$ = "DSN=" + DSN$ + ";" ; DataSourceName
 Attrib$ + "Description=" + Description$+";" ; Descriptive text
 Attrib$ + "DBQ=" + DBQ$ + ";"                 ; Name of the default database
 
 If MakeConnection(Driver$, Attrib$)
  
  If OpenDatabase(#DataBase, DSN$, UID$, PWD$)
       
   Query$ = "SELECT * FROM [" + NomOnglet + "$]"
 
   If DatabaseQuery(#DataBase, Query$, #PB_Database_DynamicCursor)
       
    Canal = CreateFile(#PB_Any, FichierCollection)
    
    If Canal
    
     ; Compteur de lignes
     MaxLignes = 0
     
     While NextDatabaseRow(#DataBase)
      MaxLignes + 1
     Wend
     
     PbIdWin = OpenWindow(#PB_Any, x, y, 300, 100, "Conversion", #PB_Window_ScreenCentered|#PB_Window_BorderLess|#WS_BORDER)
     PbIdLabel = TextGadget(#PB_Any, 0, 0, WindowWidth(PbIdWin), WindowHeight(PbIdWin), "", #PB_Text_Center|#SS_CENTERIMAGE)
     StickyWindow(PbIdWin, #True)
         
     MaxColumns = DatabaseColumns(#DataBase)
     Fichier$ = ""
     NumLigne = 0
     FirstDatabaseRow(#DataBase)
     
     For i = 0 To MaxColumns - 1
      Ligne$ + GetDatabaseString(#DataBase, i) + "|"
     Next
       
     WriteStringN(Canal, Ligne$)
     
     While NextDatabaseRow(#DataBase)
     
      If MaxColumns
       
       Ligne$ = ""
       NumLigne + 1
       
       For i = 0 To MaxColumns - 1
        Ligne$ + GetDatabaseString(#DataBase, i) + "|"
       Next
       
       SetGadgetText(PbIdLabel, "Conversion de la donnée n° " + Trim(Str(NumLigne)) + "/" + Trim(Str(MaxLignes + 1)))
       WriteStringN(Canal, Ligne$)
       
      EndIf
  
     Wend
         
     CloseWindow(PbIdWin)
     CloseFile(Canal)
     MessageRequester(#ERC_NomLogiciel + " " + #ERC_VersionLogiciel, "Le fichier ''" + GetFilePart(FichierExcel, #PB_FileSystem_NoExtension) + "'' vient d'être importé avec succés.")
     
    Else
    
     MessageRequester(#ERC_NomLogiciel + " " + #ERC_VersionLogiciel, "Le fichier ''" + FichierCollection + "'' ne peut être créé.")
     
    EndIf 
  
   Else
 
    MessageRequester(#ERC_NomLogiciel + " " + #ERC_VersionLogiciel, DatabaseError())
 
   EndIf
   
   CloseDatabase(#DataBase)
 
  EndIf
  
  DeleteConnection(Driver$, DSN$)
  
 Else
   
  MessageRequester(#ERC_NomLogiciel + " " + #ERC_VersionLogiciel, GetSQLError())
 
 EndIf

[srod]

In general ADO will be slower than ODBC, so, yes, that could well be entirely expected.

If you have the choice then I would opt for ODBC over ADO.

[Kwai chang caine]

Thanks for your answer