41 replies to this topic

[IsmAvatar]

A few people were asking for this, so I figured I'd quick slap one together. Notice, you *do* need to know SQL in order to make use of this DLL, or at least SQL as it pertains to SQLite.

SQLite is database software that doesn't require a server, like MySQL does. Instead, it does everything in a file. It's also simplified down a lot, so a lot of the more advanced features of MySQL are not there. Furthermore, this DLL only wraps *some* of the SQLite functions (the ones that I felt would most commonly be used), and not all of them. If you need some of the more advanced functionality, I've provided the source of the dll so you can add them in and compile it yourself, or whatever floats your boat.

Notice, all files are free to modify and redistribute as you see fit.
Contents:
hat.c - the C source code, in case you're interested
hat.dll - the dll that you include with your GM game (includes the wrapper and SQLite itself, which is why it's so large. It compresses well, but sadly you can't really use a compressed dll without decompressing it first)
hat.gm6 - a game maker editable to demonstrate *SOME* of the functionality of the dll
hat.txt - documentation on *all* of the functions provided by this dll

Don't ask me why it's called "hat", I really don't know. If you don't like the name, feel free to change it to anything else. I just couldn't call it GMSQLite because that's already taken.

Download
http://www.IsmAvatar...dlls/sqlite.zip

The file is 185 KB, and almost all of that is the dll. As mentioned above, it's so large because SQLite itself (in the dll) is quite large.

Credit
Just call me IsmAvatar. You might also put in a little note, like "SQLite dll" or something. You don't need to ask me if/how you can credit me, and I don't really care what you use this in. Once you've downloaded it, it is *your* copy and you can do with it what *you* want. If you really want to "pay me your respects", I don't accept donations, but you can either help improve this dll, or come visit me at my primary project, LateralGM (link in sig).

Question: That file is huge! Why should I use this when I could just code my own pseudo-database in GM?
Answer: That's completely up to you to decide. In many cases, it really is a better idea to just code your own using file functions or what not - and usually it's faster since you don't have to go through the dll and the SQLite interface (which needs to parse your SQL commands). However, as your data set gets larger and harder to handle, and especially as you get more data that links to other data, it becomes harder and harder to code it, that is when you would consider using sqlite. When your data set gets really large, like thousands of entries or such, you should definately consider upgrading to MySQL or another database program.

Database? Aren't those expensive?
On the contrary, MySQL, SQLite, and this dll are all FREE. Not only are they free of charge, they're also open source so you can see how they work, modify the source code, and redistribute as you see fit! However, big companies like Microsoft and Oracle like to charge lots of money (like thousands) and they don't even give you the source code, nor let you modify it, nor redistribute it (unless you pay extra for a license that allows it). On the positive side, though, when you pay that kind of money, you get great tech support, and probably a lifetime guarantee that the program won't break.

Can I mirror this?
I'm not going to stop you, but I'm also probably not going to post a link to your mirror in this post. My host is perfectly fine as it is.

User Contributions
Some users have contributed scripts and examples to help with this dll. Here are the posts;
GML Scripts for the DLL by ari_aaron
Example with 'less complex way' to define dlls and call the functions (includes "SQLite Database Browser") by Sindarin
A programmer friendly coding of "looking up" by ChIkEn AtE mY dOnUtS

DackR's modifications and extension - Renamed functions to better names, and provides an extension, free to modify/use, thanks to DackR (direct download link)

Edited by IsmAvatar, 31 May 2011 - 02:59 AM.

[posva]

it seems to be good, but i have one question, has this the feature to cnnect to the internet? i think that no( i ha've tried it)

anyway it's interesting

[IsmAvatar]

No, that's what MySQL is for.

[posva]

so this create local database isn't it?

[IsmAvatar]

Yes, it uses a file, which you specify when you run sopen(<filename>), and stores the database in that file.

[posva]

Ok

[tuntis]

This is really great. I'm sure that a lot of projects will benefit from this.

[death-droid]

Awesome job ismavatar I might end up using this.

[ash47]

im gonna use this if i can work out how to use it...

[IsmAvatar]

Just read the extensive documentation and look at the example, I'm sure you'll get a hang of it. I know the SQLite can be a little tricky to get the hang of - I had to do a bit of debugging just to get the example working, even after the dll itself was done.

[Sindarin]

This is really.. really useful. Especially for games with large data such as RPGs.

Don't ask me why it's called "hat", I really don't know. If you don't like the name, feel free to change it to anything else. I just couldn't call it GMSQLite because that's already taken.

You could name it GMDB.

Furthermore, this DLL only wraps *some* of the SQLite functions (the ones that I felt would most commonly be used), and not all of them

If it wraps SORT BY, UPDATE, AND, WHERE, LIKE then it's fine with me. I'll have to try those out. And thanks for the source, I'll experiment with it too.

[ash47]

i get it pretty much after i read theough some stuff on a site, just it wont let me add TEXT instead of INTEGER, how must i do that?

edit: it writes the data, just returns 0 when tries to read it, what must i change?

edit2: external_call(dbl,qq,1) where dbl is trying to get a sdouble, how do i make it get text?

edit3: How do i return the text values, not just doubles

edit4: this is pointless unless i can get those text values!!!

Edited by elite69, 19 October 2008 - 01:46 AM.

[IsmAvatar]

The example is just that, an example. As I mentioned above, there's a few other functions that it does not demonstrate, such as text. Please read the documentation to see how to define and call them. Also, please familiarize yourself with GM's dll-handling functions so that you can figure out how to define and call them yourself, rather than relying on modifying examples.

[NiCk™]

Thanks Ism this DLL is very useful.

[ash47]

i nvr learned DLLs cause i dont know c++, i didnt notice any documentation... my bad

edit: too hard for me lol

Edited by elite69, 19 October 2008 - 05:32 AM.

[IsmAvatar]

You don't need to know any languages aside from SQL and GML. When I told you to learn the dll functions, I meant the GML dll functions, which can be found in the Manual > GML > Using DLLs. You might also try looking at some other dlls to see how they are called from within GM.

Basically, if you look at the documentation, you'll find an entry on stext, which gets text, rather than a double. However, the def() function cannot be used to define this, since def() can only be used on functions that return ty_real, whereas stext returns ty_string, so you'll need to use external_define.

[ash47]

with all that text you just typed, you could have given me a one line answer and it would have been quicker, i found the stext thingy b4 but i couldnt get it to work, i got -34543543543543 instead of a text string

[Sindarin]

Yes I have the same problem.

defined,

txt = def("stext",2,0,0)

changed,

query(db,"CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT)")
query(db,"INSERT INTO t1 VALUES(1,'test')")

and,

//now print out the table contents
qq = external_call(qry,db,"SELECT * FROM t1 ORDER BY a ASC") //query
while (external_call(step,qq) = 1) //fetch rows
 ask("id: "+string(external_call(dbl,qq,0)) //get double `a`
  + " row content: " + string(external_call(txt,qq,1))) //get string `b`

do I miss anything?

btw, I found in your source code:

export const char *stext(double qry, double col) {
return sqlite3_column_text(get(qry),(int)col);

Shouldn't the datatype returned be other than (int) ?

Edited by Sindarin, 19 October 2008 - 10:31 AM.

[ash47]

yay, thnak you, i can now read text i will try it out tomorrow :D:D:D:D

[Sindarin]

yay, thnak you, i can now read text

what did you do? mind sharing the solution? XD

EDIT:

i will try it out tomorrow :D:D:D

Oh, you meant you haven't tried it yet. The above code doesn't work, I was asking Ism for a solution too.

hat.c - the C source code, in case you're interested

Also Ism, which sqlite3 .h file version you used? Can you please include it in your zip? Because the one I got from the website throws up linker errors on my compiler (using Dev C++)

Edited by Sindarin, 19 October 2008 - 12:19 PM.

[IsmAvatar]

export const char *stext(double qry, double col) {
return sqlite3_column_text(get(qry),(int)col);

This is intended. It is an argument type, not a return type. You pass in a double (since GM only supports double and string), but sqlite3_column_text expects an int, so I needed to typecast the double to int in order for sqlite3_column_text to accept it.

Also, as I mentioned above, and apparently you ignored:

However, the def() function cannot be used to define this, since def() can only be used on functions that return ty_real, whereas stext returns ty_string, so you'll need to use external_define.

[Sindarin]

EDIT:

It worked!

I define it,

txt=external_define("hat.dll","stext",dll_cdecl,ty_string,2,ty_real,ty_real)

add the text values I want,

//run some basic queries. See query() script for how they work.
  query(db,"DROP TABLE IF EXISTS t1")
  query(db,"CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT)")
  query(db,"INSERT INTO t1 VALUES(1,'potion')")
  query(db,"INSERT INTO t1 VALUES(2,'potion2')")
  query(db,"INSERT INTO t1 VALUES(NULL,'ether')") //auto-increment ID on null
  ask(external_call(lid,db))

and then,

//now print out the table contents
  qq = external_call(qry,db,"SELECT * FROM t1 ORDER BY b") //query
  while (external_call(step,qq) = 1) //fetch rows
   ask(string(external_call(dbl,qq,0)) //get double `a`
	+ " " + string(external_call(txt,qq,1))) //get text `b`

Edited by Sindarin, 19 October 2008 - 09:39 PM.

[IsmAvatar]

I've attached an example that uses your modified code.

string(external_call(txt,qq,1))) //get text `b`

Don't string a string

Edited by IsmAvatar, 19 October 2008 - 10:15 PM.

[Sindarin]

string(external_call(txt,qq,1))) //get text `b`
Don't string a string

XD Oh my... I need sleep.


EDIT:

Whoops in the example you forgot to put

while (external_call(step,qq) = 1) //fetch rows
 ask(string(external_call(dbl,qq,0)) //get double `a`
  + " " + external_call(txt,qq,1)) //get text `b`

txt instead of dbl. Guess we both need good sleep. XD

Edited by Sindarin, 19 October 2008 - 11:39 PM.

[IsmAvatar]

You need sleep, I just need a conk on the head. Fixed.

[ash47]

i modified the reading code so that i can use simple commands to call it. Thanks for DLL, will probobly credit, if i use it...

[Catan]

That's a very useful extension! I tried it and it seems to work perfectly, but is there a way to fetch row data by column name instead of column number?

[IsmAvatar]

To my knowledge, SQLite itself does not offer an easy way to do this, so this DLL also does not.

That said, however, SQLite does offer a way to fetch the name of a column number, and a way to get how many columns there are. Using these, you could loop through every column, get its name, and compare against the desired name. This obviously would not be very fast. Furthermore, none of this functionality has been coded into the dll, so you'd need to code it yourself.

[pete_wilde]

Really nice piece of work.

Many thanks,

Pete

Edited by pete_wilde, 25 January 2009 - 04:21 PM.

[Schyler]

Cool DLL

[ari_aaron]

If anyone wants, I made GML scripts for the DLL, so you don't have to manually define things.


link (right click>save as)

[pete_wilde]

That is so cool :-)

The only command that seems to be missing is a "load data" command to quickly populate a table from a text file. That would make uploading information really quick and easy.

Still, it's a fantastic DLL.

If anyone wants, I made GML scripts for the DLL, so you don't have to manually define things.


link (right click>save as)

[Sindarin]

Created an example with the 'less complex way' to define dlls and call the functions, also both double values and text strings are displayed, hope it helps some people here, also contains the open source program "SQLite Database Browser" ( http://sqlitebrowser.sourceforge.net/ ) to easily manage your databases:

http://www.mediafire...php?zitzdqot4ym

Edited by Sindarin, 04 April 2009 - 11:58 PM.

[ChIkEn AtE mY dOnUtS]

To my knowledge, SQLite itself does not offer an easy way to do this, so this DLL also does not.

That said, however, SQLite does offer a way to fetch the name of a column number, and a way to get how many columns there are. Using these, you could loop through every column, get its name, and compare against the desired name. This obviously would not be very fast. Furthermore, none of this functionality has been coded into the dll, so you'd need to code it yourself.

I've coded a method of looking up. Here's my version of this:

SQLite By Ism Avatar, Modified by me.

I tried to make it more programmer friendly to use. A lot of stuff has been automated, etc.

[pete_wilde]

To my knowledge, SQLite itself does not offer an easy way to do this, so this DLL also does not.

That said, however, SQLite does offer a way to fetch the name of a column number, and a way to get how many columns there are. Using these, you could loop through every column, get its name, and compare against the desired name. This obviously would not be very fast. Furthermore, none of this functionality has been coded into the dll, so you'd need to code it yourself.

I've coded a method of looking up. Here's my version of this:

SQLite By Ism Avatar, Modified by me.

I tried to make it more programmer friendly to use. A lot of stuff has been automated, etc.

Just tried to download the file - got the following error messages:

Warning: mkdir() [function.mkdir]: Too many links in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 57

Warning: fopen(zips/679284/SQLite.gmk) [function.fopen]: failed to open stream: No such file or directory in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 66

Warning: fwrite(): supplied argument is not a valid stream resource in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 67

Warning: fclose(): supplied argument is not a valid stream resource in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 68

Scripting languages and batch files are not allowed.

[ChIkEn AtE mY dOnUtS]

To my knowledge, SQLite itself does not offer an easy way to do this, so this DLL also does not.

That said, however, SQLite does offer a way to fetch the name of a column number, and a way to get how many columns there are. Using these, you could loop through every column, get its name, and compare against the desired name. This obviously would not be very fast. Furthermore, none of this functionality has been coded into the dll, so you'd need to code it yourself.

I've coded a method of looking up. Here's my version of this:

SQLite By Ism Avatar, Modified by me.

I tried to make it more programmer friendly to use. A lot of stuff has been automated, etc.

Just tried to download the file - got the following error messages:

Warning: mkdir() [function.mkdir]: Too many links in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 57

Warning: fopen(zips/679284/SQLite.gmk) [function.fopen]: failed to open stream: No such file or directory in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 66

Warning: fwrite(): supplied argument is not a valid stream resource in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 67

Warning: fclose(): supplied argument is not a valid stream resource in /home/content/w/i/l/willhostforfoo/html/zipdownload.php on line 68

Scripting languages and batch files are not allowed.

Just download the whole package.

Edited by ChIkEn AtE mY dOnUtS, 23 August 2009 - 04:47 PM.

[ze1]

Sorry to revive an old topic, but ChIkEn AtE mY dOnUtS's dll modification isn't available because it was hosted in willhostforfood. =( Does anyone still have the file and can upload it?

[Revel]

Thanks! This is very useful.

Here is a useful tool for the rest of you:

SQLite Browser

Its a gui interface for editing tables. It works great.

[DackR]

I know this is resurrecting an OLD topic, but I thought this would be a fitting place to give an update to this extension.

I have searched and searched for a newer version, but have been unable to find anything. So... I ended up compiling the latest version of SQLite3 (3.7.6.3) and making an extension utilizing Ismavatar's hat.c code.

I've included all of the source code (C source/GED file) that I used to create the extension. I also used the freely available Bloodshed Dev-C++ to build the DLL file.

Keep in mind that I changed all of the function names used in Ismavatar's original extension to those more meaningful to me and I added the function that returns the total number of columns returned in the query.

Tested in GM8/8.1.

DOWNLOAD RAR:
http://www.mediafire...c76eb8pj0qd2vjw

DOWNLOAD ZIP:
http://www.mediafire...gd989ndc4ctfo4n

Please feel free to modify/add functions to make the extension more complete.

[IsmAvatar]

Thank you, DackR, it's nice to see that some of these are still useful and generating interest with some people. I've mirrored your files and provided links in the first topic.

[DackR]

Thank you, DackR, it's nice to see that some of these are still useful and generating interest with some people. I've mirrored your files and provided links in the first topic.

My hat is off to you, sir. You have made my life easier.

[komarios]

Hi,

This topic is rather old but I'll give it a shot.

I was wondering if this dll could be ported to android?

Or if anyone has heard of an alternative?

Thanks.

Marios