• You've discovered RedGuides 📕 an EverQuest multi-boxing community 🛡️🧙🗡️. We want you to play several EQ characters at once, come join us and say hello! 👋
  • IS THIS SITE UGLY? Change the look. To dismiss this notice, click the X --->
MQ2SQLite

Plugin - MQ2SQLite (1 Viewer)

Redbot

🖥️💖
Moderator
Joined
Oct 15, 2004
RedCents
88,152¢
Pronouns
He/Him
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...
 
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
 
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...
 
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
 
Last edited:
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.
 
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

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

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

fails:
/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars (Name, Level) VALUES (${Me.Name}, ${Me.Level})
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}"
 
Could it be in the working example your column is named "Level" and in the non-working example your column is named "Levels" ?
 
sql:
/sqlite query C:\Users\vdell\Sync\sql\eq.db InsertChar INSERT INTO chars VALUES ('${Me.Name}', ${Me.Level})

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

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};
 
Thank you guys for all the help! )

Got the following to work:

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}" )

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.

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}')

DDL for the table is as follows:
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)
);

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}', ...

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}')
 
Last edited:
Plugin - MQ2SQLite

Users who are viewing this thread

Back
Top