FWAdoSaveRecord - Query is too complex - Help

[MFarias]

I'm testing and implementing some test routines of the ado and the functions that I am at FiveWin the adofuncs.prg but I found this limitation due to the use the function: FWAdoSaveRecord, not managing to use it was debugging and found that return on the last try / catch:

Code: Select all

function FWAdoSaveRecord( oRS, aRecord, nRecNo )

   local n, oField, uVal, uNew
   local lUpdated := .f., lSaved   := .f.

   if ! Empty( nRecNo ) .and. oRs:BookMark != nRecNo
      oRs:BookMark = nRecNo
   endif

   for n = 1 to oRS:Fields:Count
      oField = oRs:Fields( n - 1 )
        if FW_AdoFieldUpdateable( oRs, oField ) == .f.
         LOOP
      endif
      uVal   = oField:Value
      uNew   = aRecord[ n, 2 ]
      if Empty( uVal ) .and. Empty( uNew )
         LOOP
      endif

#ifdef __XHARBOUR__
      if Empty( uNew ) .and. lAnd( oField:Attributes, 0x20 ) // nullable field
         oField:Value = VTWrapper( 1 )  // assigning NULL
         LOOP
      endif
#endif
      // assume that uNew is not NIL .and. is correct data type
      if ValType( uNew ) == 'C'
         if oField:Type == adChar // Fixed width
            uNew = PadR( uNew, oField:DefinedSize )
         else
            uNew = Left( Trim( uNew ), oField:DefinedSize )
         endif
      endif

      if ! ( ValType( uVal ) == ValType( uNew ) .and. uVal == uNew )
         if AScan( { adBinary, adVarBinary, adLongVarBinary }, oField:Type ) != 0
            uNew = HB_StrToHex( uNew )
         endif

#ifndef __XHARBOUR__
         // Harbour has problem in assigning Empty Dates
         //
         if ValType( uVal ) $ 'DT' .and. Empty( uNew ) .and. ;
            ! ( FW_RDBMSName( oRs:ActiveConnection ) == "MSACCESS" )
            uNew = 0
         endif
#endif

/*
         if ValType( uNew ) == "L"
            uNew        = If( uNew, 1, 0 )
         endif
*/

         TRY
            oField:Value = uNew
            lUpdated     = .T.
           catch oError
            ? oField:Name, uNew
         END
      endif
   next

   if lUpdated
      TRY
            oRS:Update()
         lSaved   := .t.
      catch oError
       // here
        MsgInfo( oError:Description )
         oRS:CancelUpdate()
      END
   endif

return lSaved
 

the query I'm using is this: select * from customers
it has 183 columns
source code follows the opening:

Code: Select all

Function SqlQuery( cSqlSintax )
   local uRet    := {} // Retorno com Registros
   local  nAt  // Objetos de controle para o ODBC e o Registro
    Local aSqlDados:={}, lInstancia := .f. , lExecute:=.f. , oResultSetAnterior := nil
    hINI := Time()
    Try
     aSqlDados := (HB_ATokens(strtran(strtran(oSqlConexao:OleValue,"]"),"["),";"))
     if len(aSqlDados) > 0
        if !(aSqlDados[3] == "Data Source="+alltrim(cDiretorioDeDados))
          lInstancia:=.t.
        endif
     endif
    catch
     public oSqlConexao
     lInstancia:=.t.
    end
    if lInstancia
        oSqlConexao := FW_OpenAdoConnection( [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=]+cDiretorioDeDados+[ ;Extended Properties=dBASE IV;User ID=Admin;Password=] ,.t.)
    endif

   cSql     := Upper( alltrim(cSqlSintax) )

   lExecute  := !( LEFT( cSql, 7 ) == "SELECT " )
    if oSqlConexao != nil
       if lExecute
          TRY
             uRet := oSqlConexao:Execute( cSql )
          CATCH
          END
       else
          oRecordSetConsulta      := FW_OpenRecordSet( oSqlConexao, cSql )
          if oResultSetAnterior == nil
            oResultSetAnterior:= oRecordSetConsulta
          endif
            if oRecordSetConsulta != nil
                if oRecordSetConsulta:RecordCount() > 0
                 oBrowserDeDados:SetAdo( oRecordSetConsulta, .t., .t.) // ADO object
                else
                   oRecordSetConsulta:=oResultSetAnterior
                   oBrowserDeDados:SetAdo( oRecordSetConsulta, .t., .t.) // ADO object
                   oResultSetAnterior := nil
                endif
            DEFINE MSGITEM oMsgRecNo OF oMsgBar ;
            PROMPT "Reg.Atual: " + ;
            AllTrim( Str( If( oRecordSetConsulta:AbsolutePosition == -3, oRecordSetConsulta:RecordCount() + 1,;
                   oRecordSetConsulta:AbsolutePosition ) ) ) + " / " + ;
            AllTrim( Str( oRecordSetConsulta:RecordCount() ) ) ;
            SIZE 150

            DEFINE MSGITEM oMsgTagName OF oMsgBar ;
            PROMPT FWString( "Ordered by" ) + ": " + If( Empty( oRecordSetConsulta:Sort ),;
                 FWString( "natural order" ), oRecordSetConsulta:Sort ) ;
            SIZE 150
                oMsgBar:settext("Tempo da Ultima Consulta: "+ ElapTime(hini,time()) )
                oMsgBar:Refresh()
                oBrowserDeDados:CreateFromCode()
            oJanelaPrincipal:oClient  := oBrowserDeDados
                oBrowserDeDados:Refresh()
                oJanelaPrincipal:Resize()
                oJanelaPrincipal:Refresh()
                Sysrefresh()
            endif
        endif
   endif
   return uRet

 

please help me !!!

[nageswaragunupudi]

You want to test accessing DBF tables using ADO. I advise much simpler approach.

For testing we shall use DBF tables in "c:\fwh\samples\" folder.

Step-1: Open ADO connection to folder.

Code: Select all

oCn := FW_OpenAdoConnection( "c:\fwh\sampples\" )

Step-2: View the tables in the folder.

Code: Select all

XBROWSER FW_AdoTables( oCn ) TITLE "TABLES"
 

Step-3: Now let us open Table SALES.DBF. Does not matter if the table has only a few fields or 200 fields. Procedure is the same.

Code: Select all

oRs      := FW_OpenRecordSet( oCn, "SALES" )
 

We can also use FW_OpenRecordSet( oCn, "SELECT * FROM SALES" ). Result is the same.
Step-4: Let us now Quickly view the contents of the table SALES

Code: Select all

XBROWSER oRs AUTOSORT TITLE "SALES"
 

Step-5:
Now let us prepare a self-contained sample program in few quick steps to
- open the connectio
- open the table
- create XBrowse with facilities for:
-- Auto totalling of numeric columns
-- Autosort
-- Incremental Seek
-- Inline Cell Edit
-- Dialogs for Add, Edit
-- Delete
-- Report printing
and MsgBar displaying the current Sort order.

Code: Select all

#include "fivewin.ch"
#include "adodef.ch"
#include "xbrowse.ch"

function Main()

   local oCn, oRs
   local oWnd, oFont, oBrw, oMiSort

   SET DATE ITALIAN
   SET CENTURY ON

   oCn      := FW_OpenAdoConnection( "c:\fwh\samples\" )
   oRs      := FW_OpenRecordSet( oCn, "SALES" )

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE WINDOW oWnd TITLE "DBF TABLE THROUGH ADO"
   oWnd:SetFont( oFont )

   DEFINE BUTTONBAR oWnd:oBar SIZE 100,32 2010
   DEFINE BUTTON OF oWnd:oBar PROMPT "Add"    ACTION oBrw:Edit( .t. )
   DEFINE BUTTON OF oWnd:oBar PROMPT "Edit"   ACTION oBrw:Edit()
   DEFINE BUTTON OF oWnd:oBar PROMPT "Delete" ACTION oBrw:Delete()
   DEFINE BUTTON OF oWnd:oBar PROMPT "Report" ACTION oBrw:Report()
   DEFINE BUTTON OF oWnd:oBar PROMPT "Close"  ACTION oWnd:End()

   SET MESSAGE OF oWnd TO "" 2010
   DEFINE MSGITEM oMiSort PROMPT If( Empty( oRs:Sort ), "NATURAL", oRs:Sort ) ;
      SIZE 200
   oMiSort:bMsg   := { || If( Empty( oRs:Sort ), "NATURAL", oRs:Sort ) }

   @ 0,0 XBROWSE oBrw OF oWnd DATASOURCE oRs ;
      AUTOCOLS AUTOSORT CELL LINES NOBORDER FOOTERS

   AEval( oBrw:aCols, { |o| If( o:cDataType == 'N', o:nFooterType := AGGR_SUM, nil ) } )
   WITH OBJECT oBrw
      :nEditTypes       := EDIT_GET
      :bLClickHeaders   := { || oMiSort:Refresh() }
      :MakeTotals()
      //
      :CreateFromCode()
   END
   oWnd:oClient   := oBrw
   ACTIVATE WINDOW oWnd
   RELEASE FONT oFont

   oRs:Close()
   oCn:Close()

return nil

[nageswaragunupudi]

FW_AdoLoadRecord() and FW_AdoSaveRecord() are primarily intended to read field values of recordset into an array and after editing the array contents, to save to the recordset.

We advise usage of TDataRow() instead of the above methods. Usage of TDataRow simplifies the process and also is safe.

In the above example, XBrowse:Edit() uses TDataRow transparently.

[MFarias]

Thanks, I had already seen these examples in the samples folder, the most viable solution for the error is for tables over 50 fields have an index. When I created worked perfectly

Code: Select all

// isto em sql 
CREATE INDEX pkclientes ON CLIENTES(CODICLI) with PRIMARY
 

[Kleyber]

Nagesh,

Just for curiosity... since what version we have these functions working in FWH?

[nageswaragunupudi]

Mr Klyber

Most important ADO functions have been there since 2011. We have been adding and improving since then.

TDatarow class was first included in May 2013.

Full integration of XBrowse and TDataRow in July 2013. Though less realized and appreciated, with this enhancement it is possible to write fully portable code for table maintenance.

[Horizon]

Hi Mr. Rao,

Thanks for using ado sample. this is very simple. I have a few questions.

1) Can you improve your above sample using filter or indexed dbf.

2) Can we use sql query in dbf. if yes, how fast?

3) If we use this ado functions properly in our program with dbf, later can we use the code with for example mysql or mssql?

Thanks again.

[Kleyber]

Mr Klyber

Most important ADO functions have been there since 2011. We have been adding and improving since then.

TDatarow class was first included in May 2013.

Full integration of XBrowse and TDataRow in July 2013. Though less realized and appreciated, with this enhancement it is possible to write fully portable code for table maintenance.

Thanks a lot, Nagesh.

Best Regards,

[Horizon]

Hi Mr. Rao,

Thanks for using ado sample. this is very simple. I have a few questions.

1) Can you improve your above sample using filter or indexed dbf.

2) Can we use sql query in dbf. if yes, how fast?

3) If we use this ado functions properly in our program with dbf, later can we use the code with for example mysql or mssql?

Thanks again.

up