ADO RDD xHarbour

[lucasdebeltran]

Antonio,

Sorry, I don´t understand.

We have to check it at FUNCTION ADOSTRUCTTOSQL().

If the programmer has indicated in aStruct the autoincremental field, it´s perfect, nothing to be done.

Otherwhise:

Code: Select all  Expand view  RUN

if aAcan( aStruct, ADODEFLDRECNO() ) = 0
  AIns( aCols, 1, {  ADODEFLDRECNO(), '+', 10, 0 }, .t. )
endif
 

That´s it.

[AHF]

Lucas,

This is in ado_open() for next fields

Code: Select all  Expand view  RUN

      IF ADO_FIELDSTRUCT( oRecordSet, n-1 )[2] = "+" //OUR FIELD RECNO CAN ONLY BE ONE PER TABLE ITS ALREADY DEFINED USE IT
         aWAData[WA_FIELDRECNO]:=  n - 1
         //IF IT SUPPORTS SEEK WE WILL SEEK IT ISNTEAD OF FIND IT
         IF !oRecordSet:Supports(adIndex) .OR. !oRecordSet:Supports(adSeek)
            //OTHERWISE LETS USE ADO INDEX PROP TO SPEED UP
            IF  oRecordSet:CursorLocation = adUseClient 
                oRecordSet:Fields( aWAData[WA_FIELDRECNO] ):Properties():Item("Optimize"):Value := 1
            ENDIF
           
         ENDIF 
         
      ENDIF

 

Outside the next:

Code: Select all  Expand view  RUN

IF EMPTY(aWAData[WA_FIELDRECNO]) //DONT HAVE ANY AUTO INC FIELD USE AS RECNO CREATE
   
      IF ! EMPTY(ADO_GET_FIELD_RECNO(  aWAData[ WA_TABLENAME ] ))
     
         cName := "ALTER TABLE "+ aWAData[ WA_TABLENAME ]+" ADD "+ ADO_GET_FIELD_RECNO(  aWAData[ WA_TABLENAME ] )
         
         cName += HB_DECODE( aWAData[ WA_ENGINE ],  "DBASE", "","ACCESS"," AUTOINCREMENT","MSSQL"," INT IDENTITY( 1, 1 )",;
                     "MYSQL", " INT AUTO_INCREMENT","ORACLE"," INT","SQLITE"," INTEGER","FOXPRO"," NUMERIC",;
                     "POSTGRE"," SERIAL","INFORMIX"," SERIAL","ANYWHERE"," INTEGER IDENTITY","ADS"," AUTOINC", )
         
         IF aWAData[ WA_ENGINE ] <> "ADS"
            cName  += ", ADD PRIMARY KEY "+ADO_GET_FIELD_RECNO(  aWAData[ WA_TABLENAME ])
         ENDIF 
         
         TRY
         
            aWAData[ WA_CONNECTION ]:execute(cName)
         
            aField[ UR_FI_NAME ]    := ADO_GET_FIELD_RECNO(  aWAData[ WA_TABLENAME ])
            aField[ UR_FI_TYPE ]    := HB_FT_AUTOINC
            aField[ UR_FI_TYPEEXT ] := 0
            aField[ UR_FI_LEN ]     := 10
            aField[ UR_FI_DEC ]     := 0
         
            UR_SUPER_ADDFIELD( nWA, aField )
           
            aWAData[WA_FIELDRECNO] := ADO_GET_FIELD_RECNO(  aWAData[ WA_TABLENAME ])
           
            IF !oRecordSet:Supports(adIndex) .OR. !oRecordSet:Supports(adSeek)
               //OTHERWISE LETS USE ADO INDEX PROP TO SPEED UP
               IF  oRecordSet:CursorLocation = adUseClient   
                   oRecordSet:Fields( aWAData[WA_FIELDRECNO] ):Properties():Item("Optimize"):Value := 1
               ENDIF
            ENDIF   

         CATCH
            aWAData[WA_FIELDRECNO] := NIL
           // ADOSHOWERROR(aWAData[ WA_CONNECTION ])
         END
         
      ENDIF
     
   ENDIF
 

[AHF]

adordd its working quite well but still some adjustments to do. (MySql)

Our client is working with the app with adordd in MySql in parallel with the same app with ADS comparing all transactions and behavior between the two. He s quit happy. (2nd week trials on real data)

The app its more than 90% converted ( tested ) and we only needed to change till now a couple of index create expressions and like this:

Code: Select all  Expand view  RUN

     IF RDDNAME() == "ADORDD"
        cexpress := "CONVERT(ano,UNSIGNED)*100+CONVERT(sementrega,UNSIGNED) >=" +semanaini+" and "+ ;
                    "CONVERT(ano,UNSIGNED)*100+CONVERT(sementrega,UNSIGNED) <= "+semanafim+ ;
                    " and nrfactur = '         '"
     ELSE                  
     cexpress := "val(ano)*100+val(sementrega) >=val('"+str(semanaini)+"') .and."+ ;
               "val(ano)*100+val(sementrega) <= val('"+str(semanafim)+"')"+ ;
               ".and. nrfactur == space(10)"
     ENDIF
 

The only true complaint till now is the speed problem with dbeval() mainly in SUM operations. Its really very
slow and we cant find out why! Antonio any ideas?

Meanwhile we found that in certain SELECT.. WHERE... field properties become inaccessible even when recordset its not empty. Add records its not possible.
Fortunately we only need it for autoinc property and we found a workaround.
Maybe Mr.Rao knows the reason for it.

Our client has its own filter routines either already through SELECTS or by filing arrays so its not important to us nevertheless because will certainly be important for the market in general Im checking with Lucas a workaround to make it 100% compatible with (x)Harbour syntax.
Any contribution is most welcome!

Now it seems that we have more 2 clients interested in porting apps to SQL. (one MySql other Oracle)

Please note that adordd its only being tested with ADS oledb, ACCESS and MySql.

I expect to post a new version today's evening or tomorrow morning.

Letting (x)Harbour to navigate freely around all data.

[lucasdebeltran]

Antonio,

I am also having trouble with MySQL.

For somehow, at ADOFINDREC() oRecordSet:AbsolutePosition := IF( oRS:AbsolutePosition == adPosEOF, oRS:RecordCount() + 1, oRS:AbsolutePosition )

is set to -1, and it gives an error.

oRecordSet:AbsolutePosition can´t be minor than 0.

Do yoy know why?

Thank you.

[AHF]

Lucas,

You don't have recno field and your cursor doesn't support bookmarks.

Please note that although the code foresee a situation where you don't have hbrenco results would be unpredictable in some circumstances.

If you are using version sent by last email when you opened this table it should have created the hbrecno field . Please check.

[lucasdebeltran]

Antonio,

Yes, I do have HBRECNO and I am using your code.

In fact, at ADO_OPEN( nWA, aOpenInfo ), you added :

IF EMPTY(aWAData[WA_FIELDRECNO]) //DONT HAVE ANY AUTO INC FIELD USE AS RECNO CREATE
....


Well, despite I have HBRECNO, aWAData[WA_FIELDRECNO is giving 0.

Also, the sintax for ALTER TABLE is failing too.

[AHF]

Lucas,

If it is the first field can be 0!

Might be a bug.
In all our tables its always the last field and I suspect that with zero IF EMPTY(aWAData[WA_FIELDRECNO]) rerturns .t..

Please try delete hbrecno and add it to the end of all fields.

[lucasdebeltran]

Antonio,

Another bug in ADO_APPEND() and MySQL.

Dates are not saved into aVals, so oRs:AddNew( aCols, aVals ) fails.

Also maybe a problem with empty dates?.

[lucasdebeltran]

Antonio,

Please try delete hbrecno and add it to the end of all fields.

Yes, in that case works, but I think HBRECNO should be the first field, also as the PRIMARY KEY.

Thank you.

[AHF]

Lucas,

Another bug in ADO_APPEND() and MySQL.

Dates are not saved into aVals, so oRs:AddNew( aCols, aVals ) fails.

Also maybe a problem with empty dates?.

Here working ok.

Code: Select all  Expand view  RUN

append blank
replace datefield with date()
 

Can you post a example?

[lucasdebeltran]

Antonio,

It´s with MySQL.

At ADO_APPEND, i checked aVals and date fields very empty.

Are you testing with Access or MySQL?.

With ACCESS Works fine.

[AHF]

Lucas,

MySql and its working ok.

[AHF]

Antonio,

It´s with MySQL.

At ADO_APPEND, i checked aVals and date fields very empty.

Are you testing with Access or MySQL?.

With ACCESS Works fine.

This might be due to not nullable fields defined in MySql table structure.

[AHF]

Lucas,

Basic :filter ready.

Please note that expressions like '.. month(fielddate) =' are invalid.
We cant use functions in field expressions.

The only way to use standard filter expressions is like in the mail Ive sent you.
I don't see any other way.

Concerning add column in first position to use as recno please note that as far as I discover that is only possible in MySql and Oracle other RMBDs do not allow it.

Testing EMPTY( ..[ WA_FIELDRECNO] ) changed to !VALTYPE( ..[ WA_FIELDRECNO] ) == "U" because if field in first column value would be zero and empty true.

[lucasdebeltran]

Antonio,

No, the replace is as follows:

REPLACE FIELD->FECHA WITH Date().

Here is MySQL capture:



About filtering, I don´t understand. ADORDD should be able to support filters like those ones:

Code: Select all  Expand view  RUN

  cCondicion := "Dtoc( FIELD->FECHA ) >= " + chr( 34 ) + Dtoc( dFecha1 ) + chr( 34 ) + ;
                " .AND. Dtoc( FIELD->FECHA ) <= " + chr( 34 ) + Dtoc( dFecha2 ) + chr( 34 )



  SET FILTER TO alltrim(  AUXILIAR->RELACION  ) == alltrim(  STR( oData:Codigo ))


  cCondicion := "CONTA->CONCEPTO = " + chr( 34 ) + alltrim( cValToChar( cCuentaoConcepto ) ) + chr( 34 )

 

Maybe Antonio, Nages, Enrico or Rick can help us to achieve it.

Thank you.


I think it´s possible to translate them to ADO oRs:Filter().