[SELL] SQLite Database Plugin

[fastlinks 16]

Hi, is it possible to connect to a password protected Sqlite DB?

 

how to write the connection string? as the plugin only ask for the path.

 

Thanks

[Bot-Factory 602]

Hi, is it possible to connect to a password protected Sqlite DB?

 

how to write the connection string? as the plugin only ask for the path.

 

Thanks

I think this is currently not supported with the plugin.

 

Dan

[Aymen 385]

Hi, is it possible to connect to a password protected Sqlite DB?

 

how to write the connection string? as the plugin only ask for the path.

 

Thanks

 

You can't do that for now , i'll add it in a future update!

[Bot-Factory 602]

Hello.

 

I have some issues to execute a query with WHERE xx  LIKE 'yy'

The sql query works fine with the sqlite command line tool. But when I try to run it with the plugin it doesn't work.

 

Seems to be related to the LIKE command. 

 

Here's the complete query:

select urls from profiles WHERE Sprachen  LIKE 'deutsch'

 

Thanks in advance for your help

Dan

[Bot-Factory 602]

Update:

 

Seems to be related to the size of the database. It works for a small DB. But for a large one with 5Million entries this doesn't work correctly.

But when I run the query with my sqlite admin tool (sqlite expert professional), the query executes in 99ms. 

 

Update2:

Ok this issue is clearly a time problem. I had to wait a lot longer for the query to finish. 

 

So the following query:

select urls from profiles WHERE Sprachen  LIKE 'deutsch'

 

Took  95ms in my SQLite Tool.

 

With the UBot Plugin the same query against the same database took:

2minutes and 47 seconds.

 

Any idea why this happens? Can we improve that?

 

Thanks

Dan

[kev123 132]

Update:

 

Seems to be related to the size of the database. It works for a small DB. But for a large one with 5Million entries this doesn't work correctly.

But when I run the query with my sqlite admin tool (sqlite expert professional), the query executes in 99ms. 

 

Update2:

Ok this issue is clearly a time problem. I had to wait a lot longer for the query to finish. 

 

So the following query:

select urls from profiles WHERE Sprachen  LIKE 'deutsch'

 

Took  95ms in my SQLite Tool.

 

With the UBot Plugin the same query against the same database took:

2minutes and 47 seconds.

 

Any idea why this happens? Can we improve that?

 

Thanks

Dan

is this the command that returns into a ubot table, how many rows if its a lot then the bottleneck is loading into the ubot table. One quick fix is to use the limit SQLite command process being.

 

1.check how many rows your database has for the query.

2.use the SQLite limit command to read out in chunks of 500.

3.add to something like a large table.

4.repeat to all are read out.

[Bot-Factory 602]

is this the command that returns into a ubot table, how many rows if its a lot then the bottleneck is loading into the ubot table. One quick fix is to use the limit SQLite command process being.

 

1.check how many rows your database has for the query.

2.use the SQLite limit command to read out in chunks of 500.

3.add to something like a large table.

4.repeat to all are read out.

Thanks Kev.

 

tested that as well. But that's even slower. Probably because I have to use offset in combination with LIMIT. 

 

I also tried to user "where rowid between 1 AND 50000"

but that takes over 2 minutes as well.

 

Even when I use "save to file". So no variables or anything involved.

 

Same query executes in 39ms in my tool. 

 

So this looks like a performance issue with the sqlite plugin to me?

 

@Aymen: Any idea why this could happen?

 

Thanks

Dan

[Aymen 385]

Thanks Kev.

 

tested that as well. But that's even slower. Probably because I have to use offset in combination with LIMIT. 

 

I also tried to user "where rowid between 1 AND 50000"

but that takes over 2 minutes as well.

 

Even when I use "save to file". So no variables or anything involved.

 

Same query executes in 39ms in my tool. 

 

So this looks like a performance issue with the sqlite plugin to me?

 

@Aymen: Any idea why this could happen?

 

Thanks

Dan

 

I'll check that out Dan!

[Bot-Factory 602]

I'll check that out Dan!

Hi Aymen. 

 

Have you had time to look into that already?

 

Thanks

Dan

[Bot-Factory 602]

I'll check that out Dan!

 

Hi Aymen,

 

any news on the performance issue?

 

Cheers

Dan

[Aymen 385]

Hi Aymen,

 

any news on the performance issue?

 

Cheers

Dan

Been real busy Dan , i'll try to check it out soon!

[tripros 16]

Regardless of how I set up the db path and what sql command I use, I continue to get the "Object reference not set to an instance of an object" error. Can create a db file, that works, all other sql commands give me this error and can not show any data using the get data command.

 

Tried to create and populate the db file using sqliteexpert as Dan suggested (great program BTW, thanks Dan!) and then get data using ubot, still get the object error and 0 results. Tried this using the special folder (my documents) as well as direct path e:\dbname.sqlite and get the same error.

 

Even tried the exact same data and examples Aymen showed in the video, getting same error.

 

Using latest version (I assume, just downloaded a few weeks ago)

 

Any suggestions?

 

Thanks much!

[Bot-Factory 602]

Regardless of how I set up the db path and what sql command I use, I continue to get the "Object reference not set to an instance of an object" error. Can create a db file, that works, all other sql commands give me this error and can not show any data using the get data command.

 

Tried to create and populate the db file using sqliteexpert as Dan suggested (great program BTW, thanks Dan!) and then get data using ubot, still get the object error and 0 results. Tried this using the special folder (my documents) as well as direct path e:\dbname.sqlite and get the same error.

 

Even tried the exact same data and examples Aymen showed in the video, getting same error.

 

Using latest version (I assume, just downloaded a few weeks ago)

 

Any suggestions?

 

Thanks much!

Could you post or PM your test DB and the code you use in UBot Studio?

 

Dan

[tripros 16]

Could you post or PM your test DB and the code you use in UBot Studio?

 

Dan

 

 

Sure thing, thanks Dan, here is the code, file attached:

 

plugin command("SQLlite.dll", "SQLite Create Database", "{$special folder("My Documents")}/pc/pcdb.sqlite")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Query", "{$special folder("My Documents")}/pc/pcdb.sqlite", "create table mytable (name varchar(50), category varchar(25), filename varchar(75))")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Query", "{$special folder("My Documents")}/pc/pcdb.sqlite", "insert into mytable (name, category) values (\'Test Name\',\'category1\')")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Query", "{$special folder("My Documents")}/pc/pcdb.sqlite", "insert into mytable (name, category, filename) values (\'Test Name 2\',\'category2\',\'myfile.zip\')")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Get Data", "{$special folder("My Documents")}/pc/pcdb.sqlite", "select * from mytable order by name desc", &results)

alert($plugin function("SQLlite.dll", "sqlite error"))

 

Verified that the create table and insert commands are working, opened the file in sqliteexpert, but when I try to select * with the get data command, I get the object error and zero results.

 

Thanks!

 

-----

EDIT

forum here is telling me can't attach a sqlite or zip file, so not sure how to upload it? No way in PM either.

[Bot-Factory 602]

Table cells can not be NULL for the Get Data command. You can create the table with empty cells, or you can always add information to all the cells. 

 

 

plugin command("SQLlite.dll", "SQLite Create Database", "{$special folder("Application")}\\test.sqlite")
alert($plugin function("SQLlite.dll", "sqlite error"))
plugin command("SQLlite.dll", "SQLite Query", "{$special folder("Application")}\\test.sqlite", "create table mytable (name varchar(50), category varchar(25), filename varchar(75))")
alert($plugin function("SQLlite.dll", "sqlite error"))
plugin command("SQLlite.dll", "SQLite Query", "{$special folder("Application")}\\test.sqlite", "insert into mytable (name, category, filename) values (\'Test Name\',\'category1\',\'NO FILE\')")
alert($plugin function("SQLlite.dll", "sqlite error"))
plugin command("SQLlite.dll", "SQLite Query", "{$special folder("Application")}\\test.sqlite", "insert into mytable (name, category, filename) values (\'Test Name 2\',\'category2\',\'myfile.zip\')")
alert($plugin function("SQLlite.dll", "sqlite error"))
plugin command("SQLlite.dll", "SQLite Get Data", "{$special folder("Application")}\\test.sqlite", "select * from mytable", &results)
alert($plugin function("SQLlite.dll", "sqlite error"))

 

 

Cheers

Dan

[tripros 16]

Table cells can not be NULL for the Get Data command. You can create the table with empty cells, or you can always add information to all the cells. 

 

 

plugin command("SQLlite.dll", "SQLite Create Database", "{$special folder("Application")}\\test.sqlite")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Query", "{$special folder("Application")}\\test.sqlite", "create table mytable (name varchar(50), category varchar(25), filename varchar(75))")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Query", "{$special folder("Application")}\\test.sqlite", "insert into mytable (name, category, filename) values (\'Test Name\',\'category1\',\'NO FILE\')")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Query", "{$special folder("Application")}\\test.sqlite", "insert into mytable (name, category, filename) values (\'Test Name 2\',\'category2\',\'myfile.zip\')")

alert($plugin function("SQLlite.dll", "sqlite error"))

plugin command("SQLlite.dll", "SQLite Get Data", "{$special folder("Application")}\\test.sqlite", "select * from mytable", &results)

alert($plugin function("SQLlite.dll", "sqlite error"))

 

 

Cheers

Dan

 

Got it, thanks a bunch Dan, works like a charm now

Appreciate your help!

[mobileconsultant 6]

Hey Aymen,

 

This looks like a really nice plugin! I just ordered.

 

Thanks,

Rich

[The Fixer 1]

Hi. Does this plugin work with Godaddy shared hosting?

[mobileconsultant 6]

Hey The Fixer,

 

I believe most Ubotters run SQLite to write to the local disk on their PC and maybe on a hosted VPS or dedicated server. Shared web hosted accounts (usually Linux or Windows based) will most certainly not allow you to run SQLite directly on the server. Here is some SQLite info that may answer your question better, since I'm not sure what your end goal is with SQLite and your data. You may just be looking for a traditional RDBMS like MySQL to store and manipulate your data.

 

About SQLite
http://www.sqlite.org/about.html

Appropriate Uses For SQLite
http://www.sqlite.org/whentouse.html
"SQLite is not directly comparable to other SQL database engines such as Oracle, PostgreSQL, MySQL, or SQL Server since SQLite is trying to solve a very different problem."

 

I hope that helps someone...

-Rich

[arunner26 51]

mobileconsultant,

Thanks for the info on SQLite. That helps!

[Aymen 385]

Hi. Does this plugin work with Godaddy shared hosting?

 

If it is a windows vps then yes it should work!

[Bot-Factory 602]

Been real busy Dan , i'll try to check it out soon!

 

Hi Aymen,

 

don't want to be impatiently, but may I ask if you had time to look into this already?

If not, any idea when you might have a free timeslot?

 

Cheers

Dan

[Aymen 385]

Hi Aymen,

 

don't want to be impatiently, but may I ask if you had time to look into this already?

If not, any idea when you might have a free timeslot?

 

Cheers

Dan

 

Not really Dan, sorry if i promised you something , been buried in work for over 2 weeks , i didn't forget about any update though!

[Bot-Factory 602]

Hi Aymen,

 

just wanted to ask if you had some time during the last 3 months to look into the things we discussed:

 

1. Performance issue

2. Table to database command with update option. 

Thanks in advance for your help.

 

Kindest regards

Dan

[Aymen 385]

Hi Aymen,

 

just wanted to ask if you had some time during the last 3 months to look into the things we discussed:

 

1. Performance issue

2. Table to database command with update option. 

 

Thanks in advance for your help.

 

Kindest regards

Dan

 

about the performance issue , can't really have it reproduced properly , what i concluded is that it can be tied to ubot itself , i won't promise you anything about this , will see how things goes !

about new features and options i'll have a look at them when releasing the next version which will be in a week or so from now!