Working with MySQL databases

Consider using the SqlDataManager as an alternative way to interact with your JDBC-compliant databases.

This example is designed to give you an idea of how to interact with MySQL, a JDBC-compliant database, from within screen-scraper.

You will need to have MySQL already installed and the service running.

To start, download the JDBC Driver for MySQL connector Jar file and place it in the lib/ext folder where screen-scraper is installed.

Next, create a script wherein you set the different values used to connect to your database. It is recommended that you call this script from your scraping session before scraping session begins.

//The values below may be different for you
// depending on your set up.
session.setVariable("MYSQL_SERVER_URL","localhost");
session.setVariable("MYSQL_SERVER_PORT","3306");
session.setVariable("MYSQL_DATABASE","mydb");
session.setVariable("MYSQL_SERVER_USER","username");
session.setVariable("MYSQL_SERVER_PASSWORD","password");

Create another script to set up your connection and perform queries on your database. Note, it is necessary to include the connection to your database within the same script as your queries.

You will be calling this script after you have extracted data. Typically this will either be after a scrapeable file runs or after an extractor pattern's matches are applied.

//Import the entire java.sql package
import java.sql.*;

//Set up a connection and a drivermanager.
Class.forName("com.mysql.jdbc.Driver").newInstance();
      Connection conn;
      conn = DriverManager.getConnection("jdbc:mysql://" + session.getVariable("MYSQL_SERVER_URL") + ":"+session.getVariable("MYSQL_SERVER_PORT") + "/" +  session.getVariable("MYSQL_DATABASE"), session.getVariable("MYSQL_SERVER_USER"), session.getVariable("MYSQL_SERVER_PASSWORD"));

//Set extracted variables to local variables.
//Depending on when your script is executed
// you may have variables in session scope
// and others as dataRecords.
value1 = session.getVariable("value1");
value2 = session.getVariable("value2");
value3 = dataRecord.get("value3");
value4 = dataRecord.get("value4");

//Create statements and run queries
// on your database.
Statement stmt = null;
stmt = conn.createStatement();

      mysqlstring="INSERT IGNORE INTO TABLE_NAME (column1, column2, column3, column4) VALUES('"+value1+"','"+ value2 + "','"+value3+"','" + value4 +"')";
      stmt.executeUpdate(mysqlstring);

//Be sure to close up your
// statements and connection.
stmt.close();
conn.close();