xbrowse(autosort) + tdolphin - erro

[MGA]

I have an object 'xbrowse', using tdolphin. The main query has a relationship between tables, which coincidentally has a field in the table with the same name: "key". But this field is not part of the query.

SELECT p.key, p.data, c.name FROM orders AS p LEFT JOIN customers AS c ON(c.code=p.code)

the browse works fine, however when clicking on the header (autosort) of the "key" column, an error is generated.

Note: When clicking on the header of the 'key' column, internally xbrowse does:

SELECT * FROM orders AS p LEFT JOIN customers AS c ON(c.code=p.code) ORDER by key

I believe this is the problem. How can I solve the situation?

example:

estrutura:

table pedidos:
chave
data
codigo

table clientes:
codigo
nome
chave

oQry := oServer:Query('SELECT p.chave, p.data, c.nome FROM pedidos AS p LEFT JOIN clientes AS c ON(c.codigo=p.codigo)'

@ 0,0 XBROWSE oBrowse OF oDialog OBJECT oQry AUTOCOLS AUTOSORT LINES

with object oBrowse:oCol(1)
:cHeader := 'Chave'
:cEditPicture := '@!'
:bEditValue := {|| oQry:chave}
end

with object oBrowse:oCol(2)
:cHeader := 'Data'
:cEditPicture := '@!'
:bEditValue := {|| oQry:data}
end

Error description: Error MYSQL/1052 Column 'chave' in order clause is ambiguous

Stack Calls
===========
Called from: .\source\prg\tdolpsrv.prg => DOLPHIN_DEFERROR( 2807 )
Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:CHECKERROR( 793 )
Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:SQLQUERY( 2024 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:LOADQUERY( 1145 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:SETNEWFILTER( 1577 )
Called from: .\source\prg\tdolpqry.prg => (b)TDOLPHINQRY( 237 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:SETORDER( 0 )
Called from: xbrowse.prg => TXBRWCOLUMN:SETORDER( 17428 )
Called from: xbrowse.prg => TXBRWCOLUMN:HEADERLBUTTONUP( 15938 )
Called from: xbrowse.prg => TXBROWSE:LBUTTONUP( 5157 )
Called from: .\source\classes\control.prg => TCONTROL:HANDLEEVENT( 1851

[cmsoft]

Prueba de esta forma:

Code: Select all  Expand view

#include "FiveWin.ch"
#include "xBrowse.ch"
#include "tdolphin.ch"
function Main()
LOCAL oForm, oServer, oQry, oBrw
CONNECT oServer HOST 'localhost' ;
                      USER 'root' ;
                      PASSWORD 'miclave' ;
                      PORT 3306 ;
                      FLAGS 0;
                      DATABASE 'test'
oQry := oServer:Query('SELECT p.chave as chave, p.data as data, c.nome as nome FROM ped AS p LEFT JOIN cli AS c ON(c.codigo=p.codigo)')
DEFINE DIALOG oForm TITLE "MGA" FROM 05,10 TO 42,100
     
   @ 00,00 XBROWSE oBrw DATASOURCE oQry;
              COLUMNS "chave","data","nome";
              HEADERS "Chave","Data","Nome";
              SIZES 100,300,100;
              OF oForm SIZE 340,215 PIXEL  AUTOSORT LINES
     
   oBrw:CreateFromCode()  
   
ACTIVATE DIALOG oForm CENTER
RETURN nil

[MGA]

cmsoft,

Friend, thanks for answering.

The problem happens when there is ORDER BY in the query. I redid it below in your code, and the error occurs. Without ORDER BY, there is no error.


#include "FiveWin.ch"
#include "xBrowse.ch"
#include "tdolphin.ch"
function Main()
LOCAL oForm, oServer, oQry, oBrw
CONNECT oServer HOST 'localhost' ;
USER 'root' ;
PASSWORD 'miclave' ;
PORT 3306 ;
FLAGS 0;
DATABASE 'test'
oQry := oServer:Query('SELECT p.chave as chave, p.data as data, c.nome as nome FROM ped AS p LEFT JOIN cli AS c ON(c.codigo=p.codigo) ORDER BY p.chave')
DEFINE DIALOG oForm TITLE "MGA" FROM 05,10 TO 42,100

@ 00,00 XBROWSE oBrw DATASOURCE oQry;
COLUMNS "chave","data","nome";
HEADERS "Chave","Data","Nome";
SIZES 100,300,100;
OF oForm SIZE 340,215 PIXEL AUTOSORT LINES

oBrw:CreateFromCode()

ACTIVATE DIALOG oForm CENTER
RETURN nil

[cmsoft]

Esto no me produce error:

Code: Select all  Expand view

#include "FiveWin.ch"
#include "xBrowse.ch"
#include "tdolphin.ch"

function Main()
LOCAL oForm, oServer, oQry, oBrw
CONNECT oServer HOST 'localhost' ;
                      USER 'root' ;
                      PASSWORD 'miclave' ;
                      PORT 3306 ;
                      FLAGS 0;
                      DATABASE 'test'
oQry := oServer:Query('SELECT p.chave as chave, p.data as data, c.nome as nome FROM ped AS p LEFT JOIN cli AS c ON(c.codigo=p.codigo) ORDER BY p.chave')


DEFINE DIALOG oForm TITLE "MGA" FROM 05,10 TO 42,100
     
   @ 00,00 XBROWSE oBrw DATASOURCE oQry;
              COLUMNS "chave","data","nome";
              HEADERS "Chave","Data","Nome";
              SIZES 100,300,100;
              OF oForm SIZE 340,215 PIXEL  AUTOSORT LINES
     
   oBrw:CreateFromCode()
   oBrw:aCols[1]:SetOrder()  
   
ACTIVATE DIALOG oForm CENTER
RETURN nil

[MGA]

Here the error occurs, it seems that internally xbrowse does SELECT *... see the error log


Time from start: 0 hours 1 mins 17 secs
Error occurred at: 14/11/2024, 15:28:38
Error description: Error MYSQL/1052 Column 'chave' in order clause is ambiguous

Stack Calls
===========
Called from: .\source\prg\tdolpsrv.prg => DOLPHIN_DEFERROR( 2807 )
Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:CHECKERROR( 793 )
Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:SQLQUERY( 2024 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:LOADQUERY( 1145 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:SETNEWFILTER( 1577 )
Called from: .\source\prg\tdolpqry.prg => (b)TDOLPHINQRY( 237 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:SETORDER( 0 )
Called from: xbrowse.prg => TXBRWCOLUMN:SETORDER( 17432 )
Called from: xbrowse.prg => TXBRWCOLUMN:HEADERLBUTTONUP( 15938 )
Called from: xbrowse.prg => TXBROWSE:LBUTTONUP( 5157 )
Called from: .\source\classes\control.prg => TCONTROL:HANDLEEVENT( 1851 )
Called from: xbrowse.prg => TXBROWSE:HANDLEEVENT( 12642 )
Called from: .\source\classes\window.prg => _FWH( 3653 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\window.prg => TMDIFRAME:ACTIVATE( 1114 )
Called from: mga.prg => MGASISTEMAS( 302 )
Called from: mga.prg => MAIN( 79 )

System
======
CPU type: Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz 3392 Mhz
Hardware memory: 8099 megs

Free System resources: 90 %
GDI resources: 90 %
User resources: 90 %

Windows total applications running: 1
1 ,

Variables in use
================
Procedure Type Value
==========================
DOLPHIN_DEFERROR
Param 1: O Class: ERROR
TDOLPHINSRV:CHECKERROR
Param 1: O Class: TDOLPHINSRV
Param 2: N 1052
Param 3: L .F.
Param 4: C ""
Local 1: C ""
Local 2: O Class: ERROR
TDOLPHINSRV:SQLQUERY
Local 1: N 1052
Local 2: U
Local 3: L .F.
TDOLPHINQRY:LOADQUERY
Param 1: C "SELECT * FROM nfsaida AS nf LEFT JOIN pedidos AS p ON (nf.chave=p.chave) LEFT JOIN clientes AS c ON (nf.cliente = c.codigo) WHERE IF(nf.filialfiscal!=0,nf.filialfiscal,nf.filial) = 1 AND nf.emissao BETWEEN '2024-11-01' AND '2024-11-30' AND COALESCE(nf.notaservico, 0 ) = 0 ORDER BY chave"
Local 1: N 287

[cmsoft]

Param 1: C "SELECT * FROM nfsaida AS nf LEFT JOIN pedidos AS p ON (nf.chave=p.chave) LEFT JOIN clientes AS c ON (nf.cliente = c.codigo) WHERE IF(nf.filialfiscal!=0,nf.filialfiscal,nf.filial) = 1 AND nf.emissao BETWEEN '2024-11-01' AND '2024-11-30' AND COALESCE(nf.notaservico, 0 ) = 0 ORDER BY chave"

Esta es otra consulta totalmente diferente.
Cual es la consulta original que usas?
El ejemplo que yo te pase, te da errores en tu computadora?

[nageswaragunupudi]

Here the error occurs, it seems that internally xbrowse does SELECT *... see the error log

There is NO problem with XBrowse.
XBroswe does not make any SELECT queries.
XBrowse simply calls

Code: Select all  Expand view

oYourDolpinQry:SetOrder( cFieldName )

Your error is occurring inside Dolphin Qry's SetOrder method.
The problem must be with the names you used to prepare your query.