whoami 26 Posted April 20, 2014 Report Share Posted April 20, 2014 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? Quote Link to post Share on other sites
Bot-Factory 602 Posted April 20, 2014 Report Share Posted April 20, 2014 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 Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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 Quote Link to post Share on other sites
Bot-Factory 602 Posted April 21, 2014 Report Share Posted April 21, 2014 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 Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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 ? Quote Link to post Share on other sites
Kev 69 Posted April 21, 2014 Report Share Posted April 21, 2014 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 Intothen 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. Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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? Quote Link to post Share on other sites
Kev 69 Posted April 21, 2014 Report Share Posted April 21, 2014 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. Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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 Quote Link to post Share on other sites
Kev 69 Posted April 21, 2014 Report Share Posted April 21, 2014 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. Quote Link to post Share on other sites
Bot-Factory 602 Posted April 21, 2014 Report Share Posted April 21, 2014 Yes i want to insert the data to sqlite directly then how to build the correct query? Please helpWhat's the reason you don't want to store it in a list first and then loop through it to build your query? Dan Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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 Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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? Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted April 21, 2014 Report Share Posted April 21, 2014 @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. Quote Link to post Share on other sites
Bot-Factory 602 Posted April 21, 2014 Report Share Posted April 21, 2014 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 threads3. After 5000 loops, wait until all threads have ended4. Write to DB and clear list5. 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 Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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 Quote Link to post Share on other sites
abbas786 78 Posted April 21, 2014 Report Share Posted April 21, 2014 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 errorCheck 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 1 Quote Link to post Share on other sites
jamesfar 15 Posted April 21, 2014 Report Share Posted April 21, 2014 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 Quote Link to post Share on other sites
whoami 26 Posted April 22, 2014 Report Share Posted April 22, 2014 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? Quote Link to post Share on other sites
jamesfar 15 Posted April 25, 2014 Report Share Posted April 25, 2014 i got this error : http://s8.postimg.org/dp5f9zshx/2014_04_24_2340.png What is this and how to solved it Aymen? Quote Link to post Share on other sites
Aymen 385 Posted April 25, 2014 Author Report Share Posted April 25, 2014 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 ? Quote Link to post Share on other sites
Aymen 385 Posted June 2, 2014 Author Report Share Posted June 2, 2014 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! Quote Link to post Share on other sites
Bot-Factory 602 Posted June 2, 2014 Report Share Posted June 2, 2014 Wohoo. Now you're in a flow :-) Awesome! Thank you Aymen for taking the time to do all the updates! Dan Quote Link to post Share on other sites
fastlinks 16 Posted June 8, 2014 Report Share Posted June 8, 2014 (edited) Hi Aymen, my paypal Transaction ID: 21M00388FS052131HOrder Number : 205296889194 added you on skype, thanks Andrew Edited June 8, 2014 by fastlinks Quote Link to post Share on other sites
Aymen 385 Posted June 8, 2014 Author Report Share Posted June 8, 2014 Hi Aymen, my paypal Transaction ID: 21M00388FS052131HOrder Number : 205296889194 added you on skype, thanks Andrew Your Package has been PMed to you! Regards, Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.