SQL SSH Connection
I am trying to SSH into mySQL.
So far I have:
1) Before scraping session begins, I call the script SQL_SSH_CONNECTOR. It looks like this:
import com.screenscraper.datamanager.sql.*;
// SshDataSource
ds = new SshDataSource( "[email protected]", "SSH_pass_here" );
ds.setDriverClassName( "com.mysql.jdbc.Driver" );
ds.setUsername( "mysql_user_here" );
ds.setPassword( "mysql_user_pass_here" );
ds.setUrl( SshDataSource.MYSQL, 3306, "database_name_here" );
// Create Data Manager
dm = new SqlDataManager( ds, session );
// Build Schemas For all Tables
dm.buildSchemas();
session.setVariable("DBMANAGER", dm)
// SshDataSource
ds = new SshDataSource( "[email protected]", "SSH_pass_here" );
ds.setDriverClassName( "com.mysql.jdbc.Driver" );
ds.setUsername( "mysql_user_here" );
ds.setPassword( "mysql_user_pass_here" );
ds.setUrl( SshDataSource.MYSQL, 3306, "database_name_here" );
// Create Data Manager
dm = new SqlDataManager( ds, session );
// Build Schemas For all Tables
dm.buildSchemas();
session.setVariable("DBMANAGER", dm)
2) Then I manually extract data, at the end of the extract data file I added:
session.executeScript("SQL_SSH_QUERY");
3) SQL_SSH_QUERY looks like this
// Import the classes we'll need.
import com.screenscraper.datamanager.sql.SqlDataManager;
// Get the data manager from the session variable it is stored in
SqlDataManager dm = session.getVariable( "DBMANAGER" );
// Add data to the table.
dm.addData( "database_table_name", "data_base_column_name", session.getVariable("VAR_1") );
dm.addData( "database_table_name", "data_base_column_name", session.getVariable("VAR_2") );
// Once all data has been added to the table, it should be committed
dm.commit( "database_table_name" );
// Flush the data so it is written to the database
dm.flush();
import com.screenscraper.datamanager.sql.SqlDataManager;
// Get the data manager from the session variable it is stored in
SqlDataManager dm = session.getVariable( "DBMANAGER" );
// Add data to the table.
dm.addData( "database_table_name", "data_base_column_name", session.getVariable("VAR_1") );
dm.addData( "database_table_name", "data_base_column_name", session.getVariable("VAR_2") );
// Once all data has been added to the table, it should be committed
dm.commit( "database_table_name" );
// Flush the data so it is written to the database
dm.flush();
Questions
1) Did I do this correctly?
2) Do I need to buildSchemas?
-when I run it with buildSchemas the log gets stuck at Processing script: "SQL_SSH_CONNECTOR" which I assume is because it is trying to buildSchemas of 1000+ tables.
-I tried buildSchemas for just the table, but I can not get it to work(hopefully a syntax error)
EX.
dm.buildSchemas("database_table_name");
but that returned
ERROR--scraping_session: An error occurred while processing the script: SQL_SSH_CONNECTOR
scraping_session: The error message was: class bsh.EvalError (line 16): dm .buildSchemas ( "database_table" ) -- Error in method invocation: Method buildSchemas( java.lang.String ) not found in class'com.screenscraper.datamanager.sql.SqlDataManager'
scraping_session: The error message was: class bsh.EvalError (line 16): dm .buildSchemas ( "database_table" ) -- Error in method invocation: Method buildSchemas( java.lang.String ) not found in class'com.screenscraper.datamanager.sql.SqlDataManager'
3) Are table connections, foreign key required?
// Setup table connections
// parameter order: "child_table", "child_column", "parent_table", "parent_column"
dm.addForeignKey( "job", "person_id", "person", "id");
dm.addForeignKey( "address", "person_id", "person", "id");
// parameter order: "child_table", "child_column", "parent_table", "parent_column"
dm.addForeignKey( "job", "person_id", "person", "id");
dm.addForeignKey( "address", "person_id", "person", "id");
Thanks for any help.
SQL SSH Connection setup
1) The initial setup and saving process looks fine.
2) You do need to build schemas. This is how the Data Manager knows what columns and tables exist so it can build the queries correctly for insertion / updates. If you have 1000+ tables then it will take a long time to build them. You have the right idea with just building the schema for the table that you need to write to, which should greatly reduce the time in the buildSchemas call. Rather than calling it with a String you need to call it with List of Strings. For example:
tables.add("database_table_name");
dm.buildSchemas(tables);
3) You shouldn't need to add the foreign keys. The call to buildSchemas will also determine what the foreign keys are. The only reason you would need to add the foreign key information manually is if it is pertinent to what is being saved (ie the data manager will be writing to both parent and child tables) and your database table doesn't support foreign keys, such as if you are using MyISAM tables.
SOLVED: THANK YOU mikes
I thought it might have something to do with that, I just could not figure out to use the ArrayList. Thank you so much, all three answers are greatly appreciated!