to Rao

[kajot]

Mr Rao could You write some examples with
select * from .. and xBrowser

best regards
kajot

[nageswaragunupudi]

Are you referring to built-in FWHMaria library or ADO?

[kajot]

ADO

[nageswaragunupudi]

Code: Select all  Expand view  RUN

function SampleSQL

   local oCn, oRs, cSql

   FWNumFormat( "A", .t. )

   oCn   := FW_OpenAdoConnection( "MYSQL,208.91.198.197:3306,fwhdemo,gnraofwh,Bharat@1950", .t. )

// 1
   cSql  := "SELECT * FROM customer"
   oRs   := FW_OpenRecordSet( oCn, cSql )
   XBROWSER oRs FASTEDIT
   oRs:Close()

// 2
   cSql  := "SELECT ID,First,City,Salary FROM customer"
   oRs   := FW_OpenRecordSet( oCn, cSql )
   XBROWSER oRs FASTEDIT
   oRs:Close()

// 3
   cSql  := "SELECT C.ID,C.City,S.NAME AS StateName FROM customer C " + ;
            "LEFT JOIN states S ON C.STATE = S.CODE ORDER BY ID"
   oRs   := FW_OpenRecordSet( oCn, cSql )
   XBROWSER oRs FASTEDIT
   oRs:Close()

   oCn:Close()

return nil

[Marc Venken]

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=35161#p209767

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=35830

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=36740

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=39629#p241540

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=39756#p237288

[kajot]

thank you

can I use "select * from" inside xbrowser

[nageswaragunupudi]

can I use "select * from" inside xbrowser

Can you please explain more clearly what do you want to achieve?

[kajot]

I want to change parameters in "select * to" when
xbrowser has worked already

[nageswaragunupudi]

ok. Please wait

[nageswaragunupudi]

Change some parameters like Where clause but without changing the fields.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn, oRs, oDlg, oBar, oBrw, cSql

   oCn   := FW_OpenAdoConnection( "MYSQL,208.91.198.197:3306,fwhdemo,gnraofwh,Bharat@1950", .t. )
   cSql  := "SELECT ID,First,City,State FROM customer WHERE STATE = "
   oRs   := FW_OpenRecordSet( oCn, cSql + "'NY'" )

   DEFINE DIALOG oDlg SIZE 700,300 PIXEL TRUEPIXEL
   DEFINE BUTTONBAR oBar OF oDlg SIZE 32,32 2007

   @ 52,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRs AUTOCOLS ;
      CELL LINES NOBORDER FASTEDIT

   WITH OBJECT oBrw
      :nEditTypes    := EDIT_GET
      :CreateFromCode()
   END

   DEFINE BUTTON PROMPT "WA" OF oBar CENTER ACTION NewSql( oBrw, cSql + "'WA'" )
   DEFINE BUTTON PROMPT "CA" OF oBar CENTER ACTION NewSql( oBrw, cSql + "'CA'" )
   DEFINE BUTTON PROMPT "NY" OF oBar CENTER ACTION NewSql( oBrw, cSql + "'NY'" )

   ACTIVATE DIALOG oDlg CENTERED

   oRs:Close()
   oCn:Close()

return nil

static function NewSql( oBrw, cSql )

   WITH OBJECT oBrw:oRs
      :Close()
      :Source  := cSql
      :Open()
   END
   oBrw:Refresh()

return nil
 

The logic is : Close the recordset, change the oRs:Source and then Open again

[nageswaragunupudi]

It is also possible to select different fields and even change the source table.
Let us see:

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn, oRs, oDlg, oBar, oBrw

   FWNumFormat( "E", .t. )
   SetGetColorFocus()

   oCn   := FW_OpenAdoConnection( "MYSQL,208.91.198.197:3306,fwhdemo,gnraofwh,Bharat@1950", .t. )
   oRs   := FW_OpenRecordSet( oCn, "select * from states" )

   DEFINE DIALOG oDlg SIZE 800,300 PIXEL TRUEPIXEL
   DEFINE BUTTONBAR oBar OF oDlg SIZE 80,32 2007

   @ 52,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRs AUTOCOLS AUTOSORT ;
      CELL LINES NOBORDER FASTEDIT

   WITH OBJECT oBrw
      :nEditTypes    := EDIT_GET
      :CreateFromCode()
   END

   DEFINE BUTTON PROMPT "CUSTOMER"  OF oBar CENTER ACTION NewTable( oBrw, "customer" )
   DEFINE BUTTON PROMPT "PIVOTDATA" OF oBar CENTER ACTION NewTable( oBrw, "pivotdata" )
   DEFINE BUTTON PROMPT "STATES"    OF oBar CENTER ACTION NewTable( oBrw, "states" )

   ACTIVATE DIALOG oDlg CENTERED

   oRs:Close()
   oCn:Close()

return nil

static function NewTable( oBrw, cTable )

   CursorWait()
   WITH OBJECT oBrw:oRs
      :Close()
      :Sort    := nil
      :Filter  := ""
      :Source  := "select * from " + cTable
      :Open()
   END
   WITH OBJECT oBrw
      :ResetData()
      :nEditTypes := EDIT_GET
      :SetFocus()
   END

return nil
 

[kajot]

thank You

[kajot]

how can you pass parameters to "select * from "

[nageswaragunupudi]

FW_AdoApplyParams( cSql, aParams )

Code: Select all  Expand view  RUN

  cState   := "NY"
   nAge     := 35
   dDate    := {^ 1980/01/01 }

   cSql  := "SELECT * FROM customer WHERE STATE = ? AND AGE > ? AND HIREDATE > ?"
   ? cSql2 := FW_AdoApplyParams( cSql, { cState, nAge, dDate } )

// result  
// --> SELECT * FROM customer WHERE STATE = 'NY' AND AGE > 35 AND HIREDATE > '1980-01-01'
 

[nageswaragunupudi]

1) Are you using MySQL with ADO? Or MSSQL with ADO?
2) Your FWH Version? Harbour or xHarbour?
3) If you are using MySql, did you try FWH built-in MySql library?