Bot-Factory 602 Posted January 27, 2015 Report Share Posted January 27, 2015 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! It's a query that returns between 10k and 50k URLs. So it's a bit of data. When I run it natively with your plugin, the execution of this command takes almost 5 minutes. When I use the commandline SQL tool via the shell plugin: plugin command("Advanced Shell.dll", "shell batch hidden", "\"{$special folder("Application")}\\sqlite3.exe\" \"{#databasefile}\" \"select urls from profiles WHERE {#query}\" > \"{$special folder("Application")}\\sqltmp.txt\"", "Yes") plugin command("Bigtable.dll", "large List from text", "tmp", $read file("{$special folder("Application")}\\sqltmp.txt"), $new line, "replace") plugin command("Advanced Shell.dll", "delete file permanently", "{$special folder("Application")}\\sqltmp.txt") Then this executes in under 30seconds. Thanks for looking into it.Dan 1 Quote Link to post Share on other sites
Aymen 385 Posted January 29, 2015 Author Report Share Posted January 29, 2015 Hey , what i can promise you is that i will certainly take a deep look at it , and will try to at least improve it! Regards 1 Quote Link to post Share on other sites
dankass 52 Posted February 7, 2015 Report Share Posted February 7, 2015 I cannot get the query with results to return anything in UBS 5.5.11 Quote Link to post Share on other sites
dankass 52 Posted March 18, 2015 Report Share Posted March 18, 2015 @Aymen can you please update this to work with UBS 5.5.13+ I get 0 results using the sqlite get data command... I believe the other commands work ok, but have not fully tested it or anything to see... Thanks Quote Link to post Share on other sites
Bot-Factory 602 Posted March 18, 2015 Report Share Posted March 18, 2015 @Aymen can you please update this to work with UBS 5.5.13+ I get 0 results using the sqlite query with results... I believe the other commands work ok, but have not fully tested it or anything to see... Thanks Working fine for me: I'm testing my databases with:http://www.sqliteexpert.com/ Maybe that could help to find the error? Dan Quote Link to post Share on other sites
dankass 52 Posted March 18, 2015 Report Share Posted March 18, 2015 Working fine for me: I'm testing my databases with:http://www.sqliteexpert.com/ Maybe that could help to find the error? Dan @Dan , Thanks, Yes, that function/parameter works fine, it is the COMMAND "SQLite Get Data" that returns the results in a table that is not working. Can you see if that command is working for you? And thanks for the tip on the sqliteexpert software, it looks really nice. I just use sqlite manager plugin for firefox. I am pretty comfortable with sql, been using it for many years, but I do like to use visual tools too, and this looks alot nicer than sqlite manager, I think I'm gonna pick this one up:) Quote Link to post Share on other sites
Bot-Factory 602 Posted March 18, 2015 Report Share Posted March 18, 2015 Yes, that one is working fine for me as well: All my table fields are declared as TEXT. Not sure if it makes a difference if you have other Types in your DB.The Sqlite plugin is using the:Csharp Community SQLite library:https://www.nuget.org/packages/Community.CsharpSqlite.SQLiteClient/Which is based on SQLite 3.7.7.3So depending on your sqlite database, that could lead to trouble.But if you create your DB with the plugin, save data to it and load data with the plugin, that should all work.The only problem I could see is if your database was created externally and is maybe using stuff, that is not "supported" with the plugin. Dan Quote Link to post Share on other sites
dankass 52 Posted March 18, 2015 Report Share Posted March 18, 2015 Yes, that one is working fine for me as well: All my table fields are declared as TEXT. Not sure if it makes a difference if you have other Types in your DB. The Sqlite plugin is using the:Csharp Community SQLite library:https://www.nuget.org/packages/Community.CsharpSqlite.SQLiteClient/Which is based on SQLite 3.7.7.3 So depending on your sqlite database, that could lead to trouble. But if you create your DB with the plugin, save data to it and load data with the plugin, that should all work. The only problem I could see is if your database was created externally and is maybe using stuff, that is not "supported" with the plugin. Dan Interesting... yes, my db was created using sqlite manager, and the only datatypes I use are varchar, integer and text... I will experiment with this to see if maybe that's the issue. Thanks for your help. Whats wierd about is tho, it works just fine on the same database using ubot studio 4.... I just tinkered a little bit with it, and maybe it has to do with the mixed datatypes... if I do not use the wildcard and choose the specific columns I want it works. Again, whats strange is, it woks fine in ub 4 with the wildcard and on the exact same file. The error being thrown is "object not set to in instance of an object" when I use the wildcard, edit:I have narrowed this down to being an issue with the contents of one )or more) of my TEXT fields... there is probably some sort of strange character in one of the fields is whats at the root of this. Do you know of any quick and easy way to serialize a variable? I have the communications plugin and it can do that with lists, but not sure of a way to do this inside ubot with a variable (or table or anything other than a list)... maybe I'll just base64 encode these larger fields before I insert them from now on... Quote Link to post Share on other sites
Bot-Factory 602 Posted March 18, 2015 Report Share Posted March 18, 2015 I used the free encryption plugin for something like that in the past. If you need more flexibility you could also use the command line plugin and get the data via SQLite.exe via the command line. I'm doing that in one oft bots. Because the query on a large table (5million entries) id 20x faster via the SQLite.exe compared to the plugin. Dan Quote Link to post Share on other sites
dankass 52 Posted March 19, 2015 Report Share Posted March 19, 2015 @Dan I seem to have isolated this down even further now... it was not a funky character, its throwing this error if there are any of the results that contain a null value. I went through and bas64 encoded all the fields, and still had the problem, and there were 3 fields that were null... I added some dummy text and it worked fine... I worked my way column by column through my database and had the same issue with ever column if there were any null values in it. At least thats what is happening on my end, can you confirm this? Do any of your columns that the query returns contain null values? Quote Link to post Share on other sites
tripros 16 Posted March 19, 2015 Report Share Posted March 19, 2015 I've confirmed this with Aymen and have personal experience on the same, the plugin only works properly when there are no null values, if you have a null value in a field, you need to put something in the field rather than a null value. HTH 1 Quote Link to post Share on other sites
dankass 52 Posted March 19, 2015 Report Share Posted March 19, 2015 I've confirmed this with Aymen and have personal experience on the same, the plugin only works properly when there are no null values, if you have a null value in a field, you need to put something in the field rather than a null value. HTH Thanks... Quote Link to post Share on other sites
mobileconsultant 6 Posted July 10, 2015 Report Share Posted July 10, 2015 (edited) Hi Everyone,I wanted to confirm the same issues with SQLite and NULL values and DOUBLE QUOTES in your literal strings. I wrestled with this for several hours till I had a breakthrough via trial&error and research on this forum and the web, so I wanted to make this post to save someone else a headache.PROBLEM #1: SQLite databases and NULL values (you can't do it)My Solution: In the Ubot code below, I had to do a $comparison when a variable contained nothing/NULL value, I chose to set the value to the string "None" in my case.if($comparison(#xProdViews,"= Equals",$nothing)) { then { set(#xProdViews,"None","Global") }I also found out the hard way that when I tried to pass to SQLite literal strings that contained DOUBLE QUOTES via the Ubot plugin command "SQLite Table To DB". The results were the SQLite database would break or reject the transfer completely.PROBLEM #2: SQLite databases and DOUBLE QUOTES (you can do it, but you have to use two double quotes)My Solution: You have to replace DOUBLE QUOTES with a set of DOUBLE QUOTES like this ""What I did in the node view was put a set of DOUBLE QUOTES "" and click the "" icon button in the SET() node.In the Ubot code below, I'm pulling string data with $xpath parser expression and then $trim whitespaces off the string, then I $replace the html code """ with a set of DOUBLE QUOTES so that SQLite does not reject it and then Ubot is escaping the set of DOUBLE QUOTES with the node view "" icon clicked which are \ escaped characters in the Ubot code view below.You'll also noticed that I $replace the html code "'" with single quote which oddly the Ubot plugin command "SQLite Table To DB" and SQLite database themselves don't have any problems with single quotes!? But, Ubot code view does escape character the single quote below.set(#xProdTitle,$replace($replace($trim($plugin function("HTTP post.dll", "$xpath parser", #get, "//meta[@property=\"og:title\"]", "content", "HTML")),""","\"\""),"'","\'"),"Global")SUMMARY: Watch out and replace those Nothing/NULL values and DOUBLE QUOTES in yuor strings when working with SQLite database and the Ubot plugin command "SQLite Table To DB"Just wanted to give back to this awesome Ubot community and I truly hope this helps someone out and saves them coding time Thanks,-Rich Edited July 10, 2015 by mobileconsultant 2 Quote Link to post Share on other sites
Bawzee 0 Posted August 13, 2015 Report Share Posted August 13, 2015 Is there any way to get the column names (not just the data) from the sqlite database? Quote Link to post Share on other sites
HelloInsomnia 1103 Posted August 13, 2015 Report Share Posted August 13, 2015 Is there any way to get the column names (not just the data) from the sqlite database? You should be able to do this: http://www.sqlite.org/pragma.html#pragma_table_info But for some reason it's not listing all the info for me. Quote Link to post Share on other sites
Frank 177 Posted August 16, 2015 Report Share Posted August 16, 2015 Hey Aymen, I've been getting inconsistent results with some data queries. I'm going to try and hammer things out but thought I'd throw it out there. Quote Link to post Share on other sites
Frank 177 Posted September 19, 2015 Report Share Posted September 19, 2015 Ok, after a month of working with sqlite, I'm really starting to get it. I do have a request to the dev, is it possible to create two commands in the plugin to backup and restore a database? Frank Quote Link to post Share on other sites
virtualexpert 1 Posted October 3, 2015 Report Share Posted October 3, 2015 Hi aymen, I am facing Stopped working error of ubot. When i run grid window command. i have tested it many times. not sure why is it so. there is also no error inside the code. --------------------------set(#databasefile,"{$special folder("Application")}\\database.sqlite","Global")if($not($file exists(#databasefile))) { then { plugin command("SQLlite.dll", "SQLite Create Database", #databasefile) plugin command("SQLlite.dll", "SQLite Query", #databasefile, "CREATE TABLE `campaigns` ( `Campaign Name` TEXT);") } else { plugin command("SQLlite.dll", "SQLite Get Data", #databasefile, "select * from campaigns", &tubeViewer) if($contains($plugin function("SQLlite.dll", "sqlite error"),"no such table")) { then { plugin command("SQLlite.dll", "SQLite Query", #databasefile, "CREATE TABLE `campaigns` ( `Campaign Name` TEXT);") } else { clear table(&campaigns) plugin command("SQLlite.dll", "SQLite Get Data", #databasefile, "select `Campaign Name` from campaigns", &campaigns) clear list(%campaigns) with every cell in column(&campaigns,0,#campaignVar) { add item to list(%campaigns,#campaignVar,"Don\'t Delete","Global") } set(#campaigns,$replace(%campaigns,"",","),"Global") } } }}clear table(&campaigns)plugin command("SQLlite.dll", "SQLite Get Data", #databasefile, "select * from campaigns", &campaigns)alert($plugin function("SQLlite.dll", "sqlite error"))if($is blank(&campaigns)) { then { alert("No campaigns found in database!Create some campaigns first. Thanks") } else { plugin command("SQLlite.dll", "grid window", "select * from campaigns", #databasefile, 200, "Campaigns", 575, 500) }} Quote Link to post Share on other sites
Frank 177 Posted October 29, 2015 Report Share Posted October 29, 2015 Way back when, someone was talking about jquery tables to be created and used. Any further developments here? I'd love to add more intelligence to my table queries and change writes. Quote Link to post Share on other sites
Monovski 4 Posted November 1, 2015 Report Share Posted November 1, 2015 hi aymen please check PM I just purchased thanks Quote Link to post Share on other sites
Monovski 4 Posted November 2, 2015 Report Share Posted November 2, 2015 Aymen where are you? please check my order (Unique Transaction ID #5AK021069B227733J) Quote Link to post Share on other sites
tripros 16 Posted November 2, 2015 Report Share Posted November 2, 2015 Think Aymen mentioned that he is not checking regularly here due to his schedule recently and that emailing him was best way if it's urgent.HTH Aymen where are you? please check my order (Unique Transaction ID #5AK021069B227733J) Quote Link to post Share on other sites
Aymen 385 Posted November 3, 2015 Author Report Share Posted November 3, 2015 Aymen where are you? please check my order (Unique Transaction ID #5AK021069B227733J) Think Aymen mentioned that he is not checking regularly here due to his schedule recently and that emailing him was best way if it's urgent.HTH Yup what he said !Best way to send me an email if you need me fast (you the contact page at my website) , i usually response in the same day !Anyways , PMed you back! Sorry for the trouble Quote Link to post Share on other sites
sunny9495 42 Posted December 2, 2015 Report Share Posted December 2, 2015 Just Purchased,Waiting for Package Aymen Quote Link to post Share on other sites
dankass 52 Posted December 23, 2015 Report Share Posted December 23, 2015 What is current version of this? all I could find is my payment reciept from ages ago and no download link in my email. I have 2.1.0 is that thje current version? Was hoping maybe the issue with the blank fields causing no results would be fixed by now in ubot 5. 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.