[SELL] SQLite Database Plugin

[whoami 26]

Im using this code to add the &Table I have to the database.sql :

plugin command("SQLlite.dll", "SQLite Table To DB", "{$special folder("Application")}\\Database.sql", "Profiles", "Name, LastName, Title, City, Niche, URL, LastJobs, Studied, Connections, Null, Email, Null2", &profile leads)

What Am I doing wrong?

[Bot-Factory 602]

Im using this code to add the &Table I have to the database.sql :

plugin command("SQLlite.dll", "SQLite Table To DB", "{$special folder("Application")}\\Database.sql", "Profiles", "Name, LastName, Title, City, Niche, URL, LastJobs, Studied, Connections, Null, Email, Null2", &profile leads)

What Am I doing wrong?

Hello.

 

You could do a:

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

 

To see what's wrong.

 

The important thing with SQLIte Table to DO is, that the column names are correct. 

You have:

Null2

 

In your command. Is that a column in your DB?

 

Dan

[jamesfar 15]

How do you add list from scrapped element to sqlite?

 

something like this :

 

add list to list(%list, $list from text($scrape attribute(<id="gbqfsa">, "innertext"), "
"), "Delete", "Global")

 

but i don't want to use the list or loop the list and add the item list one by one to sqlite. I want to scrape and add the scrapped texts to sqlite

 

Is it possible?

 

Please help

[Bot-Factory 602]

Yes. that's possible. 

 

You have to create a SQL statement which contains all the data you want to enter:

INSERT INTO 'tablename'

SELECT 'data1' AS 'column1', 'data2' AS 'column2'

UNION SELECT 'data3', 'data4'

UNION SELECT 'data5', 'data6'

UNION SELECT 'data7', 'data8'

 

 

Here is a basic define to do that:

 

define CreateSQLAdd {

    set list position(%urls, 0)

    set(#sqlcommand, "INSERT INTO \'data1\'", "Global")

    set(#sqlcommand, "{#sqlcommand}

SELECT \'{$next list item(%urls)}\' AS \'urls\'", "Global")

    loop($subtract($list total(%urls), 1)) {

        set(#sqlcommand, "{#sqlcommand}

UNION SELECT \'{$next list item(%urls)}\'", "Global")

    }

}

 

 

But please be aware that SQLite has a limit of 250 entries per insert. 

If your list is larger than that, you have to create multiple 250 items sql queries and then add them one by one to the database.

 

But that's still a lot faster than running a query for every single item.

 

Dan

[jamesfar 15]

 

Yes. that's possible. 

 

You have to create a SQL statement which contains all the data you want to enter:

INSERT INTO 'tablename'

SELECT 'data1' AS 'column1', 'data2' AS 'column2'

UNION SELECT 'data3', 'data4'

UNION SELECT 'data5', 'data6'

UNION SELECT 'data7', 'data8'

 

 

Here is a basic define to do that:

 

define CreateSQLAdd {

    set list position(%urls, 0)

    set(#sqlcommand, "INSERT INTO \'data1\'", "Global")

    set(#sqlcommand, "{#sqlcommand}

SELECT \'{$next list item(%urls)}\' AS \'urls\'", "Global")

    loop($subtract($list total(%urls), 1)) {

        set(#sqlcommand, "{#sqlcommand}

UNION SELECT \'{$next list item(%urls)}\'", "Global")

    }

}

 

 

But please be aware that SQLite has a limit of 250 entries per insert. 

If your list is larger than that, you have to create multiple 250 items sql queries and then add them one by one to the database.

 

But that's still a lot faster than running a query for every single item.

 

Dan

 

 

Does this means i have to store the scrapped data to %urls list first Dan ?

[Kev 69]

As Dan says there's a limit to how many entries you can have in one go. I found this out the hard way having spent a lot of time trying to figure out why I couldn't INSERT over 400 rows in one query

But I built up my query as  I went along...

 

Set a variable to Insert Into

then kept adding the query to the variable as I went.

 

"Does this means i have to store the scrapped data to %urls list first Dan ?"

 

Not really sure what you mean by that but as Dan mentions the limit is 250 so break your list into separate lists of no more than 250 in each one.

[jamesfar 15]

my scrapped data is small actually (around 50) but i don't want to scrape to list

 

i want to scrape to sqlite directly. So the final it would be 50 rows in sqlite

navigate("http://www.cbengine.com/popular-clickbank-products.cfm?report=paf-2", "Wait")
wait for element(<class="ner">, "", "Appear")
add list to list(%URL, $list from text($scrape attribute(<(tagname="a" AND title="visit website")>, "href"), "
"), "Delete", "Global")

i want result from this : $scrape attribute(<(tagname="a" AND title="visit website")>, "href")

 

goes to sqlite

 

how to do that?

[Kev 69]

Do you mean you want to INSERT into your database directly?

 

If so then you need to connect to database and run your query, making sure you built it correctly.

 

I use a loop to build my query. Also ensure you have "," commas in the right places and the last comma will need to be removed from your query depending on what way you build the query up.

[jamesfar 15]

Do you mean you want to INSERT into your database directly?

 

If so then you need to connect to database and run your query, making sure you built it correctly.

 

I use a loop to build my query. Also ensure you have "," commas in the right places and the last comma will need to be removed from your query depending on what way you build the query up.

 

Yes i want to insert the data to sqlite directly

 

then how to build the correct query?

 

Please help

[Kev 69]

INSERT INTO PesonalInfo (CustomerName, Age, Address, City, PostalCode, Country)
VALUES ('Dave','30','100 Baker Street','London','SW1 9D2','United Kingdom'); ('Tom','32','101 Baker Street','London','SW1 5D2','United Kingdom');

 

 

Simple example of a query. Personalinfo is the name of the TABLE you are INSERTING the data into.

 

Notice how the other stuff then matches up with it?

 

The VALUES need to "fall in line" with the main columns (CustomerName, Age, Address, City, PostalCode, Country) YET VALUES are seperated out, also.

 

Play around with it, you'll get the grip of it.

[Bot-Factory 602]

Yes i want to insert the data to sqlite directly

 

then how to build the correct query?

 

Please help

What's the reason you don't want to store it in a list first and then loop through it to build your query?

 

Dan

[jamesfar 15]

What's the reason you don't want to store it in a list first and then loop through it to build your query?

 

Dan

 

I'm avoiding clear list on multithreading since it will clear all list even if the list scope is local

 

Local dictionary also clearing all list on all thread

 

That is why i wonder if i can use sqlite as a local list and clear only local list not global list

[jamesfar 15]

INSERT INTO PesonalInfo (CustomerName, Age, Address, City, PostalCode, Country)

VALUES ('Dave','30','100 Baker Street','London','SW1 9D2','United Kingdom'); ('Tom','32','101 Baker Street','London','SW1 5D2','United Kingdom');

 

 

Simple example of a query. Personalinfo is the name of the TABLE you are INSERTING the data into.

 

Notice how the other stuff then matches up with it?

 

The VALUES need to "fall in line" with the main columns (CustomerName, Age, Address, City, PostalCode, Country) YET VALUES are seperated out, also.

 

Play around with it, you'll get the grip of it.

 

INSERT INTO PesonalInfo (CustomerName, Age, Address, City, PostalCode, Country)

VALUES ('Dave','30','100 Baker Street','London','SW1 9D2','United Kingdom'); ('Tom','32','101 Baker Street','London','SW1 5D2','United Kingdom');

 

Does this data get inserted too on this query : ('Tom','32','101 Baker Street','London','SW1 5D2','United Kingdom'); ?

 

I tought it has to be like this :

 

INSERT INTO PesonalInfo (CustomerName, Age, Address, City, PostalCode, Country)

VALUES ('Dave','30','100 Baker Street','London','SW1 9D2','United Kingdom');

 

INSERT INTO PesonalInfo (CustomerName, Age, Address, City, PostalCode, Country)

VALUES ('Tom','32','101 Baker Street','London','SW1 5D2','United Kingdom');

 

Which one is the correct query?

[Code Docta (Nick C.) 638]

@jamestar

 

Multi-threading gets tricky. I found that if you apend your data to a file (append to file) rather a list then have a loop while checking that file every so often  to update/insert into Sqlite is best. Kinda like a que.

[Bot-Factory 602]

I'm avoiding clear list on multithreading since it will clear all list even if the list scope is local

 

Local dictionary also clearing all list on all thread

 

That is why i wonder if i can use sqlite as a local list and clear only local list not global list

 

That will not work. I played around with that a couple of days ago. 

The database will be locked a lot of times.

 

Do you scrape different things in your threads? Or could you add everything into a global list?

 

I'm doing it that way:

1. Multithreading. 

2. Add to global list from threads

3. After 5000 loops, wait until all threads have ended

4. Write to DB and clear list

5. Continue

 

That way your lists will be very small and you won't run into memory issues.

 

I scraped 3 Million URLs that way without any problems.

Dan

[jamesfar 15]

Here is what i got so far with 50 rows :

navigate("http://www.cbengine.com/popular-clickbank-products.cfm?report=paf-2", "Wait")
wait for element(<class="ner">, "", "Appear")
plugin command("SQLlite.dll", "SQLite Query", "D:\\TEMP-DEL\\test.db", "insert into regions (name) values (\'{$replace($replace($scrape attribute(<(tagname="a" AND title="visit website")>, "href"), "
", "
insert into regions (name) values (\'"), "
", "\');
")}\');")
set(#aa, $scrape attribute(<(tagname="a" AND title="visit website")>, "href"), "Global")
set(#a, $plugin function("SQLlite.dll", "sqlite error"), "Global")

 

Data inserted on sqlite on one go. But don't understand why got this on debugger : #a: near "insert": syntax error

[abbas786 78]

Here is what i got so far with 50 rows :

navigate("http://www.cbengine.com/popular-clickbank-products.cfm?report=paf-2", "Wait")

wait for element(<class="ner">, "", "Appear")

plugin command("SQLlite.dll", "SQLite Query", "D:\\TEMP-DEL\\test.db", "insert into regions (name) values (\'{$replace($replace($scrape attribute(<(tagname="a" AND title="visit website")>, "href"), "

", "

insert into regions (name) values (\'"), "

", "\');

")}\');")

set(#aa, $scrape attribute(<(tagname="a" AND title="visit website")>, "href"), "Global")

set(#a, $plugin function("SQLlite.dll", "sqlite error"), "Global")

 

Data inserted on sqlite on one go. But don't understand why got this on debugger : #a: near "insert": syntax error

Check this one

 

set(#dbname, "{$special folder("Application")}\\test.db", "Global")

set(#a, $nothing, "Global")

navigate("http://www.cbengine.com/popular-clickbank-products.cfm?report=paf-2", "Wait")

wait for element(<class="ner">, "", "Appear")

set(#tempdata, "INSERT INTO Regions (name) VALUES (\'{$replace($replace($scrape attribute(<(tagname="a" AND title="visit website")>, "href"), "

", "

INSERT INTO Regions (name) VALUES (\'"), "

", "\');{$new line}

")}\');", "Global")

plugin command("SQLlite.dll", "SQLite Query", #dbname, #tempdata)

set(#a, $plugin function("SQLlite.dll", "sqlite error"), "Global")

 

 

It might work

[jamesfar 15]

Check this one

 

set(#dbname, "{$special folder("Application")}\\test.db", "Global")

set(#a, $nothing, "Global")

navigate("http://www.cbengine.com/popular-clickbank-products.cfm?report=paf-2", "Wait")

wait for element(<class="ner">, "", "Appear")

set(#tempdata, "INSERT INTO Regions (name) VALUES (\'{$replace($replace($scrape attribute(<(tagname="a" AND title="visit website")>, "href"), "

", "

INSERT INTO Regions (name) VALUES (\'"), "

", "\');{$new line}

")}\');", "Global")

plugin command("SQLlite.dll", "SQLite Query", #dbname, #tempdata)

set(#a, $plugin function("SQLlite.dll", "sqlite error"), "Global")

 

 

It might work

 

Thanks Abbas. Works perfectly

[whoami 26]

Hello.

 

You could do a:

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

 

To see what's wrong.

 

The important thing with SQLIte Table to DO is, that the column names are correct. 

You have:

Null2

 

In your command. Is that a column in your DB?

 

Dan

 

I see Dan, the problem with the columns is that I have 2 empty columns right now that were intended for some variables. I will erase them, but the code is ok to save the table to database right?

[jamesfar 15]

i got this error :

 

http://s8.postimg.org/dp5f9zshx/2014_04_24_2340.png

 

What is this and how to solved it Aymen?

[Aymen 385]

i got this error :

 

http://s8.postimg.org/dp5f9zshx/2014_04_24_2340.png

 

What is this and how to solved it Aymen?

 

can you reproduce it ?

what did you do exactly to generate that error ?

[Aymen 385]

v 2.1 Update Released  :

-Fixed sqlite error function sometimes returning errors on successful queries

-Added lock feature to database queries to prevent errors when accessing the database from different threads!

[Bot-Factory 602]

Wohoo. Now you're in a flow :-)

 

Awesome! Thank you Aymen for taking the time to do all the updates!

 

Dan

[fastlinks 16]

Hi Aymen, my paypal Transaction ID: 21M00388FS052131H

Order Number : 205296889194

 

added you on skype, thanks

 

Andrew

Edited June 8, 2014 by fastlinks

[Aymen 385]

Hi Aymen, my paypal Transaction ID: 21M00388FS052131H

Order Number : 205296889194

 

added you on skype, thanks

 

Andrew

 

Your Package has been PMed to you!

 

Regards,