Problem With oRs:Requery()

[vilian]

Good Morning Guys,
I'm having a problem with oRS:Requery(). I have the SQL statment bellow:

Code: Select all  Expand view

cSql := " SELECT mps.*,pes.nome FROM tmovgpes AS mps LEFT JOIN tpessoal AS pes ON mps.cmat=pes.cmat WHERE mps.data = ? AND mps.cequipto = ? AND mps.nequipe = ? ORDER BY funcao"

When I do:

Code: Select all  Expand view

oRs := oBD:Query( cSql, {dDataAtual,aVeiculos[1,1],aVeiculos[1,9]} )

everything is ok, But when I do:

Code: Select all  Expand view

oRs:Requery({dDataAtual, oQryMvg:cequipto,oQryMvg:nequipe})

There is happehing this error:

Code: Select all  Expand view

SELECT mps.*,pes.nome FROM tmovgpes AS mps LEFT JOIN tpessoal AS pes ON mps.cmat=pes.cmat WHERE data = '2022-04-07' AND cequipto = '00001' AND nequipe = 7 ORDER BY funcao;SHOW FULL COLUMNS FROM tmovgpes [ ERROR: Column 'cequipto' in where clause is ambiguous ]
 

I observed that in the error message, WHERE condition is different . Is missing field's ALIAS. Do you know why is it happening ?

[nageswaragunupudi]

Impossible.
iI am sorry, this must be a total misunderstanding on your side.

SELECT mps.*,pes.nome FROM tmovgpes AS mps LEFT JOIN tpessoal AS pes ON mps.cmat=pes.cmat WHERE data = '2022-04-07' AND cequipto = '00001' AND nequipe = 7 ORDER BY funcao;SHOW FULL COLUMNS FROM tmovgpes [ ERROR: Column 'cequipto' in where clause is ambiguous ]

This is not an SQL generated by Requery().
";SHOW FULL COLUMNS FROM ...." is included in the sql only the first time, i.e., when the RowSet is created for the first time and never in the sql created by Requery().

So, the entry in the logfile does not pertain to Requery.

Also I am 100% sure that the where clause is not changed. Not even a single alphabet is changed in the original SQL.
Simply the "?" place holders are replaced by the parameters and nothing else.

Very likely that another logged error, you are attributing to the requery.

Another possibility:
We can Requery() with a totally different new Sql with or without parameters.
Eg:

Code: Select all  Expand view

oRs:Requery( cNewSql, { anewparams } )
 

In this case, it is like a totally new rowset reading into the current rowset object.
There can be an error in the cNewSql.

In any case, the problem lies outside the library, but not inside the library.

[nageswaragunupudi]

I have created a similar SQL using two tables on the our demo cloud server.

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn, oRs, cSql, cLog

   cLog  := cFileSetExt( ExeName(), "log" )
   FERASE( cLog )

   oCn   := FW_DemoDB( 6 )

   oCn:lLog := .t.

   cSql  := "SELECT mps.*, pes.NAME AS StateName FROM " + ;
            "customer mps LEFT JOIN states pes ON mps.STATE = pes.CODE " + ;
            "WHERE mps.STATE = ? AND mps.AGE < ?"

   oRs   := oCn:RowSet( cSql, { "NY", 50 } )
   ? "CREATION SQL", "", oRs:cLastSQL

   XBROWSER oRs TITLE FWVERSION


   oRs:ReQuery( { "WA", 60 } )
   ? "FIRST REQUERY SQL", "", oRs:cLastSQL

   XBROWSER oRs TITLE "REQUERY WITH " + cValToChar( oRs:aParams  )

   oRs:ReQuery( { "MA", 55 } )
   ? "SECOND REQUERY SQL", "", oRs:cLastSQL

   XBROWSER oRs TITLE "REQUERY WITH " + cValToChar( oRs:aParams  )

   XBROWSER oRs:aSql TITLE "LIST OF FIRST SQL & REQUERY SQLS" ;
      SHOW RECID ;
      SETUP ( oBrw:nDataLines := 4, oBrw:nMarqueeStyle := 1, ;
              oBrw:aCols[ 1 ]:cHeader := "SQL" )

   oRs:Close()
   oCn:Close()

   WinExec( "notepad.exe " + cLog )

return nil
 

I request you to first copy this code to your fwh\samples folder without any changes and build and test with buildh.bat.

You will see the original sql and two requery sqls.



you can also modify the sample program using any other tables on the demo server or by copying any tables to the demo server
Note:
You can easily copy tables from one server to another server using:

Code: Select all  Expand view

oMyCon:CopyTableToServer( "mytablename", oOtherServer )
 

I am sure you can never get the error you mentioned with Requery() and if you insist please try to prove with any two tables ( existing or new ) on the demo server.

[mariordz]

Mr Rao, I have Fivewin 19.12, Can I request recordsets from MSSQl using parameter with my versión?
Using the code:

Code: Select all  Expand view

cCadsql1:="SELECT e.ITEMNMBR, a.ITEMDESC, e.LOCNCODE, e.QTYONHND, e.ATYALLOC, e.QTYONHND - e.ATYALLOC, a.SELNGUOM, IV40201.baseuofm,"+;
" e.QTYSOLD, e.QTYRTRND, e.QTYDMGED, e.QTYBKORD, e.QTYONORD, e.QTYINUSE, e.QTYINSVC, e.BINNMBR, a.itemtype, a.USCATVLS_1, "+;
"IV40600.UserCatLongDescr, a.CURRCOST, a.CURRCOST * e.QTYONHND, (select psitmval from iv10402 where itemnmbr=e.itemnmbr and "+;
"PRCSHID='GENERAL'), isnull(c.caduca,0) FROM IV00102 e left outer JOIN IV00101 a ON e.ITEMNMBR = a.ITEMNMBR left outer JOIN IV40600 "+;
"ON a.USCATVLS_1=IV40600.USCATVAL join iv40201 on a.UOMSCHDL=iv40201.UOMSCHDL left outer join auxiliary..equivale c on "+;
"e.itemnmbr=c.itemnmbr WHERE a.ITEMTYPE < '3' and e.locncode= ? order by e.itemnmbr"
   
oCn   := FW_OpenAdoConnection( { "MSSQL", xSOURCE, xCATALOGA, xUSERID, xPASSWORD }, .t. )
   
oRs1   := oCn:RowSet( cCadSql1, { '003' } )
 

I get the error:

Application
===========
Path and name: C:\xDevStudio\Projects\Tubelite\Tubelite.EXE (32 bits)
Size: ********* bytes
Compiler version: Harbour 3.2.0dev (r1603082110)
FiveWin version: FWH 19.12
C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 10 secs
Error occurred at: 17/10/2022, 17:30:33
Error description: (DOS Error -2147352567) WINOLE/1007 Argumentos incorrectos, fuera del intervalo permitido o en conflicto con otros. (0x800A0BB9): ADODB.Connection
Args:
[ 1] = C SELECT e.ITEMNMBR, a.ITEMDESC, e.LOCNCODE, e.QTYONHND, e.ATYALLOC, e.QTYONHND - e.ATYALLOC, a.SELNGUOM, IV40201.baseuofm, e.QTYSOLD, e.QTYRTRND, e.QTYDMGED, e.QTYBKORD, e.QTYONORD, e.QTYINUSE, e.QTYINSVC, e.BINNMBR, a.
itemtype, a.USCATVLS_1, IV40600.UserCatLongDescr, a.CURRCOST, a.CURRCOST * e.QTYONHND, (select psitmval from iv10402 where itemnmbr=e.itemnmbr and PRCSHID='GENERAL'), isnull(c.caduca,0) FROM IV00102 e left outer JOIN IV00101 a ON e.ITEM
NMBR = a.ITEMNMBR left outer JOIN IV40600 ON a.USCATVLS_1=IV40600.USCATVAL join iv40201 on a.UOMSCHDL=iv40201.UOMSCHDL left outer join auxiliary..equivale c on e.itemnmbr=c.itemnmbr WHERE a.ITEMTYPE < '3' and e.locncode=? order by e.itemnmbr
[ 2] = A { ... } length: 1

Stack Calls
===========
Called from: => TOLEAUTO:ROWSET( 0 )
Called from: C:\xDevStudio\Projects\Tubelite\Source\Inventsc.PRG => BUS_ART( 493 )

Am I doing something worng?

[nageswaragunupudi]

Am I doing something worng?

Yes, please.

You are mixing up ADO with FWH built-in MySql/MariaDB library classes.

ADO connection object does not have RowSet(...) method. ADO is a product of Microsoft.

This is the suggested approach:

Code: Select all  Expand view

oCn   := FW_OpenAdoConnection( { "MSSQL", xSOURCE, xCATALOGA, xUSERID, xPASSWORD }, .t. )
if oCn == nil
   ? "connection fail"
   return nil
endif
cSql  := FW_ADOApplyParams( cCadSql1, { '003' } )
oRs1  := FW_OpenRecordSet( oCn, cSql )
if oRs1 == nil
   ? "Recordset open error"
else
   ? oRs:Fields( <fieldname> ):Value
   // other work
   oRs1:Close()
endif
oCn:Close()

 

You better study ADO here
https://www.w3schools.com/asp/ado_intro.asp