Release MQ2SQLite

[Redbot]

Redbot submitted a new resource:

MQ2SQLite - Interact with a SQLite database

This plugin allows you to interact with a SQLite database. A SQLite database is just a file that
can be accessed using SQL commands. This plugin gives you access to the SQLite API and adds
a TLO for viewing the results.
Since the results of the query are stored in memory, this can take up a lot of memory if you use
unique query names and do not clear your results from memory. So, don't do that. Be sure to use
the .clear function when you're done accessing the results or continue to use...

Read more about this resource...

 

[Redbot]

Redbot updated MQ2SQLite with a new update entry:

library update

Upgraded library to 3.28, add ResultCode TLO (req. exspes) that returns the SQLite Result Code or -1 for an Active Query or -2 for Query Not Found.

Read the rest of this update entry...

 

[Knightly]

Oh, you poor unfortunate resource that looks like you never get updated. Major update inc. Read the readme.

Added advanced functionality
- Can now choose to keep a database open rather than open and close on each query
- Deprecated the .clear TLO in favor of the clear command line argument (in Live this will only notify you 5 times per plugin load/unload)
- Added the query verb to specify that this is a standard query
- Added the open/close verbs to open and close databases
- Added the AdvQuery verb to specify that you want an advanced query

 

[Knightly]

Redbot updated MQ2SQLite with a new update entry:

Major update - Added advanced functionality

Can now choose to keep a database open rather than open and close on each query
Deprecated the .clear TLO in favor of the clear command line argument (in Live this will only notify you 5 times per plugin load/unload)
Added the query verb to specify that this is a standard query
Added the open/close verbs to open and close databases
Added the AdvQuery verb to specify that you want an advanced query
Code cleanup

Read the rest of this update entry...

 

[jb321]

Does this do BULK, OPENROWSET, all that cool stuff?

If it does I am having a heckuva time getting it to work..

/sqlite query macros\Artisan\TD.db DBT SELECT * FROM OPENROWSET(BULK 'd:\mb.txt', SINGLE_CLOB) AS Contents

OR

/sqlite query macros\Artisan\TD.db DBT COPY tdtable FROM 'd:/mb.txt' DELIMITER ' ';

yeah a little reading goes a long way... methinks this can't handle some of these operations , they are SQL based not sqlite

 

[Knightly]

That looks like Transact-SQL (TSQL) which would only work on Microsoft's SQL server. However, SQLite does have file based operations. For example, you can .import a csv file and you can use a virtual database if you don't want it imported into a file.

 

[playj]

What is the syntax for inserting TLO values into SQLite db ?

I am trying to save my character info to database.

Hard coded values work, as in the following example

[CODE lang="sql" title="works"]/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars (Name, Level) VALUES ("Foobar", 110)[/CODE]

Trying to put ${Me.Name} and ${Me.Level} directly into insert does not work

[CODE lang="sql" title="fails"]/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars (Name, Level) VALUES (${Me.Name}, ${Me.Level})[/CODE]
Edit: fixed Level but it fails silently now, (with wrong column name at least I got some error message)

Any ideas?

PS I also tried "${Me.Name}"

 

[Coldblooded]

Could it be in the working example your column is named "Level" and in the non-working example your column is named "Levels" ?

 

[playj]

Could it be in the working example your column is named "Level" and in the non-working example your column is named "Levels" ?

Good catch but that is not it! When I fix column name to Level it still fails - worse it fails silently - no error but no row is inserted.

 

[snoworc]

[CODE title="sql"]/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars VALUES ('${Me.Name}', ${Me.Level})[/CODE]

You just need to add the single quote around string values, i am guessing.

[CODE title="example of a working query i am using (var names changed)"]/sqlite query foo.db foo_query INSERT INTO foo VALUES('${hashKey}', '${Me.CleanName}', '${Me.Class.Name}', '${Requester}', '${requestedItem}', 1, ${itemCount}) ON CONFLICT(my_hash) DO UPDATE SET state=1, requested_item_count=${itemCount};[/CODE]

 

[Knightly]

Can do single or double quotes. Double is useful for strings that contain single quotes and vice versa.

 

[playj]

Thank you guys for all the help! )

Got the following to work:

[CODE lang="sql" title="Works"]/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars (Name, Level, AA, Class, Server, Guild) VALUES ('${Me.Name}', '${Me.Level}', '${Me.AAPointsAssigned}', '${Me.Class}', "${EverQuest.Server}", "${Me.Guild}" )[/CODE]

However, I can't seem to insert ${Me.Platinum} nor ${Me.Krono} - they work fine in echo

Namely the following line does not work - I get the error message.

[CODE lang="sql" title="Fails"]/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars (Name, Level, AA, Class, Server, Platinum, Krono, Guild) VALUES ('${Me.Name}', '${Me.Level}', '${Me.AAPointsAssigned}', '${Me.Class}', '${EverQuest.Server}', '${Me.Platinum}' ,'${Me.Krono}', '${Me.Guild}')[/CODE]

DDL for the table is as follows:
[CODE lang="sql" title="Schema"]CREATE TABLE Chars (
CharsID INTEGER PRIMARY KEY AUTOINCREMENT,
Account VARCHAR (64),
Name VARCHAR (64),
Level INTEGER,
AA INTEGER,
Class VARCHAR (32),
Server VARCHAR (64),
Platinum INTEGER,
Krono INTEGER,
Guild VARCHAR (64),
Bind VARCHAR (64),
Jewelcraft INTEGER,
Note VARCHAR (256),
Created DATETIME,
Updated DATETIME DEFAULT (CURRENT_TIMESTAMP)
NOT NULL,
BanCheck DATETIME,
AllAccess VARCHAR (32)
);[/CODE]

Surely if Level works then Platinum and Krono should work too/

Maybe there is a *facepalm* typo somewhere?

EDIT: Found the problem! Comma had to be immediately after '${Me.Platinum}', ...

[CODE lang="sql" title="Fixed, works"]/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars (Name, Level, AA, Class, Server, Platinum, Krono, Guild) VALUES ('${Me.Name}', '${Me.Level}', '${Me.AAPointsAssigned}', '${Me.Class}', '${EverQuest.Server}', '${Me.Platinum}', '${Me.Krono}', '${Me.Guild}')[/CODE]