inserting, deleting and updating DB on push of one button

[mcbroh]

Skärmavbild 2015-01-09 kl. 06.58.08.png (23.43 KiB) Viewed 5434 times

Hi all,

I have a mobile app that is been made, but my headache has been based on the fact that i want the user to enter a code, if the code is in database, the point assigned with the code is added to users point, code is deleted from database, user point on both database and app are updated.
this has been my headache for day now. please help needed.
attached are the picture of app interface and code on it. the code is on the stack level. here is the code

on updatePoint
    -- construct the SQL (this selects all the data from the specified table)
    put "share" into tTableName    -- set this to the name of a table in your database

    put "SELECT Points FROM "&tTableName&" WHERE Username= '" & gUsername & "'" into tSQL  
    put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData -- query the database
 if gConnectionID is not a number then -- check the result and display the data or an error message
        answer error "There was a problem querying the database:" & cr & tData
    else
put tData into gPoints
    end if
end updatePoint

on getPoint
Put field "forCode" into tCodes
put "codes" into tTableCodes
put "SELECT Points FROM "&tTableCodes&" WHERE Codes= '" & tCodes & "'" into tSQL
 put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
Add tData to gPoints
put gPoint into tPoint
end getPoint

on upData
put field "forCode" into tCodes
put "codes" into tTableCodes
put gPoints
put "share" into tTableName
put "UPDATE "&tTableName&" SET Points= '"&tPoint&"' WHERE Username= '" & gUsername & "'" into tSQL
put "UPDATE "&tTableCodes&" SET Points= '0' WHERE Codes='" & tCodes & "'" into uSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL, uSQL) into tData
end upData



on the blue button i have created two handler which are on the stack level

on mouseDown
getPoint
end mouseDown

on mouseUp
upData
end mouseUp

[Klaus]

Hi mcbroh,

I do not see any REVOPENDATABASE command in your scripts?
This looks like you intended to open the database, but didn't:
...
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData -- query the database
if gConnectionID is not a number then
...
?

Should be something like this:
...
put revOPENDATABASE(.....) into gConnectionID
if gConnectionID is not a number then
...


Best

Klaus

[mcbroh]

Hi Klaus,

i have done that before just above the script

global gConnectionID, gPoints, gUsername

on openStack
send "openCard" to me in 110 millisecs

put urlEncode("-----") into gAddress
put urlEncode("-----") into gName
put urlEncode("---") into gUserName
put urlEncode("-----") into gPassword
put revOpenDatabase ("mysql", gAddress, gName, gUserName, gPassword) into tResult

//checking connection and giving reply & setting connectionID
    if tResult is a number then
        put tResult into gConnectionID
        answer "Welcome"
    else
        put empty into gConnectionID
        answer "In maintainance mode. Please check back later"
    end if

//updatePoint
end openStack

// handler for point box.
on updatePoint

then other handlers come afterwords

do you mean i need to open DB on every time i query it?
if am right i think once the first script opens it then its enough.

[phaworth]

You only need to open the database once.

You haven't said what the problem is. Do you get an error message or is the table just not updated?

I see a couple of problems in your code.

After revDataFromQuery, you need to check if tData begins with "revdberr" to see if the query worked or not. In one place you are checking gConnectionID and you don;t check at all in the other places.

The other issues is that you are calling revDataFromQuery to issue an UPDATE command. You need to call revExecuteSQL for anything other than SELECT statements.

I don;t see where your updatePoints handler is being called - is it just for debugging?

And finally, you can't pass 2 parameters with different UPDATE statements in them in one call. You need to call reveExecuteSQL twice, once for each UPDATE and check for error in between. The check for an error after revExecuteSQL is different than after revDataFromQuery - you have to check for a non-integer value in the result.

Is it guaranteed that there will be an entry for the user in the table? If not, you will have to check for that condition and issue an INSERT statement instead of an UPDATE.

FInally, issue a BEGIN command before the first UPDATE (using revExecuteSQL again) and a COMMIT after both UPDATES have worked successfully. If an error occurs, issue a "ROLLBACK command.

Hope that helps.

Pete
lcSQL Software

[mcbroh]

Hi Pete,

thanks for your advice, I have implemented the changes and it WORKS as i expected, but as a newbie, can you write me example of the BEGIN command, COMMIT and rollback ?
Hope it's not too much of a prob

Thanks.
Mcbroh

[phaworth]

No problem, they are really simple.

revexecuteSQL gConnectionID,"BEGIN"

revExecuteSQL gConnectionID,"COMMIT"

revExecuteSQL gConnectionID,"ROLLBACK"

This will make sure that your database is in a consistent state if. For example, if your second UPDATE fails, your database will not show results of the first UPDATE. Without these statements, if the second UPDATE fail, your database would show the results of the first UPDATE but not the second which would probably cause problems in your application.

Good luck!

Pete
lcSQL Software

[mcbroh]

Thanks Pete.

Problem solved.

//mcbroh