Jump to content
UBot Underground

Recommended Posts

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_sample
  • and a new user called: ubotter
  • who has the access password: awesomepass

Of 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

  • Like 11
Link to post
Share on other sites

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... :ph34r:

  • Like 3
Link to post
Share on other sites

Have a look at http://www.navicat.com/

The best Tool i ever used for Querys and MySQL DB-Handling

And 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

Link to post
Share on other sites

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

  • Like 2
Link to post
Share on other sites

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.

  • Like 2
Link to post
Share on other sites

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...

Link to post
Share on other sites
  • 2 weeks later...

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!!!

  • Like 1
Link to post
Share on other sites

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

Link to post
Share on other sites
  • 2 months later...

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
Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 2 weeks later...

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

Link to post
Share on other sites

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...

Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...