Script interacting with database

[Zummy]

Hi, I've started working on a new project but I have come across an obstacle and I need some help. I was wondering if it is possible to write a script that calls a method in another java file which in turn interacts with a local database. I have a method that can interact with the database but I am having trouble with the script not performing the method (which is seperate from the script files). I basically just want the script to update a variable in the database with just java and sql.

Edited August 3, 2018 by Zummy

[HeyImJamie]

You'll probably need to do something like Java -> Php -> Sql as Osbot's security features block a fair amount.

[Zummy]

1 hour ago, HeyImJamie said:

You'll probably need to do something like Java -> Php -> Sql as Osbot's security features block a fair amount.

Yeah I was afraid of that, I'll find another way. Thanks for the reply!

[liverare]

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

• I used XAMPP Control Panel v3.2.2 to handle Apache and MySQL locally.
• I used mysql-connector-java-8.0.11.jar as my SQL driver.

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.

Edited August 3, 2018 by liverare

[Zummy]

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

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

• I used XAMPP Control Panel v3.2.2 to handle Apache and MySQL locally.
• I used mysql-connector-java-8.0.11.jar as my SQL driver.

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.

I get a can't find database error, I just want to thank you for the nice write up and taking the time for it! But... would you be so kind to guide me through the process of connecting everything together, I have xampp with mysql active on port 3306 but I don't know how to configure everything, use the driver and link it to my existing local database .

[liverare]

In XAMPP, you need to be running both the Apache and MySQL.

In your script, you need to make sure these are correct for you:

String host = "localhost:3306";
String db   = "big_league";
String user = "root";
String pass = "";

The only variable you should need to change is 'db', because the rest are default for me so they should be default for you.

Failing that, I may be able to assist you via TeamViewer later. If you still can't get it working, PM me.

[Zummy]

I think the issue is the fact that I'm using a sqlite database which produces a .db file instead of mysql files and tables etc. can't be read from a .db database. Where is are your database files located?

Edited August 5, 2018 by Zummy

[dormic]

I can confirm that @liverare's post works.

[TheCosmicWolf]

Thanks for the solution @liverare, i was searching for this and you provided a solution!

[fyermen8]

@liverare god job

[Pegasus]

cmd printed

Failed to load local script

after I click add account in client.

[Zunarb]

I keep getting java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/mule?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC what am i doing wrong here ?

[Xlash]

I cannot get this method to work, even without running a script. I just get the "Failed to load local script" errors.

EDIT: Now I got the library to work but I get the following

"Suppressed: java.security.AccessControlException: access denied ("java.security.SecurityPermission" "insertProvider.MySQLScramShaSasl")"

 

Edited March 11, 2021 by Xlash

[Jarl]

23 minutes ago, Xlash said:

I cannot get this method to work, even without running a script. I just get the "Failed to load local script" errors.

Most likely you are not building your jar properly. Check your artifacts