VaultBoss 310 Posted November 28, 2012 Report Share Posted November 28, 2012 Since the new UBot Studio v.4.2x we've finally got support to connect to external databases, using MySQL. I am no master of this myself, but I'm sure many people with more knowhow will chime in and add their contributions. I wanted to put up a bit of code as an example usage for people new to all this. Hope it helps. Pre-requisites:_________________________________________ You will need to have a MySQL server installed, either on your machine, or remotely (as in your website hosting account for instance - all cPanel powered hosting accounts have a MySQL Management tab there, you will find it) I am using wamp for this purpose (installs also php and Apache locally on my PC) but feel free to use your webhosting MySQL if you don't want a local server. _________________________________________ The administrator username for the server is root and you'll have a password for it, too... However, it is NOT recommended to use root to perform database actions, usually, so we will also create a new user and grant them permissions. This is the first MySQL Node you will need to use in UBot, AFTER you have installed MySQL locally, or like I said, alternatively to connect to a remote server. plugin command("DatabaseCommands.dll", "connect to database", "server=localhost;uid=root; pwd=*******; database=mysql; port=3306; pooling=false") { plugin command("DatabaseCommands.dll", "query", "CREATE DATABASE ubotstudio_sample;") { } plugin command("DatabaseCommands.dll", "query", "GRANT ALL PRIVILEGES on ubotstudio_sample.* to ubotter@\'localhost\' identified by \'awesomepass\';") { } plugin command("DatabaseCommands.dll", "query", "QUIT") { } } I have hidden my own server password for obvious reasons behind the ******* but you will just have to replace that with your own password. Note:This is the password of the administrator (root) With this bit of code:we created a new database called: ubotstudio_sampleand a new user called: ubotterwho has the access password: awesomepassOf course, you will personalize those to your own liking... IMPORTANT:This code has to be run only ONCE, to create the new user and the database they are allowing to access.Including this in the main loop of your program will give you repeat errors when you will try to re-create the same DB or USER again and again... Hope this is clear. Once you've created the above, it is time to create a new table inside the above DB and populate it with some data. After that, we will retrieve the new table's content into a regualr UBot table you are familiar with: plugin command("DatabaseCommands.dll", "connect to database", "server=localhost;uid=ubotter; pwd=awesomepass; database=ubotstudio_sample; port=3306; pooling=false") { plugin command("DatabaseCommands.dll", "query", "CREATE TABLE usernames (id INT NOT NULL AUTO_INCREMENT, firstname VARCHAR(48), lastname VARCHAR(48), PRIMARY KEY(id));") { } plugin command("DatabaseCommands.dll", "query", "INSERT INTO usernames (firstname, lastname) VALUES (\'John\', \'Doe\');") { } plugin command("DatabaseCommands.dll", "query", "INSERT INTO usernames (firstname, lastname) VALUES (\'Jane\', \'Doe\');") { } plugin command("DatabaseCommands.dll", "query with results", "SELECT * FROM usernames;", &tbl_ubotstudio_sample_names) } There is a LOT more to learn about how to use MySQL, of course, but I believe even with these basic things you guys could start thinking of new ways to store all your data from now on, besides only .txt or .csv files like before. Enjoy! P.S. I've also attached the above as an .ubot file for download.Sample-MySQL-usage_bot.ubot 11 Quote Link to post Share on other sites
Kreatus (Ubot Ninja) 422 Posted November 28, 2012 Report Share Posted November 28, 2012 Nice. this will come in handy. +1 Quote Link to post Share on other sites
blumi40 222 Posted November 28, 2012 Report Share Posted November 28, 2012 Have a look at http://www.navicat.com/The best Tool i ever used for Querys and MySQL DB-HandlingAnd the Best...a fullworking free Version 3 Quote Link to post Share on other sites
Aymen 385 Posted November 28, 2012 Report Share Posted November 28, 2012 thanks so much for this ! i think with an onload window command and MYSQL , we can easily create our own licensing system ! Quote Link to post Share on other sites
VaultBoss 310 Posted November 28, 2012 Author Report Share Posted November 28, 2012 Looks like an excellent tool, blumi40... Thanks a bunch! +1 (I knew people will jump in with tips and advice on such a thread) Quote Link to post Share on other sites
Bliss 194 Posted November 29, 2012 Report Share Posted November 29, 2012 Nice simple example for everyone, excellent work Quote Link to post Share on other sites
Legend 181 Posted November 29, 2012 Report Share Posted November 29, 2012 The ability to first wrap your head around relational databases, and then to provide code based on this new ability, while daunting for those who have never played with SQL, really is a game changer and I recommend any serious botters to start digging into this if you want to take your bots to the next level. For those of you who just don't understand what all the fuss is about, it would be well worth your while to learn to understand... because this is some serious sh*t and you are in the perfect place right now, as it is brand new, to grow with it and make some SERIOUS coin as a result of that effort. As an added hint, if you haven't been paying attention to Buddy's tutorials, you might want to start... no reason at all behind this, I'm just saying... 3 Quote Link to post Share on other sites
HarryPotter 9 Posted November 30, 2012 Report Share Posted November 30, 2012 Have a look at http://www.navicat.com/The best Tool i ever used for Querys and MySQL DB-HandlingAnd the Best...a fullworking free Version where to find the full working free version? looked through the site and found 30-day free trials and paid versions THANKS Quote Link to post Share on other sites
blumi40 222 Posted November 30, 2012 Report Share Posted November 30, 2012 pls search google for navicat lite Quote Link to post Share on other sites
HarryPotter 9 Posted November 30, 2012 Report Share Posted November 30, 2012 weird............... i don't think navicat lite is released anymore because the latest version i found is from april 2011 here is the link to download for anyone else interested: http://download.cnet...4-11884522.html license for the above is "free" -> SO GET IT BEFORE THEY TAKE IT DOWN and if you click on "A newer version of Navicat Lite is available." it will take you to a "free to try" version 2 Quote Link to post Share on other sites
VaultBoss 310 Posted November 30, 2012 Author Report Share Posted November 30, 2012 Nice find, HarryPotter! Quote Link to post Share on other sites
Ubot_guy 13 Posted December 3, 2012 Report Share Posted December 3, 2012 My favorite DB admin tool is Toad(5+ years), it happens to be free. Here is a link directly to the video tutorials. Take a look and see if it fits your style. From the screen shots it looks like navicat, but is current and free. Update: I was trying to work with the new MySQL plugin and had a hard time getting varialbes from ubot to 'insert' into my database. I finally figured out to put the entire query into a variable "MyQuery"(or anything else you want to name it.), then reference the variable in the "query" node, inside the "connect to database" node. Here is a screen shot. 2 Quote Link to post Share on other sites
VaultBoss 310 Posted December 5, 2012 Author Report Share Posted December 5, 2012 Or you could just EDIT the Query Node and then press the bottom-left button: Wizard..and input the code there. Quote Link to post Share on other sites
VaultBoss 310 Posted December 5, 2012 Author Report Share Posted December 5, 2012 My favorite DB admin tool is Toad(5+ years), it happens to be free. I tried to install Toad on my PC, but, although my local MySQL server runs fine and I am able to access it both through wamp or Navicat, as well as from UBot, I still cannot open a connection from Toad.It keeps yelling at me: "Unable to connect to any of the specified MySQL hosts." Kinda makes me crazy... so I'm still looking for a good Visual MySQL Query Designer and DB Management tool(free, if possible, would be nice...) Navicat - especially the free version - is a good simple and easy to learn/use tool for building the database/tables, but as far as querying the tables, creating JOINs and such, seems to be pretty simplistic and featureless (especially on the visual part) Still hoping someone would jump in with a new toy to test here. Looking forward to it... Quote Link to post Share on other sites
Ubot_guy 13 Posted December 15, 2012 Report Share Posted December 15, 2012 Hey VB, Pardon me for the delayed response, just lovin' the new DB feature. Really lovin' it!! Did you allow yourself to connect to the remote host? or are you blocking connections? Did you download Toad for MySQL? Can you connect using navcat? Did you try connecting using the ip address? I use "username@xxx.xxx.xxx.xxx" I use port 3306 with a tcp connection. HTH Waiting to here from you on skype, if you can catch me!!! 1 Quote Link to post Share on other sites
VaultBoss 310 Posted December 16, 2012 Author Report Share Posted December 16, 2012 Did you allow yourself to connect to the remote host? or are you blocking connections? Well, I do, actually. With all the other programs, I CAN connect. I will look closer to any advanced options/setup in Toad again though, to see if I missed anything there... Did you download Toad for MySQL? Yes, of course! Can you connect using navcat? YUP, along with MySQL Workbench & MySQL Lite too... Did you try connecting using the ip address? I use "username@xxx.xxx.xxx.xxx" No, haven't tried that yet - I will definitely do so now and see what happens. My setup is local, running it as a local service (comes bundled together: Apache, PHP & MySQL with WAMPSERVER) so I only used localhost so far. I use port 3306 with a tcp connection. Same port here. Waiting to here from you on skype, if you can catch me!!! Already added you.. Thanks for the suggestions, even if not working, still a great effort to put them together. +1 Quote Link to post Share on other sites
VaultBoss 310 Posted December 16, 2012 Author Report Share Posted December 16, 2012 With your help, I was finally being able to connect, but only using 127.0.0.1 instead of localhost. Thank you! +10 1 Quote Link to post Share on other sites
Ubot_guy 13 Posted December 23, 2012 Report Share Posted December 23, 2012 Hey VaultBoss,My Pleasure helping you out, great speaking with you!!! Anybody else needs a hand, hit me up! Be to share a screen, I usually have skype loaded. 1 Quote Link to post Share on other sites
yonatankra 1 Posted March 9, 2013 Report Share Posted March 9, 2013 Hi,This isn't working for me.Here's my code: plugin command("DatabaseCommands.dll", "connect to database", "server=serverIP;uid=userid; pwd=MyPass; database=myDB; port=3306; pooling=false") { plugin command("DatabaseCommands.dll", "query with results", "SELECT * FROM users", &users)} When I'm looking at "users" it is empty (0 rows, 0 cols). When I run the same query in phpMyAdmin it returns the users table...Using the "Wizard" I test the connection and it says connection established...What am I doing wrong?Thanks Quote Link to post Share on other sites
VaultBoss 310 Posted March 10, 2013 Author Report Share Posted March 10, 2013 I'm not sure how these would work in UBS with Standard License. You should eventually open a support ticket, maybe they can help you. Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted March 20, 2013 Report Share Posted March 20, 2013 Hey guys, great thread VB OK, lemme see if I have this correct. So, I'm dealing with very large list and as you know Ubot has issues with this. I have a list of US cities and I want to get certain certain cities in certain counties from a particular state. What I want to do is have a database in the application file for the bot, also I want it for creating campaigns for the bot. Instead of loading the list I want to query the database for these particular cities without to much hassle as with a list or table. I'm assuming this is much faster and more stable. My question is...Do I need to create a database from Toad or MySQL then just put it in the App folder then connect to that database localy (localhost 127.0.0.1) with un and pw? I'm learning the MySQL commands from this site http://www.databasejournal.com/features/mssql/ So I have not just tried this yet but, is it that simple? or do I need something special to do this? eventually this bot will be for sale and I rather not have the end user install a database on their server and also call that database for saving settings and user data for campaigns. Is this what developers do as far as their database goes or is it a different setup? Also, instead of scraping all my huge sites' site map then navigating to each url, I would just like to connect to WP DB and get url, title,meta description and Keywords or what ever else I may need. Am I on the correct path here or what? Thanks in advance! TC Quote Link to post Share on other sites
VaultBoss 310 Posted March 20, 2013 Author Report Share Posted March 20, 2013 If you plan to sell the bot, I wouldn't recommend a local MySQL DB ... it would be difficult to explain to the customers that they need to install extra sw, configure it properly, etc... Rather use a web located DB on their server (if you know how, even try to hook into their WP already existing DB - with a plugin, for instance - as nowadays most people would have a WP blog) Hope this helps you... Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted March 22, 2013 Report Share Posted March 22, 2013 Ok, I got the bot you posted to work, So, how do I get my bots to update the data base with out a server? Like when a user wants to create profiles or saved settings? How does my bot store data and update data? Does it connect to data base the same way it would as if on a server? Would I store the .sql file in a Data file inside the app folder? I understand how I would do this (I think) if it was on a server, ie my wp db's on my dedicated, but I'm just not understanding if it was just sitting in a file.My users won't have Mysql server installed so I just not getting how you would make bot communicate with the SQL file to update and create records. Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted March 22, 2013 Report Share Posted March 22, 2013 AH, I miss read your comment VB. Thought u said would. But, I'm dealing with large list like my cities list is 80k. I see other software using .sql for saving like profile and campaign data. How is this done with out using .csv or list? Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted March 22, 2013 Report Share Posted March 22, 2013 Yea, I guess I should have them create DB on their server would be best eh? 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.