MySQL Query Size?[SOLVED]

ID:1251655
 
Apr 30 2013, 11:29 am (Edited on May 1 2013, 3:44 am)
Keywords: mysql, saving
(See the best response by A.T.H.K.)
Version 1:
mob/proc/insert_table()
    set background=1
    var/dbConnection/connector = new()
    var/dbconnection=connector.getConnection(connector.getDbi())

    if(dbconnection)
        var/Guide/G=new/Guide
        G.load(src.clan,src.slots[currentguide])// loads the guide


        var/DBQuery/resultset1 = connector.runQuery(dbconnection,"INSERT INTO `GOAHelper` (ckey, name) VALUES('[ckey]','[G.name]')")
        if(resultset1)
            if(debug) src << "Phase1: Okay"
            resultset1.Close(); //free up and erase data.
        else
            resultset1.Close();
            if(debug) src << "Failed.";
        //INITIATE THE GUIDE
        spawn()
            var/DBQuery/resultset2 = connector.runQuery(dbconnection,"UPDATE GOAHelper SET clan='[G.clan]', date='[G.date]', description='[G.description]', views='[G.views]' WHERE name='[G.name]' AND ckey='[G.owner]'")
            if(resultset2)
                if(debug) src << "Phase2: Okay"
                resultset2.Close(); //free up and erase data.
            else
                resultset2.Close();
                if(debug) src << "Failed.";
        spawn()
            var/DBQuery/resultset3 = connector.runQuery(dbconnection,"UPDATE GOAHelper SET likes='[G.likes]', creator='[G.creator]', descr='[G.descr]' WHERE name='[G.name]' AND ckey='[G.owner]'")
            if(resultset3)
                if(debug) src << "Phase3: Okay"
                resultset3.Close(); //free up and erase data.
            else
                resultset3.Close();
                if(debug) src << "Failed.";
        spawn()
            var/DBQuery/resultset4 = connector.runQuery(dbconnection,"UPDATE GOAHelper SET pros='[G.pros]', cons='[G.cons]', skillpoints='[G.skillpoints]' WHERE name='[G.name]' AND ckey='[G.owner]'")
            if(resultset4)
                if(debug) src << "Phase4: Okay"
                resultset4.Close(); //free up and erase data.
            else
                resultset4.Close();
                if(debug) src << "Failed.";
        spawn()
            var/DBQuery/resultset5 = connector.runQuery(dbconnection,"UPDATE GOAHelper SET str1='[G.str1]', rfx1='[G.rfx1]', con1='[G.con1]' WHERE name='[G.name]' AND ckey='[G.owner]'")
            if(resultset5)
                if(debug) src << "Phase5: Okay"
                resultset5.Close(); //free up and erase data.
            else
                resultset5.Close();
                if(debug) src << "Failed.";
        spawn()
            var/DBQuery/resultset6 = connector.runQuery(dbconnection,"UPDATE GOAHelper SET ent1='[G.int1]', lvl1='[G.lvl1]' WHERE name='[G.name]' AND ckey='[G.owner]'")
            if(resultset6)
                if(debug) src << "Phase6: Okay"
                resultset6.Close(); //free up and erase data.
            else
                resultset6.Close();
                if(debug) src << "Failed.";


Version 2:
mob/proc/insert_table2()//updated version 2
    set background=1
    var/dbConnection/connector = new()
    var/dbconnection=connector.getConnection(connector.getDbi())

    if(dbconnection)
        var/Guide/G=new/Guide
        G.load(src.clan,src.slots[currentguide])// loads the guide
        var/DBQuery/resultset1 = connector.runQuery(dbconnection,"INSERT INTO `GOAHelper` (ckey, name) VALUES('[ckey]','[G.name]')")
        if(resultset1)
            if(debug) src << "Phase1: Okay"
            resultset1.Close(); //free up and erase data.
        else
            resultset1.Close();
            if(debug) src << "Failed.";
        //INITIATE THE GUIDE

        var/SQLQuery
        SQLQuery+="\"UPDATE GOAHelper SET clan='[G.clan]', date='[G.date]', description='[G.description]', views='[G.views]'"
        SQLQuery+="SET likes='[G.likes]', creator='[G.creator]', descr='[G.descr]'"
        SQLQuery+="SET pros='[G.pros]', cons='[G.cons]', skillpoints='[G.skillpoints]'"
        SQLQuery+="SET str1='[G.str1]', rfx1='[G.rfx1]', con1='[G.con1]'"
        SQLQuery+="SET ent1='[G.int1]', lvl1='[G.lvl1]' WHERE name='[G.name]' AND ckey='[G.owner]'\""

        var/DBQuery/resultset2 = connector.runQuery(dbconnection,SQLQuery)
        if(resultset2)
            if(debug) src << "Phase2: Okay"
            resultset2.Close(); //free up and erase data.
        else
            resultset2.Close();
            if(debug) src << "Failed.";


Problem description:
I am Having an issue with my mysql setup. The ugly code(version 1) works perfectly and uploads all the information needed. However it is ugly, and I wouldn't dream of porting that to my multiplaer project. I have tried to streamline the code abit and do things more efficiently in version 2, however when that query is run, only the first 3 fields are uploaded to the server. The rest remain as null.

Which is the reason I resorted to version 1 in the first place so I could break the query into small bite sizes that can get uploaded.


Row 1: compressed code(version 2)
Row 2: ugly code(version 1)

I am using Dantom's Library, and someone else's mysql demo, when I find the name I will give credit. If anyone can give me some insight on how to make more robust queries, I would be grateful.

Thanks

Apr 30 2013, 12:31 pm
Jean Sqribe wrote:
Version 1:
 var/SQLQuery
        SQLQuery+="\"UPDATE GOAHelper SET clan='[G.clan]', date='[G.date]', description='[G.description]', views='[G.views]'"
        SQLQuery+="SET likes='[G.likes]', creator='[G.creator]', descr='[G.descr]'"
        SQLQuery+="SET pros='[G.pros]', cons='[G.cons]', skillpoints='[G.skillpoints]'"
        SQLQuery+="SET str1='[G.str1]', rfx1='[G.rfx1]', con1='[G.con1]'"
        SQLQuery+="SET ent1='[G.int1]', lvl1='[G.lvl1]' WHERE name='[G.name]' AND ckey='[G.owner]'\""


Are you intending to use 'SET' repeatedly?
Apr 30 2013, 12:59 pm
Your query is incorrect, that's why it doesn't work.

I think this one will do fine:
        var/SQLQuery = {"
UPDATE GOAHelper SET
    clan='[G.clan]',
    date='[G.date]',
    description='[G.description]',
    views='[G.views]',
    likes='[G.likes]',
    creator='[G.creator]',
    descr='[G.descr]',
    pros='[G.pros]',
    cons='[G.cons]',
    skillpoints='[G.skillpoints]',
    str1='[G.str1]',
    rfx1='[G.rfx1]',
    con1='[G.con1]',
    ent1='[G.int1]',
    lvl1='[G.lvl1]'
WHERE name='[G.name]' AND ckey='[G.owner]';"}
Apr 30 2013, 1:31 pm
No i did not intend to repeatedly use SET, that was silly of me. However, the problem still persists. I created a test verb to make sure the Query looks like how it should and it does, which is why I was escaping strings with backslashes.

I also ran a similar test using condensed code with smaller rows affected. Everything works until you start trying to manipulate more than 4-5 rows, it varies on the table strangely.

At first I assumed it was the free server I used putting a limit on the query size, but I have recently started renting a VPS and I still can't get it to work. Do you guys experience similar issues with your queries?
Apr 30 2013, 3:05 pm
In response to Jean Sqribe
I haven't experienced an issue like that, no.
Can you run the query directly on your server without trouble?

Edit: I would also be using Quote() to sanitize any text input. Maybe there's a ' in description, for example.
Apr 30 2013, 10:10 pm
Normally there's no limit. Invalid query is the only valid reason.
Apr 30 2013, 11:37 pm
Best response
Here is a good post about some of the functions the library has - http://www.byond.com/forum/?post=79592

Pay attention to the ErrorMsg() section, lets see if that helps.