Best suggested approach to db support?

[DeadPk3r]

I'm looking for insight on what you guys would suggest using for database support with scripts such as storing accounts etc.

Edited June 29, 2018 by DeadPk3r

[lizzzerd]

Learn some basic mySQL. learn some basic queries, how to manipulate information, etc... Then you move up to manipulating the db with your scripts

[Pegasus]

Does osbot support external library( external jar file)?

[FrostBug]

1 hour ago, Pegasus said:

Does osbot support external library( external jar file)?

No - most scripters use a web server as a mediator between script and database

[Zappster]

If you're doing the route of java -> php webserver -> database then make sure you use prepared statements or your putting your shit at risk

PDO: http://php.net/manual/en/pdo.prepare.php

MySQLi: http://php.net/manual/en/mysqli.prepare.php

https://stackoverflow.com/questions/24988867/when-should-i-use-prepared-statements/24989031

Edited June 29, 2018 by Zappster

[FrostBug]

33 minutes ago, Zappster said:

If you're doing the route of java -> php webserver -> database then make sure you use prepared statements or your putting your shit at risk

Damn, you almost had it

[Pegasus]

How to reverse the order  database -> php webserver -> java ?

script post http request to php webserver every second?

Edited February 3, 2019 by Pegasus

[Nor3g]

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.

[liverare]

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

[Pegasus]

8 hours ago, liverare said:

 

I tired this but cmd printed : Failed to load local script

after I click add account in  osbot.

[toincow]

----

Edited February 24, 2023 by toincow