Android Question Using RDC to get data from SQL

[Gearcam]

I now have the SQL server connected to my B4A app and it runs

now to get specific data from existing tables!
I do not need to add tables just get data for now

I have added to the config.properties
sql.select_coating=SELECT type FROM coatings WHERE ID = 5

Which when from B4A I run this sub

Sub getcoat(name As String)
Log("get name - " & name)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name ="select_coating"
    cmd.Parameters = Array As Object(name)
    reqManager.ExecuteQuery(cmd, 0, Null)
End Sub

I get the results as expected with correct answer linking ID=5 to the value under "type"

Does this mean I need to add to the config file every query ?

What I need to be able to do from variables in the B4A program is select different values instead of 5 say 1273 with out having to manually change the config.properties each time.

for a different table would I need another line in the config properties ?

This must be possible

 

[Erel]

There is a small bug in your code. cmd.Parameters is a list of parameters that will replace the placeholders in the query.
There are no question marks in your query.

Change your config file to:
sql.select_coating=SELECT type FROM coatings WHERE ID = ?

Sub getcoat(id As Int)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name ="select_coating"
    cmd.Parameters = Array As Object(id)
    reqManager.ExecuteQuery(cmd, 0, Null)
End Sub

 

[Gearcam]

Thanks Erel
Now really moving forward
Is the write data basically the same ?

 

[Erel]

Yes.

 

[Gearcam]

Erel is therir an example of the config file ?

I tried
sql.update_dummy=UPDATE Type = 55 WHERE ID = 10

this should update a table called "Dummy" where id=10 and set Type=55

But does not work

Tried
sql.update_dummy=SET Type = 55 [WHERE ID = 10]

still does not work !

 

[DonManfred]

UPDATE Dummy SET Type = 55 WHERE ID = 10

?

 

[Gearcam]

this is the line in the config.properties
It will update specific records in the MS SQL database

its a test really it will need to have variables in it like
sql.select_coating=SELECT type FROM coatings WHERE ID = ?

 

[Gearcam]

Would be nice to have a full list of commands that can be used in config.properties file

this works

sql.update_dummy=UPDATE Dummy SET Type = 55 WHERE ID = 10

 

[incendio]

There are no list of commands, you must create it by yourself according to your need.

 

[Erel]

Would be nice to have a full list of commands that can be used in config.properties file

You can use any valid SQL statement.

 

[Gearcam]

Erel I am getting the hang of it now and can read and write data

One question

In the sql statement I can use ? which is then replaced by passed variables ie
sql.update_orders=update Orders set [Data1] = ? WHERE ID = ? (works fine)

But if I have lots of different data I want to use one line like this

sql.update_orders=update Orders set ? = ? WHERE ID = ? (does not work)

Or do I have to do
sql.update_orders=update Orders set [Data1] = ? WHERE ID = ?
sql.update_orders=update Orders set [Data2] = ? WHERE ID = ?
sql.update_orders=update Orders set [Data3] = ? WHERE ID = ?
sql.update_orders=update Orders set [Data4] = ? WHERE ID = ?

This is because in different cases I may want to write only certain pieces of data

Steve

 

[Erel]

Only values can be parameterized like this (this is how SQL works). Fields and tables cannot be parameterized.