MySqlite Not throwing errors but...

ID:1795094
 
Feb 27 2015, 8:01 am
(See the best response by Nadrew.)
Code:
/*Database is created on world new in another file*/
var/global
    database/gq//
    const
        db="mydata.db"
        table="CharPlus"
        currency_name="GOLDENRYO"


proc
    CheckDB(var/database/query/q)
        if(q.Execute(gq))
            world<<"worked"
        else
            return q.ErrorMsg()


mob
    verb
        MakeTable()
            set category="Database"
            var/database/query/q=new({"CREATE TABLE [table](
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   [currency_name]         INT
);"})
            if(q.Execute(gq))
                world<<"worked"
            else
                world<<q.ErrorMsg()



        UpdDB()
            set category="Database"
            .=rand(5,999)
            //var/database/query/w=new({"UPDATE [table] SET [currency_name] = 999 WHERE NAME = [usr.key];"})
            var/database/query/w=new({"UPDATE `[table]` SET  `[currency_name]` =  '999' WHERE  `NAME` =  '[usr.ckey]';"})
            if(w.Execute(gq))
                world<<"worked"
            else
                world<<w.ErrorMsg()


        InzDB()
            set category="Database"
            set name="Insert Player Into DB"
            //INSERT INTO [table](NAME) VALUES([usr.key]);
            //var/database/query/q=new({"INSERT INTO [table]([usr.key]) VALUES(NAME);"})
            var/database/query/q=new({"INSERT INTO [table] (`NAME`) VALUES ('[usr.ckey]');"})
            if(q.Execute(gq))
                world<<"worked"
            else
                world<<q.ErrorMsg()
        
        
        GrabDB()
            set category="Database"
            var/database/query/f=new({"SELECT `[currency_name]` FROM `[table]` WHERE `NAME`= '[usr.ckey]';"})
            if(f.Execute(gq))
                var/list/ex=f.GetRowData()
                var/amount=text2num(ex["[currency_name]"])
                for(var/i in ex)
                    world<<"worked grabbed [i] : [amount]"
            else
                world<<f.ErrorMsg()
        
        
        GrabAllInfo()
            set category="Database"
            var/database/query/f=new({"SELECT * FROM `[table]` WHERE `NAME`= '[usr.ckey]';"})
            if(f.Execute(gq))
                var/list/ex=f.GetRowData()
                //var/amount=text2num(ex["[currency_name]"])
                for(var/i in ex)
                    world<<"worked grabbed [i] : [ex[i]]"
            else
                world<<f.ErrorMsg()


Problem description:
Can't seem to get the Update or Insert Queries to work. Any ideas on what I'm missing or messing up? This is the file pertaining to database work in it's entirety.

Note: I'm trying to use SQLite to utilize the database features of BYOND, do I still need to include the libmysql.dll? that's the only thing I haven't tried

Feb 27 2015, 1:53 pm (Edited on Feb 27 2015, 2:04 pm)
Hmm, for my 'two cents' i'd say what you might want to do is try tweaking your actual sql statements a little, because they do seem a little weirdly put together with the single quotations and such, just wondering if it might be because of that, that it might be why the queries aren't executing.

I.e.
      //[...Removed Code...]
        UpdDB()
            set category="Database"
            .=rand(5,999)
            //var/database/query/w=new({"UPDATE [table] SET [currency_name] = 999 WHERE NAME = [usr.key];"})
            var/database/query/w=new({"UPDATE `[table]` SET  `[currency_name]` =  '999' WHERE  `NAME` =  '[usr.ckey]';"})
            if(w.Execute(gq))
                world<<"worked"
            else
                world<<w.ErrorMsg()


        InzDB()
            set category="Database"
            set name="Insert Player Into DB"
            //INSERT INTO [table](NAME) VALUES([usr.key]);
            //var/database/query/q=new({"INSERT INTO [table]([usr.key]) VALUES(NAME);"})
            var/database/query/q=new({"INSERT INTO [table] (`NAME`) VALUES ('[usr.ckey]');"})
            if(q.Execute(gq))
                world<<"worked"
            else
                world<<q.ErrorMsg()


I'd suggested changing so it becomes:
        UpdDB()
            set category="Database"
            .=rand(5,999)

            var/database/query/w=new("UPDATE [table] SET  [currency_name] =  999 WHERE NAME =  '[usr.ckey]';")

                                                  //NAME should match what you've titled the particular
                                                  //field you're looking to change

//btw if the field referred to by [currency_name] holds
//values you've defined as integers within the database,
//then no need for the use of the quotation marks, that
//is for strings or i should say text, so '999'-> 999


            if(w.Execute(gq))
                world<<"worked"
            else
                world<<w.ErrorMsg()


        InzDB()
            set category="Database"
            set name="Insert Player Into DB"

            var/database/query/q=new("INSERT INTO [table] (NAME) VALUES ('[usr.ckey]');")
            if(q.Execute(gq))
                world<<"worked"
            else
                world<<q.ErrorMsg()
Feb 27 2015, 3:32 pm
Best response
The problem is, you're never initializing the 'gq' variable, there's absolutely nothing in your code telling the system to point at a file, in fact, the closest you come is your "db" variable, which you use exactly nowhere in your code.

var/database/gq = new(db)


I'm honestly surprised this wasn't throwing out a bunch of runtime errors for trying to Execute() on a non-existent /database.
Feb 27 2015, 10:28 pm
In response to Nadrew
Nadrew wrote:
The problem is, you're never initializing the 'gq' variable, there's absolutely nothing in your code telling the system to point at a file, in fact, the closest you come is your "db" variable, which you use exactly nowhere in your code.

> var/database/gq = new(db)
>

I'm honestly surprised this wasn't throwing out a bunch of runtime errors for trying to Execute() on a non-existent /database.

If you'll notice at the top of the code snippet I comment that I initialize the database upon world/new().

That part was in a seperate file,inside of a mostly unrelated proc- so I just didn't bother to include it.
Feb 27 2015, 10:32 pm
In response to Turboskill
I've tried it all kinds of ways, with the '' and without. Strangely when I use something like
[usr.key]

I get a column error stating that there's no such column as the client key.
Obviously I know that already so I tried it with the ' - and it works properly.

I maybe should start using ?'s and the built in Database.query.Add() functionality to see if it yields different results. So far though, I cannot 1) Get the database to update correctly and 2) Insert any new data into the database. In fact t he only real thing I can do is create a table and create the database itself.
Feb 28 2015, 12:13 pm
Well, not that i believe i'll be of much help in this case -not really had anything to do with byond's database stuff yet- but d'you mind posting here what exactly the complete statements looks like that you feel are naturally the correct ones to use to do what you wish, but gives you that 'no such column as client key' error message? so i can try and see if it might be something solve-able in how you write the statements themselves
Feb 28 2015, 11:35 pm
In response to Turboskill
These are the codes that don't throw errors.
They just don't work for updating the database or inserting into the database.
Mar 1 2015, 5:45 am
In response to Avidanimefan
Right, but i said the codes you've previously tried (and seem should be the most correct way to write the statements) that do give you the errors, specifically the 'no such column as client key' thing you mentioned.
Mar 1 2015, 6:52 am
In response to Turboskill
Those are already included- as you can see they are commented out in the OP.
Mar 1 2015, 8:33 am
Using Dantom DB lib this works properly.
proc
    InDB(var/mob/m)
        set background = 1
        if(isnull(m))return
        spawn()
            var/DBQuery/query=my_c.NewQuery("SELECT `[mypoints]` FROM `[mytable]` WHERE `[myuser]`='[m.ckey]' LIMIT 1")
            query.Execute()
            if(isnull(query))
                return 0
            else
                world<<"found in database."
                return 1


    CAcct(mob/m)//called for all people.
        set background = 1
        spawn()
            if(!isnull(m))
                if(my_c.IsConnected())
                    if(!InDB(m))
                        var/DBQuery/query=my_c.NewQuery({"INSERT INTO `[mytable]` (`[myuser]`,`[mypoints]`) VALUES ('[m.ckey]','0');"})
                        query.Execute()
                        m<<"Account Created."

                else
                    my_c=new
                    spawn()
                        connected=my_c.Connect(DBI,username,password)
                        m<<"Attempting to connect to database."
                        if(my_c.IsConnected())
                            m<<"Connect success."
                            if(!InDB(m))
                                var/DBQuery/query=my_c.NewQuery({"INSERT INTO `[mytable]` (`[myuser]`,`[mypoints]`) VALUES ('[m.ckey]','0');"})
                                query.Execute()
                                m<<"Account Created."
                            else
                                m<<"Account found. Welcome back [m.key]."

                        else
                            m<<"Unable to create account."



This is an older snippet. I'm seeking to get this working using the database datum. I'll try copy pasting directly into my queries this info and see if it still works.
Mar 1 2015, 9:23 am
Had to go old school to get anywhere with this issue:



Seems the issue is that the text isn't being sent properly to the database.
Mar 1 2015, 12:47 pm
One big question here is: Why are you using grave accents instead of single or double quotes?
Mar 1 2015, 1:42 pm
Grave accents are an accepted syntax in SQL, they're actually quite commonplace.
Mar 1 2015, 2:56 pm
 
/*V 2- Note that I initialize both the qry and gq vars in another file.*/

var/global
    database/gq//=new("mydata.db")
    database/query/qry
    const
        db="Data.db"
        table="CharPlus"
        currency_name="GOLDENRYO"
        myuser="NAME"


proc
    CheckDB()
        if(qry.Execute(gq))
            world<<"worked"
        else
            return qry.ErrorMsg()


mob
    verb
        MakeTable()
            set category="Database"
            qry.Clear()
            qry.Add("CREATE TABLE [table] (?,?)","NAME string","GOLDENRYO int")
            if(qry.Execute(gq))
                world<<"Table created."
            else
                world<<qry.ErrorMsg()



        UpdDB()
            set category="Database"
            qry.Clear()
            qry.Add("UPDATE [table] SET  [currency_name] =  ? WHERE  NAME =  ?",999,usr.ckey)
            if(qry.Execute(gq))
                world<<"Database updated."
            else
                world<<qry.ErrorMsg()



        InzDB()
            set category="Database"
            set name="Insert Player Into DB"//[lowertext(usr.ckey)]
            qry.Clear()
            qry.Add("INSERT INTO [table] (NAME,GOLDENRYO) VALUES (?,?)",usr.ckey,0)
            if(qry.Execute(gq))
                world<<"Inserted into data."
            else
                world<<qry.ErrorMsg()



        GrabDB()
            set category="Database"
            set name="Select From Db"
            qry.Clear()
            qry.Add("SELECT [currency_name] FROM [table] WHERE [myuser]= ?",usr.ckey)//
            if(qry.Execute(gq))
                var/list/ex=qry.GetRowData()
                for(var/i in ex)
                    if(i==null)
                        world<<"null check"
                    else
                        world<<"[i] [ex[i]]"
            else
                world<<qry.ErrorMsg()


And also, this still does not allow me to insert into the database successfully. (So I can't really even tell if the update works properly.)

I did this according to the f1 help document but if there are any errors please point them out.
Mar 1 2015, 4:08 pm
Is the file being properly generated? Is it just the INSERT/UPDATE queries that are failing? Have you tried executing the same queries using an external SQLite editor? I see you tried using the command-line and it still failed this could indicate something on your system is preventing the file from being appended.

I've been using SQLite quite extensively lately and I haven't run across any issues like this.
Mar 1 2015, 4:26 pm
If you don't mind then could you post a snippet of an insert query direct from a source that is verified?
The only thing I haven't done is take the database file (which is generated) and used the cmd line prompt to run inserts and queries on it but I shall do that shortly.
Mar 1 2015, 4:59 pm (Edited on Mar 1 2015, 5:04 pm)
-_-

Upon studying the f1 help a bit more I came across NextRow() proc which lead me to try this :

        GrabDB()
            set category="Database"
            set name="Select From Db"
            qry.Clear()
            qry.Add("SELECT * FROM CharPlus")//
            if(qry.Execute(gq))
                var/list/info=list()
                while(qry.NextRow() )
                    var/list/ex=qry.GetRowData()
                    for(var/i in ex)
                        if(i==null)
                            world<<"null check"
                        else
                            world<<"[i] [ex[i]]"
                            info.Add(i)
                            info[i]=ex[i]
                world<<"Gotten Data. [info["GOLDENRYO"]]."
            else
                world<<qry.ErrorMsg()


Which works as expected:
EDIT:
ALSO I removed all of the [] related text fields just to make sure nothing funny was going on. Ideally I'd like to use those as well.

I feel a bit dumb now but at least I know how to do it, is there a simpler way to go about grabbing data from a database or is this it?

Mar 1 2015, 5:35 pm
Your question had nothing to do with SELECT, so I assumed you were successfully verifying the data was in fact failing to load. As with all SQL syntax usage you need to iterate through the results using NextRow() or you're never actually navigating through the data structure.
Mar 1 2015, 5:54 pm
In response to Nadrew
Nadrew wrote:
Your question had nothing to do with SELECT, so I assumed you were successfully verifying the data was in fact failing to load. As with all SQL syntax usage you need to iterate through the results using NextRow() or you're never actually navigating through the data structure.

Not failing. It seems it worked properly. I was just neglecting to use nextrow(), so I couldn't verify the results, thanks for the help.