B4J Question HTML data to SQL

[paddy12309]

Hi everyone,

I'm possibly being very slow here but web dev isn't something i've done much/any of...
The aim is to try and get 2 text box inputs to be written to a database when a button is clicked.

Dim tabParam As ResultSet = Main.IntSQL.ExecQuery("SELECT * FROM TabletParameters;")
    Dim tabint As Int
        tabint = 0
    resp.Write("<Form>")
    resp.Write("<table style='width:100%'>")
    resp.Write("<tr>")
    resp.Write("<td style='width:25%'><b>Parameter</b></td>")
    resp.Write("<td style='width:25%'><b>StringValue</b></td>")
    resp.Write("<td style='width:25%'><b>IntValue</b></td>")
    resp.Write("<td style='width:25%'><b>Change</b></td>")
    resp.Write("</tr>")
    Do While tabParam.nextrow
        resp.Write("<tr>")
        resp.Write("<td style='width:25%'>" & tabParam.GetString("Param") & "</td>")
        resp.Write("<td style='width:25%'><input type='text' name='StringValue" & tabint & "' value='" & tabParam.GetString("StringValue") & "'></td>")
        resp.Write("<td style='width:25%'><input type='number' name='IntValue" & tabint & "' value='" & tabParam.GetString("IntValue") & "'></td>")
        resp.Write("<td style='width:25%'><a href='http://" & Main.myLocalIP & ":8888/DataTable?DatabaseName=IntSQL&DataTableName=TabletParameters'" & Main.IntSQL.ExecNonQuery("INSERT INTO TabletParameters(StringValue, IntValue)VALUES(" & StringValue & ", " & IntValue & ")") & "> <button type='button' value='Change'>change " & tabParam.GetString("Param") & "</button></a></td>")
        resp.Write("</tr>")
        ' & tabParam.GetString("Params") &
        tabint = tabint + 1
    Loop
    resp.Write("</table>")
    'resp.Write("<input type='submit'/>")
    resp.Write("</form>")

this creates a page, that is a table of 4 columns from left to right being, name, StringValue(as an input), IntValue(as an input), and an update button. so when clicked this should get the 2 inpouts and insert them into the database.

Hope this makes sense^^
Thanks in advance for any help!

 

[Erel]

You should really learn how to use smart strings: https://www.b4x.com/android/forum/threads/50135/#content

Is this a server handler? What is the question?

 

[paddy12309]

sorry yes its a server handler, here's the full handler

'Handler class
Sub Class_Globals
    Private mreq As ServletRequest 'ignore
    Private mresp As ServletResponse 'ignore
    Private ResponseTimer As Timer
    Private LastCommand As Long
End Sub
Public Sub Initialize
   
End Sub
Sub Handle(req As ServletRequest, resp As ServletResponse)
    mreq = req
    mresp = resp
    resp.ContentType = "text/html"
    Subs.RespWriteStyle(resp)
   
    If Main.LastReboot = 0 Or Main.LastReboot > (DateTime.Now - (1 * DateTime.TicksPerMinute)) Then
        resp.Write("Not Ready - Please wait until " & DateTime.Time(Main.LastReboot + (1 * DateTime.TicksPerMinute)) & "...")
        Return
    End If
    'Collator Settings
    resp.Write("<b>LiteIP Collator " & Main.ConnectedLiteIPMateSN & " - " & Main.ActivePostcode & "</b>")
   
    'Links to Other Pages
    Subs.RespWriteLinks(resp)
    Dim tabParam As ResultSet = Main.IntSQL.ExecQuery("SELECT * FROM TabletParameters;")
    Dim tabint, tabint2 As Int
        tabint = 0
    Dim StringValue As String
        StringValue = "test"
    Dim IntValue As Int
        IntValue = 0
    resp.Write("<style>")
    resp.Write("tr:nth-child(even) {background-color: #f2f2f2;}")
    resp.Write("</style>")
    'resp.Write("<Form>")
    resp.Write("<table style='width:100%'>")
    resp.Write("<tr>")
    resp.Write("<td style='width:25%'><b>Parameter</b></td>")
    resp.Write("<td style='width:25%'><b>StringValue</b></td>")
    resp.Write("<td style='width:25%'><b>IntValue</b></td>")
    resp.Write("<td style='width:25%'><b>Change</b></td>")
    resp.Write("</tr>")
    Do While tabParam.nextrow
        resp.Write("<tr>")
        resp.Write("<td style='width:25%'>" & tabParam.GetString("Param") & "</td>")
        resp.Write("<td style='width:25%'><input type='text' id='StringValue" & tabint & "' value='" & tabParam.GetString("StringValue") & "'></td>")
        resp.Write("<td style='width:25%'><input type='number' id='IntValue" & tabint & "' value='" & tabParam.GetString("IntValue") & "'></td>")
        resp.Write("<td style='width:25%'><a href='http://" & Main.myLocalIP & ":8888/DataTable?DatabaseName=IntSQL&DataTableName=TabletParameters'> <button type='button' value='Change'>change " & tabParam.GetString("Param") & "</button></a></td>")
        resp.Write("<td style='width:25%'><a href='http://" & Main.myLocalIP & ":8888/DataTable?DatabaseName=IntSQL&DataTableName=TabletParameters'" & Main.IntSQL.ExecNonQuery("INSERT INTO TabletParameters(StringValue, IntValue)VALUES(" & StringValue & tabint & ", " & IntValue & tabint & ")") & "> <button type='button' value='Change'>change " & tabParam.GetString("Param") & "</button></a></td>")
        resp.Write("</tr>")
        ' & tabParam.GetString("Params") &
        tabint = tabint + 1
    Loop
    resp.Write("</table>")
    'resp.Write("<input type='submit'/>")
'    resp.Write("</form>")
   
   
End Sub

So I don't know how to have the web-page displayed and when you edit the text boxes get that input and insert it into the database using B4J?

 

[KMatle]

I'ts better to devide UI elelements from data. What I do is to store just the data in a good db-model or (if it's just data which often changes via a JSON string). Then you're free to change the form/html-page like you need. If you store the html code in the db and want to change the look or other things you would need to update everything. Another con is that you are wasting lot's of storage.

I have an app doing this (just 500-800 bytes for a small invoice) instead of creating pdf files (about 150 KB)

PS: Check your SQL statement for SQL-injection issues

 

[paddy12309]

Sorry I don't think I've explained this very well, I have the handler create a web page. this is effectively just a table from a database 4 columns name, value1, value2, update button. When the page is loaded the value1 and value2 fields are filled with the data from the DB however these are text boxes so editable by the user, if edited the user then needs to be able to hit the submit button that then will update the DB data to the new value.

As for the SQL injection it shouldn't be an issue as its a small local project not running online, but will probably still escape the SQL!

 

[KMatle]

Did you think about Apache/php/html?

 

[Erel]

Start here: https://www.b4x.com/android/forum/threads/webapp-web-apps-overview.39811/#content

Check this example: https://b4x.com:51041/dbutils/index.html