FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour Problem with FWH and script SQL Server - SOLVED
jose_murugosa
Posts: 1279
Joined: Mon Feb 06, 2006 04:28 PM
Problem with FWH and script SQL Server - SOLVED
Posted: Thu May 29, 2025 02:22 PM
Me ha ocurrido en otras oportunidades y modificando el script ha funcionado, pero en este caso no lo he podido solucionar.......
Este es el Script en MS SQL SERVER
Code: Select all Collapse
     SELECT a.fecha, a.tropaano, a.tropanumero, a.correlativo, ta.descripcion,
          ROUND(NULLIF(t.kilosenpie, 0) / NULLIF(t.cabezas, 0), 2) AS kilosenpie,
          (a.kilos + b.kilos) AS KgsEn2da,
          ROUND( ((a.kilos + b.kilos) / NULLIF(t.kilosenpie / NULLIF(t.cabezas, 0), 0)) * 100, 2 ) AS 'Rend.', 
          b.kosher AS [Ritual], IIF(a.hilton = 0x54, 'SI', 'NO') AS hilton,
          IIF(a.tipodehacienda = 0, 'NO', 'SI') AS organico, t.productor AS 'No.Prod',
          prd.nombre AS 'Productor', t.consignatario AS 'No.Con.', con.nombre AS 'Consignatario',
          t.dicoseorigen, dpt.nombre AS 'Departamento',
          IIF(t.moneda = 22, 'Dólar', 'Peso U.') AS Moneda,
          IIF(t.moneda = 22, ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0), 2),
               ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0) / NULLIF(fh.cotizaciondolar, 0), 2)
          ) AS 'Precio U$S'
     FROM C10304 a WITH (NOLOCK)
     INNER JOIN C10304 b WITH (NOLOCK)
          ON a.establecimiento = b.establecimiento AND a.fecha = b.fecha AND a.correlativo = b.correlativo
          AND b.estado < 5 AND b.tipoderegistro = 4 AND b.mediares = 2
     INNER JOIN C10203 t WITH (NOLOCK) ON t.empresa = a.empresa AND t.especie = a.tropaespecie
          AND t.ano = a.tropaano AND t.numero = a.tropanumero
     INNER JOIN C10204 td WITH (NOLOCK) ON td.empresa = a.empresa AND td.especie = a.tropaespecie
          AND td.ano = a.tropaano AND td.numero = a.tropanumero AND td.ordendepesada = a.tropaorden
     LEFT JOIN P0025 prd WITH (NOLOCK) ON prd.establecimiento = a.establecimiento 
          AND prd.codigo = t.productor
     LEFT JOIN P0025 con WITH (NOLOCK) ON con.establecimiento = a.establecimiento 
          AND con.codigo = t.consignatario
     LEFT JOIN P0015 dpt WITH (NOLOCK)
          ON dpt.codigo = prd.departamento
     LEFT JOIN P0021 ta WITH (NOLOCK)
          ON ta.codigo = a.tipodeanimal
     OUTER APPLY ( 
          SELECT MIN(fc.cotizaciondolar) AS cotizaciondolar, SUM(fd.subtotal) AS subtotal
               FROM C10210 fc WITH (NOLOCK)
          INNER JOIN C10211 fd WITH (NOLOCK) ON fd.empresa = fc.empresa AND fd.pof = fc.pof
               AND fd.numero = fc.numero AND fd.tropalinea = td.ordendepesada
          WHERE fc.empresa = a.empresa AND fc.estado < 9 AND fc.pof = 'P' AND fc.tropaespecie = a.tropaespecie
               AND fc.tropaano = a.tropaano AND fc.tropanumero = a.tropanumero
          ) fh
     LEFT JOIN P0025 dio WITH (NOLOCK)
          ON dio.establecimiento = a.establecimiento 
          AND dio.dicose = t.dicoseorigen
     WHERE a.empresa = 1 AND a.estado < 5 AND a.tipoderegistro = 4 AND a.mediares = 1 
          AND a.fecha BETWEEN '20250301' AND '20250522'
     ORDER BY a.fecha, a.tropaano, a.tropanumero, a.correlativo;
If I put it in HeidiSQL works perfectly.



But in FWH with this code It doesn't work
Code: Select all Collapse
text into cSql
     
     SELECT a.fecha, a.tropaano, a.tropanumero, a.correlativo, ta.descripcion,
          ROUND(NULLIF(t.kilosenpie, 0) / NULLIF(t.cabezas, 0), 2) AS kilosenpie,
          (a.kilos + b.kilos) AS KgsEn2da,
          ROUND( ((a.kilos + b.kilos) / NULLIF(t.kilosenpie / NULLIF(t.cabezas, 0), 0)) * 100, 2 ) AS 'Rend.', 
          b.kosher AS [Ritual], IIF(a.hilton = 0x54, 'SI', 'NO') AS hilton,
          IIF(a.tipodehacienda = 0, 'NO', 'SI') AS organico, t.productor AS 'No.Prod',
          prd.nombre AS 'Productor', t.consignatario AS 'No.Con.', con.nombre AS 'Consignatario',
          t.dicoseorigen, dpt.nombre AS 'Departamento',
          IIF(t.moneda = 22, 'Dólar', 'Peso U.') AS Moneda,
          IIF(t.moneda = 22, ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0), 2),
               ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0) / NULLIF(fh.cotizaciondolar, 0), 2)
          ) AS 'Precio U$S'
     FROM C10304 a WITH (NOLOCK)
     INNER JOIN C10304 b WITH (NOLOCK)
          ON a.establecimiento = b.establecimiento AND a.fecha = b.fecha AND a.correlativo = b.correlativo
          AND b.estado < 5 AND b.tipoderegistro = 4 AND b.mediares = 2
     INNER JOIN C10203 t WITH (NOLOCK) ON t.empresa = a.empresa AND t.especie = a.tropaespecie
          AND t.ano = a.tropaano AND t.numero = a.tropanumero
     INNER JOIN C10204 td WITH (NOLOCK) ON td.empresa = a.empresa AND td.especie = a.tropaespecie
          AND td.ano = a.tropaano AND td.numero = a.tropanumero AND td.ordendepesada = a.tropaorden
     LEFT JOIN P0025 prd WITH (NOLOCK) ON prd.establecimiento = a.establecimiento 
          AND prd.codigo = t.productor
     LEFT JOIN P0025 con WITH (NOLOCK) ON con.establecimiento = a.establecimiento 
          AND con.codigo = t.consignatario
     LEFT JOIN P0015 dpt WITH (NOLOCK)
          ON dpt.codigo = prd.departamento
     LEFT JOIN P0021 ta WITH (NOLOCK)
          ON ta.codigo = a.tipodeanimal
     OUTER APPLY ( 
          SELECT MIN(fc.cotizaciondolar) AS cotizaciondolar, SUM(fd.subtotal) AS subtotal
               FROM C10210 fc WITH (NOLOCK)
          INNER JOIN C10211 fd WITH (NOLOCK) ON fd.empresa = fc.empresa AND fd.pof = fc.pof
               AND fd.numero = fc.numero AND fd.tropalinea = td.ordendepesada
          WHERE fc.empresa = a.empresa AND fc.estado < 9 AND fc.pof = 'P' AND fc.tropaespecie = a.tropaespecie
               AND fc.tropaano = a.tropaano AND fc.tropanumero = a.tropanumero
          ) fh
     LEFT JOIN P0025 dio WITH (NOLOCK)
          ON dio.establecimiento = a.establecimiento 
          AND dio.dicose = t.dicoseorigen
     WHERE a.empresa = 1 AND a.estado < 5 AND a.tipoderegistro = 4 AND a.mediares = 1 
          AND a.fecha BETWEEN '20250301' AND '20250522'
     ORDER BY a.fecha, a.tropaano, a.tropanumero, a.correlativo;
        
     ENDTEXT 
            
     oRs := FW_OpenRecordSet( oCn2, cSql )

     IF !oRs:Eof()
          xBrowse( oRs )
          oRs:Close()
     ELSE
          MsgAlert( 'No se pudo conectar con la base de datos' ) 
     ENDIF

     ENDTEXT
The script in the program is a copy of heidisql but returns an empty browse:



Why am I experimenting this problem?
Saludos/Regards,

José Murugosa

"Los errores en programación, siempre están entre la silla, el teclado y la IA!!"
karinha
Posts: 8515
Joined: Tue Dec 20, 2005 07:36 PM
Re: Problem with FWH and script SQL Server
Posted: Thu May 29, 2025 03:17 PM
Mira se ayuda,

https://www.fivetechsoft.com/forums/viewtopic.php?t=25836

Regards, saludos.
João Santos - São Paulo - Brasil - Phone: +55(11)95150-7341
jose_murugosa
Posts: 1279
Joined: Mon Feb 06, 2006 04:28 PM
Re: Problem with FWH and script SQL Server
Posted: Thu May 29, 2025 05:44 PM

Thanks for your reply, Karinha. I've been looking, but I haven't been able to fix it with those suggestions, even after replacing the 'between' in the script.

I couldn't find the solution.

Gracias por tu respuesta Karinha, estuve viendo, pero no he podido solucionarlo con esas sugerencias, pese incluso a reemplazar el 'between' en el script.

No he podido dar con la tecla.

Saludos/Regards,

José Murugosa

"Los errores en programación, siempre están entre la silla, el teclado y la IA!!"
Otto
Posts: 6983
Joined: Fri Oct 07, 2005 07:07 PM
Re: Problem with FWH and script SQL Server
Posted: Thu May 29, 2025 08:50 PM
ChatGPT suggests:
Code: Select all Collapse
#include "FiveWin.ch"

FUNCTION Main()

   LOCAL oCn2, oRs, cSql

   // 1. Minimal test query to check basic connection
   cSql := "SELECT TOP 10 a.fecha, a.tropaano, a.tropanumero " + ;
           "FROM C10304 a WITH (NOLOCK) " + ;
           "WHERE a.empresa = 1 AND a.estado < 5"

   oCn2 := FW_OpenAdoConnection( "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=USER;Password=PASS;" )

   IF oCn2 == NIL
      MsgAlert( "Could not open SQL connection", "ERROR" )
      RETURN NIL
   ENDIF

   oRs := FW_OpenRecordSet( oCn2, cSql )

   IF oRs:Fields:Count == 0
      MsgAlert( "No fields returned. Possible alias or syntax issue.", "Warning" )
   ELSEIF oRs:RecordCount == 0
      MsgAlert( "Query executed but returned no records.", "Note" )
   ELSE
      xBrowse( oRs )
   ENDIF

   oRs:Close()
   oCn2:Close()

   RETURN NIL
You can adapt the cSql variable to test:
JOINs gradually (Step 2)

Remove or rework OUTER APPLY (Step 3)

Clean up aliases (Step 4)

Replace complex SQL with a VIEW and test SELECT * FROM vYourView WHERE ... (Step 5)


Code: Select all Collapse
#include "FiveWin.ch"

STATIC cLogFile := "sql_debug.log"

FUNCTION Main()

   LOCAL oCn2, oRs, cSql, cMessage

   // 1. Basic test SQL – adjust as needed
   cSql := "SELECT TOP 10 a.fecha, a.tropaano, a.tropanumero " + ;
           "FROM C10304 a WITH (NOLOCK) " + ;
           "WHERE a.empresa = 1 AND a.estado < 5"

   FWLog( "Attempting connection..." )

   oCn2 := FW_OpenAdoConnection( ;
      "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=USER;Password=PASS;" )

   IF oCn2 == NIL
      FWLog( "Connection failed." )
      MsgAlert( "Could not open SQL connection", "ERROR" )
      RETURN NIL
   ENDIF

   FWLog( "Connection opened. Running query..." )
   FWLog( "SQL: " + cSql )

   BEGIN SEQUENCE
      oRs := FW_OpenRecordSet( oCn2, cSql )

      cMessage := "Fields: " + LTrim(Str(oRs:Fields:Count)) + ;
                  " | Records: " + LTrim(Str(oRs:RecordCount))

      FWLog( "Query executed. " + cMessage )

      IF oRs:Fields:Count == 0
         MsgAlert( "No fields returned. Possible alias or syntax issue.", "Warning" )
         FWLog( "ERROR: No fields returned." )
      ELSEIF oRs:RecordCount == 0
         MsgAlert( "Query executed but returned no records.", "Note" )
         FWLog( "Note: No data returned from query." )
      ELSE
         xBrowse( oRs )
         FWLog( "Browse opened successfully." )
      ENDIF

      oRs:Close()
   RECOVER USING oErr
      FWLog( "Exception: " + oErr:Description )
      MsgAlert( "Error during query execution: " + oErr:Description, "Exception" )
   END SEQUENCE

   oCn2:Close()
   FWLog( "Connection closed." )

   MsgInfo( MemoRead( cLogFile ), "Execution Log" )

   RETURN NIL

// --------------------------------------------------------

STATIC FUNCTION FWLog( cText )
   LOCAL hFile := FOpen( cLogFile )
   IF hFile < 0
      hFile := FCreate( cLogFile )
   ENDIF
   IF hFile >= 0
      FSeek( hFile, 0, FS_END )
      FWrite( hFile, DToC( Date() ) + " " + Time() + " >> " + cText + CRLF )
      FClose( hFile )
   ENDIF
   RETURN NIL
Features:
Connects to SQL Server using FW_OpenAdoConnection()

Logs all steps to sql_debug.log

Displays field and record counts

Shows query result via xBrowse() if successful

Displays log content in a popup at the end

🛠 What to customize:
Replace SERVERNAME, DBNAME, USER, PASS with real values

Replace the SQL query to test different variations (as discussed)
jose_murugosa
Posts: 1279
Joined: Mon Feb 06, 2006 04:28 PM
Re: Problem with FWH and script SQL Server
Posted: Fri May 30, 2025 12:43 PM
Otto wrote: ChatGPT suggests:
Code: Select all Collapse
#include "FiveWin.ch"

FUNCTION Main()

   LOCAL oCn2, oRs, cSql

   // 1. Minimal test query to check basic connection
   cSql := "SELECT TOP 10 a.fecha, a.tropaano, a.tropanumero " + ;
           "FROM C10304 a WITH (NOLOCK) " + ;
           "WHERE a.empresa = 1 AND a.estado < 5"

   oCn2 := FW_OpenAdoConnection( "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=USER;Password=PASS;" )

   IF oCn2 == NIL
      MsgAlert( "Could not open SQL connection", "ERROR" )
      RETURN NIL
   ENDIF

   oRs := FW_OpenRecordSet( oCn2, cSql )

   IF oRs:Fields:Count == 0
      MsgAlert( "No fields returned. Possible alias or syntax issue.", "Warning" )
   ELSEIF oRs:RecordCount == 0
      MsgAlert( "Query executed but returned no records.", "Note" )
   ELSE
      xBrowse( oRs )
   ENDIF

   oRs:Close()
   oCn2:Close()

   RETURN NIL
You can adapt the cSql variable to test:
JOINs gradually (Step 2)

Remove or rework OUTER APPLY (Step 3)

Clean up aliases (Step 4)

Replace complex SQL with a VIEW and test SELECT * FROM vYourView WHERE ... (Step 5)


Code: Select all Collapse
#include "FiveWin.ch"

STATIC cLogFile := "sql_debug.log"

FUNCTION Main()

   LOCAL oCn2, oRs, cSql, cMessage

   // 1. Basic test SQL – adjust as needed
   cSql := "SELECT TOP 10 a.fecha, a.tropaano, a.tropanumero " + ;
           "FROM C10304 a WITH (NOLOCK) " + ;
           "WHERE a.empresa = 1 AND a.estado < 5"

   FWLog( "Attempting connection..." )

   oCn2 := FW_OpenAdoConnection( ;
      "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=USER;Password=PASS;" )

   IF oCn2 == NIL
      FWLog( "Connection failed." )
      MsgAlert( "Could not open SQL connection", "ERROR" )
      RETURN NIL
   ENDIF

   FWLog( "Connection opened. Running query..." )
   FWLog( "SQL: " + cSql )

   BEGIN SEQUENCE
      oRs := FW_OpenRecordSet( oCn2, cSql )

      cMessage := "Fields: " + LTrim(Str(oRs:Fields:Count)) + ;
                  " | Records: " + LTrim(Str(oRs:RecordCount))

      FWLog( "Query executed. " + cMessage )

      IF oRs:Fields:Count == 0
         MsgAlert( "No fields returned. Possible alias or syntax issue.", "Warning" )
         FWLog( "ERROR: No fields returned." )
      ELSEIF oRs:RecordCount == 0
         MsgAlert( "Query executed but returned no records.", "Note" )
         FWLog( "Note: No data returned from query." )
      ELSE
         xBrowse( oRs )
         FWLog( "Browse opened successfully." )
      ENDIF

      oRs:Close()
   RECOVER USING oErr
      FWLog( "Exception: " + oErr:Description )
      MsgAlert( "Error during query execution: " + oErr:Description, "Exception" )
   END SEQUENCE

   oCn2:Close()
   FWLog( "Connection closed." )

   MsgInfo( MemoRead( cLogFile ), "Execution Log" )

   RETURN NIL

// --------------------------------------------------------

STATIC FUNCTION FWLog( cText )
   LOCAL hFile := FOpen( cLogFile )
   IF hFile < 0
      hFile := FCreate( cLogFile )
   ENDIF
   IF hFile >= 0
      FSeek( hFile, 0, FS_END )
      FWrite( hFile, DToC( Date() ) + " " + Time() + " >> " + cText + CRLF )
      FClose( hFile )
   ENDIF
   RETURN NIL
Features:
Connects to SQL Server using FW_OpenAdoConnection()

Logs all steps to sql_debug.log

Displays field and record counts

Shows query result via xBrowse() if successful

Displays log content in a popup at the end

🛠 What to customize:
Replace SERVERNAME, DBNAME, USER, PASS with real values

Replace the SQL query to test different variations (as discussed)
Otto,

Thanks a lot for your help.

Replacing FW_OpenRecordSet with oRs:=oCn2:Execute( cSql ) it works

I found that in complex or big scripts FW_OpenRecordSet doesn't work properly.

Again, Thanks
Saludos/Regards,

José Murugosa

"Los errores en programación, siempre están entre la silla, el teclado y la IA!!"

Continue the discussion

Static archive · New replies & topics via GitHub Discussions