populate_table.script

# This SLAMD script can be used to connect to a relational database over JDBC,
# create a table named 'testTable' and populate it with some number of rows.  
# The table will have four columns:  employeeID (a sequentially incrementing 
# integer), firstName (always the string 'user'), lastName (the same as 
# employeeID), and fullName (firstName {space} lastName).
#
# Many of the settings should be specified as script arguments.  The script
# arguments that may be used are:
#
# dbdriver    -- The fully-qualified name of the JDBC driver to use to connect
#                to the database.
# dburl       -- The JDBC URL to use to connect to the database.
# dbuser      -- The username to use to connect to the database.
# dbpassword  -- The password to use to connect to the database.
# numrows     -- The number of rows to create in the database.


# Make the JDBC data types available for use.
use com.sun.slamd.scripting.jdbc.JDBCConnectionVariable;
use com.sun.slamd.scripting.jdbc.JDBCPreparedStatementVariable;
use com.sun.slamd.scripting.jdbc.JDBCResultSetVariable;


# Define the variables that we will use.
variable int                   counter;
variable int                   numRows;
variable JDBCConnection        db;
variable JDBCPreparedStatement statement;
variable string                dbDriver;
variable string                dbURL;
variable string                dbUser;
variable string                dbPassword;
variable string                errorMessage;
variable string                failureReason;
variable string                firstName;
variable string                fullName;
variable string                lastName;
variable string                sql;


# Assign values to all the variables that can be set as arguments.
dbDriver   = script.getScriptArgument("dbdriver");
dbURL      = script.getScriptArgument("dburl");
dbUser     = script.getScriptArgument("dbuser");
dbPassword = script.getScriptArgument("dbpassword");
numRows    = script.getScriptIntArgument("numrows", 1000);


# Try to load the database driver.
if dbDriver.isNull()
begin
  script.logMessage("No DB driver class specified.");
  script.exitWithError();
end;

ifnot db.setDriverClass(dbDriver)
begin
  errorMessage = "Unable to load DB driver ";
  errorMessage = errorMessage.append(dbDriver);
  errorMessage = errorMessage.append(":  ");
  errorMessage = errorMessage.append(db.getFailureReason());
  script.logMessage(errorMessage);
  script.exitWithError();
end;
else begin
  script.debugMessage("Successfully loaded DB driver");
end;


# Try to connect to the database.
if dbURL.isNull()
begin
  script.logMessage("No JDBC URL specified to use to connect to the DB");
  script.exitWithError();
end;

ifnot db.connect(dbURL, dbUser, dbPassword)
begin
  errorMessage = "Unable to connect to the DB ";
  errorMessage = errorMessage.append(dbURL);
  errorMessage = errorMessage.append(":  ");
  errorMessage = errorMessage.append(db.getFailureReason());
  script.logMessage(errorMessage);
  script.exitWithError();
end;
else begin
  script.debugMessage("Successfully connected to the DB");
end;


# Try to create a new table in the database.
sql = "CREATE TABLE testTable ( ";
sql = sql.append("employeeID INT NOT NULL PRIMARY KEY, ");
sql = sql.append("firstName VARCHAR(30) NOT NULL, ");
sql = sql.append("lastName VARCHAR(30) NOT NULL, ");
sql = sql.append("fullName VARCHAR(60) NOT NULL );");

db.executeUpdate(sql);
failureReason = db.getFailureReason();
ifnot failureReason.isNull()
begin
  errorMessage = "Unable to create a new table:  ";
  errorMessage = errorMessage.append(failureReason);
  script.logMessage(errorMessage);
  db.disconnect();
  script.exitWithError();
end;
else begin
  script.debugMessage("Successfully created the table testTable");
end;


# Try to create an index for the employeeID column of the table.
sql = "ALTER TABLE testTable ADD INDEX testIndex (employeeID);";

db.executeUpdate(sql);
failureReason = db.getFailureReason();
ifnot failureReason.isNull()
begin
  errorMessage = "Unable to add an index for the table table:  ";
  errorMessage = errorMessage.append(failureReason);
  script.logMessage(errorMessage);
  db.disconnect();
  script.exitWithError();
end;
else begin
  script.debugMessage("Successfully created the index for testTable");
end;


# Create a prepared statement that we will use to actually create the rows.
sql = "INSERT INTO testTable VALUES (?, ?, ?, ?);";
statement = db.prepareStatement(sql);
if statement.isNull()
begin
  errorMessage = "Unable to create prepared statement ";
  errorMessage = errorMessage.append(sql);
  errorMessage = errorMessage.append(":  ");
  errorMessage = errorMessage.append(failureReason);
  script.logMessage(errorMessage);
  db.disconnect();
  script.exitWithError();
end;
else begin
  script.debugMessage("Successfully created the prepared statement");
end;


# Create a loop that will try to add the appropriate number of users.
counter = 1;
while counter.lessThanOrEqualTo(numrows)
begin
  firstName = "User";
  lastName  = counter.toString();
  fullName  = firstName;
  fullName  = firstName.append(" ");
  fullName  = fullName.append(lastName);
  
  statement.setInteger(1, counter);
  statement.setString(2, firstName);
  statement.setString(3, lastName);
  statement.setString(4, fullName);
   
  statement.executeUpdate();
  failureReason = statement.getFailureReason();
  ifnot failureReason.isNull()
  begin
    errorMessage = "Unable to execute SQL update ";
    errorMessage = errorMessage.append(sql);
    errorMessage = errorMessage.append(":  ");
    errorMessage = errorMessage.append(failureReason);
    script.logMessage(errorMessage);
    db.disconnect();
    script.exitWithError();
  end;
  else begin
    errorMessage = "Added row ";
    errorMessage = errorMessage.append(counter.toString());
    script.debugMessage(errorMessage);
  end;
  
  counter.increment();
end;


# Disconnect from the database.
db.disconnect();
script.debugMessage("All processing complete.");
script.exit();