FiveTech Software tech support forums

carlos vargas wrote:podria ser que:

Code: Select all | Expand

oCn:QueryResult( "select field1, field2 to my table where field3 = 'x' " ) // 0.21 seconds

retorna un arreglo simple de datos
y que

Code: Select all | Expand

oCn:Rowset( "select field1, field2 to my table where field3 = 'x' " ) // 1.87 seconds

acá creo que rao hace uso de una estructura de datos propia para poder hacer los filtrados y ordenamiento de forma local (cliente), para no hacer lecturas al servidor constantemente, aunado a que es un objeto bastante complejo, data/var y métodos, que es en lo cual donde realmente toma su tiempo, no tanto en la recuperación del cursor de datos que para ambos métodos es igual.

en lo personal, lo miro normal.

por eso cuando la información que se pretende recuperar es poca, y una sola fila de datos es preferible usar QueryResult, no tiene sentido usar RowSet, dado que no se hará búsqueda, ordenaciones, etc.

otro tip importantes es que, en un recordset, cuando usamos la asignación ( oQry:FIELD1 := 10 ) y lectura de un campo del query ( ?oQry:FIELD1 ), (tanto tmysq y tdolphin, en fwh no lo tengo claro ya que no se proporcionan los fuentes), se hace uso del método error para poder asignar y recuperar la información y eso agrega tiempo adicional, acá Rao podría aclarar.

when i have a button to "insert' a new record, is there other way editing data without RowSet object?

   oData := TArrayData():New( oCn, "select * from <tablename> limit 1" )
   oData:Record(,.t.):Edit()
   oData:SaveData()

oRecord := oCn:Record( cSql, [lBlank] ) // returns FW_Record / TDataRow object
oRecord:Edit()

nageswaragunupudi wrote:RowSet
RowSet takes a bit more time, because during creation it collects a lot other relevant information which makes usage faster.
First time reading is a bit slower when compared to other libraries.
Once opened, updates, inserts, deletes, sorts, filters, etc. are all extremely faster than the other libs.
If we look back in the forums, we posted speed comparison with other libraries a few years back.

When a RowSet is opened for ReadOnly, again the initial reading is faster.

Code: Select all | Expand

oRs := oCn:RowSet( cSql, [aParams], [lReadOnly] )

Execute

Code: Select all | Expand

aData := oCn:Execute( cSql, [aParams] )

We get the data in a multi-dimensional array.
In case we need the structure, call this immediately

Code: Select all | Expand

aStruct := oCn:Execute()

QueryResult
This is an extension to Execute.
if we use an sql like "select age from customer where id = 100"
this function returns 58, instead of {{58}}

nageswaragunupudi wrote: As you indicated above, if we want to edit just one record in a table, we can

Code: Select all | Expand

aRow := oCn:QueryResult( "select * from states where code='WA'" )
//We get a single dim array { "WA", "Washington" }
// Edit the array and then
oCn:Upsert( "states", nil, { aRow } )

aRow := oCn:QueryResult( "select field1,field2 from states where code='WA'" )
if aRow == NIL // not found, then inserts
    aRow := { "aa","bb" } 
    oCn:Upsert( "states", nil, { aRow } )
endif

nageswaragunupudi wrote:

when i have a button to "insert' a new record, is there other way editing data without RowSet object?

Please try this and let us know.

Code: Select all | Expand

   oData := TArrayData():New( oCn, "select * from <tablename> limit 1" )
   oData:Record(,.t.):Edit()
   oData:SaveData()

Código           : 1
Descrição do Erro:
Erro BASE/1123  Erro nos parƒmetros: AADD
Código: 1
Detalhamento:
------------
Arg. 1      Tipo: U Valor: 
Arg. 2      Tipo: N Valor: 1

Chamada de Pilha
================
Chamado de AADD(0)
Chamado de (b)TARRAYDATA:LOADMYSQL(618)
Chamado de AEVAL(0)
Chamado de TARRAYDATA:LOADMYSQL(618)
Chamado de TARRAYDATA:FROMQUERY(456)
Chamado de TARRAYDATA:FROMMYSQL(366)
Chamado de TARRAYDATA:NEW(255)

nageswaragunupudi wrote:

I am thinking of adding method to get a single record from a table ( existing or new ) and then edit and save the data.
May be something like this:

Code: Select all | Expand

oRecord := oCn:Record( cSql, [lBlank] ) // returns FW_Record / TDataRow object
oRecord:Edit()

I am try this:
Code:
aRow := oCn:QueryResult( "select field1,field2 from states where code='WA'" )
if aRow == NIL // not found, then inserts
aRow := { "aa","bb" }
oCn:Upsert( "states", nil, { aRow } )
endif


But insert not work.

oCn:Upsert( "states", nil, { { 0, "KK", "KState" } } )

oCn:Insert( "states", "code,name", { { "KK", "KKKK" } } )

nageswaragunupudi wrote:I am try this:

Code: Select all | Expand

aRow := oCn:QueryResult( "select field1,field2 from states where code='WA'" )
if aRow == NIL // not found, then inserts
    aRow := { "aa","bb" }
    oCn:Upsert( "states", nil, { aRow } )
endif
 

But insert not work.
Table `states` has 3 fields: id, code, name where id is autoincrement field

This should work:

Code: Select all | Expand

oCn:Upsert( "states", nil, { { 0, "KK", "KState" } } )

For Upsert() to work, we need to include primary key also.

nageswaragunupudi wrote:But you can use normal Insert like this:

Code: Select all | Expand

oCn:Insert( "states", "code,name", { { "KK", "KKKK" } } )

nageswaragunupudi wrote:Please try this for adding new record.
This is working for me and should work for you too.
First run this program as it is without changes before you adopt to your tables.

Code: Select all | Expand

function MariaNewRecord()

   local oCn := maria_Connect( "209.250.245.152,fwh,fwhuser,FiveTech@2022" )
   local oData

   oData := TArrayData():New( oCn, "states", "FALSE" )
   oData:Edit()
   oData:SaveBatch()

   xbrowser ocn:states

return nil
 

Código           : 1
Descrição do Erro:
Erro BASE/1081  Erro nos parƒmetros: +
Código: 1
Detalhamento:
------------
Arg. 1      Tipo: C Valor:  where 
Arg. 2      Tipo: O Valor: TARRAYDATA

Chamada de Pilha
================
Chamado de TARRAYDATA:LOADMYSQL(607)
Chamado de TARRAYDATA:FROMMYSQL(391)
Chamado de TARRAYDATA:NEW(255)
Chamado de MARIANEWRECORD(190)

Run time error: