file callback to use mysql limit function in nettalk browse

[ozejohn]

We have some huge mysql tables as data sources for some nettalk web server browse windows. We would like to use mysql's limit feature to restruct reads to just the number of records required to fill a browse. The set,next syntax results in reading all the records from the mysql table even though only a page worth are displayed each time.

We use a file call back browse extension template in clarion apps to implement a mysql file call back.

Has anyone tried to implement a file call back with nettalk to implement the mysql limit function? In the alternative can anyone suggest how else we might take advantage of the mysql limit function with nettalk web server?

[Bruce]

Hi John,

The MsSql driver allows you to set a prop:hint to do this - as in
Invoices{prop:hint} = ‘&Top 5’
I've been doing some testing to see if this can be built into the template.

However from the docs it does not appear that this property is supported in the ODBC driver. (But it's worth a try of course...)

After that it becomes a question of how strict the MySQL engine is as to the placement of the limit in the SQL statement. For example, you may be able to pass it via the SQLFILTER command.

Lastly - the NetTalk procedure is a normal Clarion procedure. So it should be possible to add the call back code, in much the same way as you do it to a normal browse. Is this a commercial template you are using? If not you can send it to me and I'll have a look to see what it's doing.

Cheers
Bruce

[ozejohn]

Hi Bruce,

Yep I made a little template to add to the browse window for mysql files. It is a hack of a free clarion template for mysql limits on a browse. A bit rough and ready but it does the job. It Improved the browse speed by about 1000 times on the mysql data. I will post it below in case it is useful to someone else that has to access mysql files.

#!
#!
#EXTENSION(MySQLWEBBBrowseSupport,'Activate NetTalk MySQL page load support'), APPLICATION
#PROMPT('Enable NetTalk MySQL Page Loaded Browse Support', CHECK), %MySQLWEBPageLoadEnabled, AT(10,10)
#!
#!
#EMBED(%MySQLWEBHiddenEmbed), HIDE
#EXTENSION(MySQLwebBrowse,'Nettalk MySQL Page Load Browse Extension'),Procedure
#RESTRICT
#IF(%MySQLWEBPageLoadEnabled)
  #ACCEPT
#ELSE
  #REJECT
#ENDIF
#ENDRESTRICT
#DISPLAY('Be sure to use the global extension ''MySQLWEBBrowseSupport''')
#DISPLAY('and enable it for this to work!')
#ATSTART
#ENDAT
#AT(%ModuleDataSection)
#IF(%MySQLWEBPageLoadEnabled)
      INCLUDE('FILECB.INC'), ONCE
#ENDIF

#ENDAT
#AT(%DataSection)
#IF(%MySQLWEBPageLoadEnabled)

MySQLWEBcb class, implements(sqlcallbackinterface)
wHandle         long
Flag            long
File            &FILE
SetCallbackOn   procedure()
SetCallbackOff  procedure
                end

#ENDIF
#ENDAT
#AT(%BrowseFilter)
#IF(%MySQLWEBPageLoadEnabled)

  MySQLWEBcb.wHandle = 0{prop:handle}
  MySQLWEBcb.File &= %PRIMARY
  MySQLWEBcb.SetCallbackOn()
  FLUSH(THISVIEW)
#ENDIF
#ENDAT
#AT(%Validaterecord)
#IF(%MySQLWEBPageLoadEnabled)

  MySQLWEBcb.SetCallbackOff()

#ENDIF
#ENDAT


#AT(%LocalProcedures)
#IF(%MySQLWEBPageLoadEnabled)
MySQLWEBcb.sqlcallbackinterface.ExecutingCode  PROCEDURE(CONST *CSTRING inStr, *BYTE Err, *CSTRING ErrCode, *CSTRING ErrMsg)
l:orderby CSTRING(1025)
l:sqlstatement CSTRING(8193)
l:limit cstring(33)
  code
  l:limit = ''
  l:sqlstatement = instr
  #EMBED(%MySQLWEBcbStart,'mysqlweb call back start')
  !putini('mysqlweblimit','start-' & format(today(),@D10) & '-' & format(clock(),@t5),instr,'.\mysqlweblimit.ini')
  loop
  #IF(NOT %MySQLWEBPageLoadEnabled)
     BREAK ! Page loading not enabled
  #ENDIF
     if upper(sub(InStr,1,7)) = 'SELECT ' and self.wHandle = 0{prop:handle}
        if instring(' LIMIT ',instr,1,1) ! added to stop limit being added twice if same file on same window twice
           BREAK
        end
        l:limit = ' LIMIT ' & %nRecs  * 2
     end
     break
  end
  !putini('mysqlweblimit','end-limit' & format(today(),@D10) & '-' & format(clock(),@t5),l:limit,'.\mysqlweblimit.ini')
  return l:sqlstatement & l:limit 

MySQLWEBcb.SetCallbackOn procedure()
  code
  self.Flag += 1
  if self.Flag
    SQLCallback(self.File, self.SQLCallbackInterface)
  end

MySQLWEBcb.SetCallbackOff  procedure()
  code
  self.Flag -= 1
  if self.Flag = 0
    SQLCallback(self.File, self.SQLCallbackInterface, 1)
  end

#ENDIF
#ENDAT