Jump to content

Best suggested approach to db support?


Recommended Posts

  • 7 months later...
Posted

I'm making a snippet for scripts to connect directly to a mySQL db. I believe I read somewhere, as long as the library is in the same folder as the script/osbot client, can't remember which one, it will work. Haven't tested it yet but will probably do in the next few days. I can report back how it went.

  • Like 1
Posted
2 hours ago, Nor3g said:

I'm making a snippet for scripts to connect directly to a mySQL db. I believe I read somewhere, as long as the library is in the same folder as the script/osbot client, can't remember which one, it will work. Haven't tested it yet but will probably do in the next few days. I can report back how it went.

 

On 8/3/2018 at 6:32 PM, liverare said:

I was able to successfully interact with a MySQL database from a script.

I created a folder on my Desktop and put in the following:

  • osbot-sql/
  • osbot-sql/lib/mysql-connector-java-8.0.11.jar
  • osbot-sql/lib/OSBot 2.5.8.jar
  • osbot-sql/Start OSBot with SQL Driver.bat

 

The contents of the batch file is as follows:


"C:\Program Files\Java\jre1.8.0_172\bin\java.exe" -cp "lib/*" org.osbot.Boot -debug

The first bit "C:\...\..\..\" can be changed to just "java" if your running version of Java is compatible with OSBot. Mine isn't. If yours is, then you can simply do the following:


java -cp "lib/*" org.osbot.Boot -debug

Once the bot loaded, I then ran a simple test script to read from a table called "big_league" from my database. The test script is as follows:


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.osbot.rs07.script.Script;
import org.osbot.rs07.script.ScriptManifest;

@ScriptManifest(author = "", info = "", logo = "", name = "SQL Test", version = 0)
public class Test extends Script {

	@Override
	public void onStart() throws InterruptedException {
		
		try {
			
			String host = "localhost:3306";
			String db   = "big_league";
			String user = "root";
			String pass = "";
			
			String connStr = String.format("jdbc:mysql://%s/%s?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", host, db);
			
			Connection conn = DriverManager.getConnection(connStr, user, pass);
			
			ResultSet results = conn.createStatement().executeQuery("SELECT `name` FROM `item`;");

			while (results.next()) {
				
				logger.debug(results.getString("name"));
			}
			
		} catch (SQLException e) {
			logger.error(e);
		}
		
	}

	@Override
	public int onLoop() throws InterruptedException {
		return 50;
	}
}

And it worked. It printed out the 3 item names I had stored in my database. If a query works, then insert, update, delete, drop, etc. will also work too.

 

Note: Your script won't be approved for SDN release because:

  • SDN compiler won't have those libraries, so ClassNotFoundException will occur.
  • Developers don't want to add bloat to OSBot that only benefits a few users.
  • Potential licensing/legal issues using external libraries commercially, or redistributing them.
  • Like 3

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...