MYSQL Save Question

ID:2151182
 
Sep 23 2016, 7:36 pm (Edited on Sep 23 2016, 11:28 pm)
(See the best response by Nadrew.)
Code:
    SaveCharacter()
        var/DBConnection/dbcon = new()
        var/connected = dbcon.Connect(DBI,username,server_pass)
        if(!connected){alert(usr,"Connection failed: [dbcon.ErrorMsg()]");return}
        var/Values={"
            x=[usr.x],y=[usr.y],z=[usr.z],level=[usr.level],exp=[usr.exp],hp=[usr.hp],max_hp=[usr.max_hp],energy=[usr.energy],
            max_energy=[usr.max_energy],atk=[usr.atk],def=[usr.def],spd=[usr.spd],money=[usr.money],baseicon=[usr.baseicon],
            hairicon=[usr.hairicon],haircolor=[usr.haircolor]"}

        var/query_sql   ="SELECT * FROM `[my_database]`.`characters` WHERE `char_id` = '[usr.Char_ID]'"
        var/insert_query="[query_sql] INSERT INTO `characters` SET '[Values]'"
        var/update_query="UPDATE `characters` SET [Values] WHERE `char_id` = [usr.Char_ID]"
        var/DBQuery/query = dbcon.NewQuery(query_sql)
        if(!usr.Char_ID)
            query_sql=insert_query
        if(usr.Char_ID)
            query_sql=update_query
        query.Execute()

        if(!query.Execute()){alert(usr,"Saving has failed.\n[query.ErrorMsg()]");return}
        alert(usr,"You have successfully saved your character.")
        query.Close()
        dbcon.Disconnect()




Using Dantom.DB.
Problem description:I'm prob doing alot of things wrong, i can get the character to load if i put stuff in manual into the tables but i can't get it to save/update or even to create i've tried multiple ways, it reads the IDs from tables and everything but it wont save.

Sep 24 2016, 12:01 am
I imagine your syntax is wrong, you won't see an ErrorMsg() because you're calling query.Execute() twice, the second one would clear the error from the first.

At a quick glance, it's probably because you're not encasing your variable values in anything, which is not valid SQL syntax.

Removing the excessive Execute() call should allow the query.ErrorMsg() part to output if there is in fact an error.
Sep 24 2016, 12:09 am
In response to Nadrew
i got rid of the
if(!query.Execute()){alert(usr,"Saving has failed.\n[query.ErrorMsg()]");return}


but left 
alert(usr,"Saving has failed.\n[query.ErrorMsg()]")


so i can see the error and nothing comes up and still don't work and that only leave one execute left
Sep 24 2016, 12:13 am
In response to Nadrew
 
    SaveCharacter()
        var/DBConnection/dbcon = new()
        var/connected = dbcon.Connect(DBI,username,server_pass)
        if(!connected){alert(usr,"Connection failed: [dbcon.ErrorMsg()]");return}
        var/Values={"
            x=[usr.x],y=[usr.y],z=[usr.z],level=[usr.level],exp=[usr.exp],hp=[usr.hp],max_hp=[usr.max_hp],energy=[usr.energy],
            max_energy=[usr.max_energy],atk=[usr.atk],def=[usr.def],spd=[usr.spd],money=[usr.money],baseicon=[usr.baseicon],
            hairicon=[usr.hairicon],haircolor=[usr.haircolor]"}
        var/query_sql   ="SELECT * FROM `[my_database]`.`characters` WHERE `char_id` = '[usr.Char_ID]'"
        var/insert_query="INSERT INTO `characters` SET `[Values]`"
        var/update_query="UPDATE `characters` SET `[Values]`"
        var/DBQuery/query = dbcon.NewQuery(query_sql)
        query_sql=insert_query
        query_sql=update_query
        if(!usr.Char_ID)
            query_sql=insert_query
        if(usr.Char_ID)
            query_sql=update_query
        query.Execute()
        alert(usr,"\n[query.ErrorMsg()]")
        query.Close()
        dbcon.Disconnect()


I tried it like this to with ` around Values even tried ' around values still does not work
Sep 24 2016, 12:14 am
To clarify, does the alert() appear at all, but with nothing being displayed as the error? You should have actually left the if(!query.Execute()) part, because if it's reaching that condition it means the query is failing for one reason or another, if it's successfully calling Execute() the error may be on the server's end, are you trying on a local or remote server, and are you sure the username you're using as proper read/write access for non-local connections. By default, MySQL usernames only work for localhost connections, and on some setups remote users don't get write access by default when being setup.
Sep 24 2016, 12:16 am
We've currently gotten the login and loading to work correctly but the saving does not. The server is being hosted on a local machine and everything is reading correctly.

Regardless of how we type it, it will not update the database and we've been at this for about 12 hours and we've written this in nearly every possible way we can think of.

I will also add that the alert is being called, however no error is present.
Sep 24 2016, 12:17 am
Oh, heh. You're never actually passing your query through Execute(), the only thing being passed to Execute() is your SELECT query, changing the query_sql variable doesn't change the query unless you change query.sql to it.

As it stands, that initial SELECT query is doing nothing, it's not required for saving unless you're looking to see if there's already data there -- which you're not even close to doing with that code.
Sep 24 2016, 12:20 am
Best response
Looking at it closer, you'd want to initialize the variable and everything AFTER checking if it should be set to insert_query or update_query, then you'd create your query variable with query_sql passed through as it is now. Or you can just pass query_sql through Execute(), but that adds an extra set of calls as opposed to just setting it in NewQuery().
Sep 24 2016, 12:28 am
In response to Nadrew
Got it to work thank you, took forever but it is updating now :)
Sep 24 2016, 12:29 am
Thank you for the subtle nudge. After 12 long hours, it works wonderfully. Thank you very much.