topic-41622

Board index FiveWin for Harbour/xHarbour Problem With oRs:Requery()

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Problem With oRs:Requery()
Posted: Fri Apr 08, 2022 01:15 PM

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

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 (fw): Select all Collapse

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

everything is ok, But when I do:

Code (fw): Select all Collapse

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

There is happehing this error:

Code (fw): Select all Collapse

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 ?

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem With oRs:Requery()
Posted: Sat Apr 09, 2022 07:08 AM

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 (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem With oRs:Requery()
Posted: Sat Apr 09, 2022 07:45 AM

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

Code (fw): Select all Collapse

#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 (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

mariordz

Posts: 131

Joined: Tue Dec 26, 2006 04:50 PM

Re: Problem With oRs:Requery()
Posted: Mon Oct 17, 2022 10:45 PM

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

Code (fw): Select all Collapse

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem With oRs:Requery()
Posted: Tue Oct 18, 2022 03:11 AM

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 (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion