Webgadget Q

captain_skank · **Posted:** Fri Apr 27, 2012 2:12 pm

Hi all,

I've been playing around with using a webgadget as a replacement listicon that easily allows me to have variable row heights ( and can be made to look prettier ).

For the most part it works fine, and I build the html into a string and pass it to the webgadget using

Code:

SetGadgetItemText(#gadget_0, #PB_Web_HtmlCode, html.s)

However, I create the string by looping through records in the database and adding to the html.s string, but this seems to be very slow for any more than 10 records.

I'm assuming this is because the string is getting larger and larger - but is there a better way to do this ??

cheers

captain_skank · **Posted:** Fri Apr 27, 2012 2:17 pm

This is some of my code

Code:

Procedure tab_01_populate_table()
  
  html.s = "<html><head><style type='text/css'> table {border-collapse:collapse;} table, td, th {padding:10px; border:1px solid #d6dde6; font-family: arial; font-size: 12px;}</style></head><body link='black' alink='black' vlink='black'><table width=100%>"
  html.s = html.s + "<th bgcolor='#828282'><font color='#ffffff'>Part ID</font></th><th bgcolor='#828282'><font color='#ffffff'>Part No.</font></th><th bgcolor = '#828282'><font color='#ffffff'>Issue</font></th><th bgcolor = '#828282'><font color='#ffffff'>Status</font></th><th bgcolor = '#828282'><font color='#ffffff'>Long Description</font></th>"
 
  sql.s = "SELECT DISTINCT id, part_id, part_number, issue_number, product_status, short_description FROM tbl_product WHERE part_number <> '' ORDER BY part_number ASC, issue_number ASC"
  
  row_no.s = "odd"
  
  ; Initialize the ODBC engine
  If UseODBCDatabase()
    ; Connect to the MySQL database
    If OpenDatabase(0, "database", "username", "password")
      ; Execute a SQL query
      If DatabaseQuery(0, sql)
        ; Fetching rows
        While NextDatabaseRow(0)
          ;html.s = html.s + "<tr>"
          If row_no = "odd"
            html.s = html.s + "<tr bgcolor = '#dfe7f2'>"
            row_no.s = "even"
          Else
            html.s = html.s + "<tr bgcolor = '#ffffff'>"
            row_no.s = "odd"
          EndIf
          record_id.s = GetDatabaseString(0, 0)
          part_id.s = GetDatabaseString(0, 1)
          part_number.s = GetDatabaseString(0, 2)
          issue_number.s = GetDatabaseString(0, 3)
          product_status.s = GetDatabaseString(0, 4)
          long_description.s = ReplaceString(GetDatabaseString(0, 5), Chr(13), "<br />")
          
          html.s = html.s + "<td width=15%><a STYLE='text-decoration:none' href=" + record_id + ">" + part_id + "</s></td>"
          html.s = html.s + "<td width=15%><a STYLE='text-decoration:none' href=" + record_id + ">" + part_number + "</s></td>"
          html.s = html.s + "<td width=5%><a STYLE='text-decoration:none' href=" + record_id + ">" + issue_number + "</s></td>"
          html.s = html.s + "<td width=10%><a STYLE='text-decoration:none' href=" + record_id + ">" + product_status + "</s></td>"
          html.s = html.s + "<td width=55%><a STYLE='text-decoration:none' href=" + record_id + ">" + long_description + "</s></td>"
          html.s = html.s + "</tr>"
        Wend
      Else
        debug "error"
      EndIf
      CloseDatabase(db_no_two)
    Else
      debug "error"
    EndIf
  Else
    debug "error"
  EndIf
  
  html.s = html.s + "</table></body></html>"
  
  ; populate web gadget with html
  SetGadgetItemText(#fld_8010_t1_table, #PB_Web_HtmlCode, html.s)
  ; hook webgadget events
  SetGadgetAttribute(#fld_8010_t1_table, #PB_Web_NavigationCallback, @AboutHtml_NavigationCallback())
  
EndProcedure

Seymour Clufley · **Posted:** Mon Apr 30, 2012 8:13 pm

I don't know anything about ODBC databases, but your code could certainly be improved.

You only need to put the .s at the end of a string variable the first time, when you declare it. Also, adding to a string can be done more simply. So this:

Code:

html.s = html.s + "abc"

could be written as:

Code:

html + "abc"

The STYLE attribute should be in lower case.

The FONT tag is deprecated and should not be used.

#ffffff could be written as #fff or even just white.

Instead of applying the same style attributes to elements individually (eg. "text-decoration:none; "), use CSS. This would greatly shorten the string variables you're juggling in PB.

width=15% should be width='15%', with apostrophes or speech marks. That is valid HTML. The same goes for href attributes.

You are closing anchor elements with </s> instead of </a>.

Within PB, you are reading data into string variables that do nothing but get merged into other string variables. This is inefficient. Instead of:

Code:

part_number.s = GetDatabaseString(0, 2)
html.s = html.s + "<td width=15%><a STYLE='text-decoration:none' href=" + record_id + ">" + part_number + "</s></td>"

do:

Code:

html + "<td class='my_td'><a href='" + record_id + "'>" + GetDatabaseString(0, 2) + "</a></td>"

with this class defined at the start:

Code:

.my_td { width:15%; }
.my_td a { text-decoration:none; }

Do all of those things then try the new code. It should be faster than your current version.

It may be that the slowness of the program is not PB creating the HTML, but the webgadget struggling to parse the HTML you've fed it. You really need to be careful with HTML, especially if the webgadget is Internet Explorer.

captain_skank · **Posted:** Tue May 01, 2012 8:56 am

Thanks for the reply.

Yeah the code is messy, and tbh I was really only pratting about with the HTML/CSS side at the mo, but i'm not quite sure where I got the '/S' from :oops:

.

The changes suggested made it no faster however.

I've tried a few other things and have decided on a work around that displays records grouped alphabeticaly ( like a roladex ) and limiting the SQL to 300 records at a time using LIMIT in the sql.

It works quite well and as I said using the web gadget gives me variable row height which is important for this task and a prettier display.

Seymour Clufley · **Posted:** Tue May 01, 2012 4:27 pm

I would suggest isolating different parts of the code and doing speed tests. For example:

1. Cut out the webgadget and see how long it takes to just get the info out of the database (no HTML forming).

2. Ignore the database and just make the HTML then feed it to the webgadget.

Hopefully this would identify which part of the program is going slowly. But if you've got a workaround you probably don't need to do this!

Perkin · **Posted:** Sat May 12, 2012 12:48 am

Store each of the strings that build the html page in a list/array (which you've got anyway)
(Edit: I used a list then I didn't have to worry about dimesions and looping was done with foreach)
before building the whole page, loop through the array getting the string lengths and adding to a totalsize variable
and create a memory buffer of that size
re-loop poking the strings into the memory buffer (with offsets)
then either directly send that buffer to the WebGadget (if you also included the front/end page stuff) or PeekS into a string that you can then use for WebGadget.

I did something similar quite a while ago where I was building a very long html page out of thousands of strings that I was modifying, doing it this way was UNBELIEVABLE in speed increase, I thought I'd messed something up and it had skipped something, it went from minutes to seconds, to build the page.

Perkin · **Posted:** Sat May 12, 2012 1:17 am

To give you some idea of what I meant... (adapted you're example)

Code:

Procedure tab_01_populate_table()
   NewList DB_HTML.s()
    
  html.s = "<html><head><style type='text/css'> table {border-collapse:collapse;} table, td, th {padding:10px; border:1px solid #d6dde6; font-family: arial; font-size: 12px;}</style></head><body link='black' alink='black' vlink='black'><table width=100%>"
  html.s = html.s + "<th bgcolor='#828282'><font color='#ffffff'>Part ID</font></th><th bgcolor='#828282'><font color='#ffffff'>Part No.</font></th><th bgcolor = '#828282'><font color='#ffffff'>Issue</font></th><th bgcolor = '#828282'><font color='#ffffff'>Status</font></th><th bgcolor = '#828282'><font color='#ffffff'>Long Description</font></th>"
 
  sql.s = "SELECT DISTINCT id, part_id, part_number, issue_number, product_status, short_description FROM tbl_product WHERE part_number <> '' ORDER BY part_number ASC, issue_number ASC"
 
  row_no.s = "odd"
 
  ; Initialize the ODBC engine
  If UseODBCDatabase()
    ; Connect to the MySQL database
    If OpenDatabase(0, "database", "username", "password")
      ; Execute a SQL query
      If DatabaseQuery(0, sql)
        ; Fetching rows
        While NextDatabaseRow(0)
           AddElement(DB_HTML())
           thisentry.s=""
          ;html.s = html.s + "<tr>"
          If row_no = "odd"
            thisentry = thisentry + "<tr bgcolor = '#dfe7f2'>"
            row_no = "even"
          Else
            thisentry = thisentry + "<tr bgcolor = '#ffffff'>"
            row_no = "odd"
          EndIf
          record_id.s = GetDatabaseString(0, 0)
          part_id.s = GetDatabaseString(0, 1)
          part_number.s = GetDatabaseString(0, 2)
          issue_number.s = GetDatabaseString(0, 3)
          product_status.s = GetDatabaseString(0, 4)
          long_description.s = ReplaceString(GetDatabaseString(0, 5), Chr(13), "<br />")
         
          thisentry = thisentry + "<td width=15%><a STYLE='text-decoration:none' href=" + record_id + ">" + part_id + "</s></td>"
          thisentry = thisentry + "<td width=15%><a STYLE='text-decoration:none' href=" + record_id + ">" + part_number + "</s></td>"
          thisentry = thisentry + "<td width=5%><a STYLE='text-decoration:none' href=" + record_id + ">" + issue_number + "</s></td>"
          thisentry = thisentry + "<td width=10%><a STYLE='text-decoration:none' href=" + record_id + ">" + product_status + "</s></td>"
          thisentry = thisentry + "<td width=55%><a STYLE='text-decoration:none' href=" + record_id + ">" + long_description + "</s></td>"
          thisentry = thisentry + "</tr>"
          DB_HTML() = thisentry
        Wend
        hmtlsize.i=0
        ForEach DB_HTML()
           htmlsize+Len(DB_HTML())
        Next
        *buff = AllocateMemory(htmlsize)
        If *buff
           offset.i=0
           ForEach DB_HTML()
              PokeS(*buff+offset,DB_HTML())
              offset+Len(DB_HTML())
           Next
           html + PeekS(*buff)
           FreeMemory(*buff)
        Else
           Debug "Error: Can't allocate memory."
        EndIf
        
      Else
        Debug "error"
      EndIf
      CloseDatabase(db_no_two)
    Else
      Debug "error"
    EndIf
  Else
    Debug "error"
  EndIf
 
  html.s = html.s + "</table></body></html>"
 
  ; populate web gadget with html
  SetGadgetItemText(#fld_8010_t1_table, #PB_Web_HtmlCode, html.s)
  ; hook webgadget events
  SetGadgetAttribute(#fld_8010_t1_table, #PB_Web_NavigationCallback, @AboutHtml_NavigationCallback())
 
EndProcedure

I know allocating and deleting strings is quite (relatively) time consuming especially when doing several hundred/thousand times.

N.B. - I haven't tested it

Hopefully it'll show you what I meant and you can see/understand and it helps.

PureBasic Forum

Webgadget Q

Who is online