SQLite: Trouble updating table

SQLite: Trouble updating table - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: SQLite: Trouble updating table (/showthread.php?tid=345183)

SQLite: Trouble updating table - ReneG - 24.05.2012

So I have this huge chunk of saving code, for some reason, it's not updating the row. I know it's not a problem with formatting, because I've printed the query, and the query is just right.

pawn Код:

format(string1, 300, "UPDATE `Users` SET Password = '%s', AdminLevel = '%d', VIPLevel = etc etc etc");
format(string2, 350, " etc etc etc WHERE `Name` = '%s' COLLATE NOCASE");
strcat(query, string1);
strcat(query, string2);
db_query(Database, query);

Just to be sure about the query, printing the query results in.

Код:

[20:04:01] UPDATE `Users` SET Password = '0D22FB7594D51AD1429447E311C477C01884CF8DDEC6427146FCE64BD817D8A4522458613941237CB5954415A05C49B7D03D753711AA8C28467AB24708F0EC59', 
AdminLevel = '0', VIPLevel = '0', ConnectedTime = '7', Sex = '1', Age = '14', Origin = '5', Muted = '0', Respect = '0', Cash = '0', BankA Job = '0', Jailed = '0', JailTime = '0', 
Mats = '0', Pot = '0', Crack = '0', Leader = '0', Rank = '0', Skin = '184', Warns = '0', Banned = '0', TutStep = '0', Pos_X = '150.009536', Pos_Y = '-67.181488', Pos_Z = '1.429687', 
Interior = '0', VirtualWorld = '0' WHERE `Name` = 'Test' COLLATE NOCASE

I also know that it isn't related to 'updating' empty fields because I had updated an empty field as a test like so.

pawn Код:

new
    testquery[128]
;
format(testquery, 128, "UPDATE `Users` SET Level = '%d' WHERE `Name` = '%s'", 1337, GetPlayerNameEx(playerid));
db_query(Database, testquery);

and it updates fine. Can someone tell me what I'm doing wrong? I'm thinking the problem is in the query. This is bugging me.

Re: SQLite: Trouble updating table - Jonny5 - 24.05.2012

try updating the pass in a separate query,
how long is this query?
Iv had problems with querys over 1024 in len

and ended up updating my pass in a separate query as I used whirlpool also

Re: SQLite: Trouble updating table - ReneG - 24.05.2012

The first part of the query that includes the password is 300 cells, the second part of the query that includes the rest of the data is 350 cells, and the query that Im concatenating is 700 cells, and the resulting query prints fine without cutting any of the query out. Im also using Whirlpool to hash passwords. What cell size would you recommend me to set as the password query?

Re: SQLite: Trouble updating table - T101 - 24.05.2012

Quote:

Originally Posted by VincentDunn

Код:

[20:04:01] UPDATE `Users` SET Password = '0D22FB7594D51AD1429447E311C477C01884CF8DDEC6427146FCE64BD817D8A4522458613941237CB5954415A05C49B7D03D753711AA8C28467AB24708F0EC59', 
AdminLevel = '0', VIPLevel = '0', ConnectedTime = '7', Sex = '1', Age = '14', Origin = '5', Muted = '0', Respect = '0', Cash = '0', BankA Job = '0', Jailed = '0', JailTime = '0', 
Mats = '0', Pot = '0', Crack = '0', Leader = '0', Rank = '0', Skin = '184', Warns = '0', Banned = '0', TutStep = '0', Pos_X = '150.009536', Pos_Y = '-67.181488', Pos_Z = '1.429687', 
Interior = '0', VirtualWorld = '0' WHERE `Name` = 'Test' COLLATE NOCASE

In case, that the column name "BankA Job" is in fact two separated words in your code, then i think you'd place them into quotation marks also. Hope it'll work that way.

Regards,
T101

Re: SQLite: Trouble updating table - Jonny5 - 24.05.2012

ahh yes you cannot have a space in the field name! im sure this is the issue
iv missed that completely!
also i use 129 for my whirlpool
that is the min for it from what iv read!

off topic
I made a function for creating tables easy!

pawn Код:

forward CreateDbTable(DB:db, tbl_name[], bool:PrimaryID,...);
public CreateDbTable(DB:db, tbl_name[], bool:PrimaryID,...)
{
    new
        str_Query[1024],
        str[24];
    
    strcat(str_Query,"CREATE TABLE IF NOT EXISTS `");
    strcat(str_Query,tbl_name);
    strcat(str_Query,"` (");
    if (PrimaryID) strcat(str_Query,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
    for (new i = 3,i_n_a=numargs(); i < i_n_a ; ++i)
    {
        if ((i>3)) strcat(str_Query,",");
        //credits to Meta for the code to get a string from the argument.
        //https://sampforum.blast.hk/showthread.php?pid=1426694#pid1426694
        for(new _argcount, _temparg[20], _fullarg[256]; ; _argcount++)
        {
            format(_temparg, sizeof(_temparg), "%s", getarg(i, _argcount));
            if(!strlen(_temparg)) { format(str, sizeof(str), "%s", _fullarg); break; }
            format(_fullarg, sizeof(_fullarg), "%s%s", _fullarg, getarg(i, _argcount));
        }
        strcat(str_Query,str);
    }
    strcat(str_Query,")");
    db_query(db,str_Query);
    return 1;
}

usage

pawn Код:

CreateDbTable
(
    db,     //database ref
    "USERS",    //table name
    true,       //create an auto incremental index field (ID)
    //table fields
    "NAME TEXT",
    "PASSWORD TEXT",
    "ALIAS TEXT",
    "IP",
    "X",
    "Y",
    "Z",
    "A",
    "INTERIOR",
    "VWORLD"
);

just wanted to share that with ya sense your getting into sqlite
I have a hole set of functions i plan to release in the future like this.

Re: SQLite: Trouble updating table - T101 - 24.05.2012

Actually, it'd be working even with a space in it, but the words must be inside extra quotation marks like this:

Код:

`BankA Job` = '0'

Regards,
T101

Re: SQLite: Trouble updating table - ReneG - 24.05.2012

Thank you guys very much, I figured out what the problem was, the first formatted query wasn't big enough causing the space typo, just had to increase the string size. Thanks again.

Re: SQLite: Trouble updating table - T101 - 24.05.2012

You're welcome. It's my pleasure, that i was able to help you out.

Regards,
T101