Getting a random value from DB table/column

[Anonymous]

Hi all,
I am new to TOS and Java and I am trying to "extend" my tRowGenerator component to use a function defined in routines that would connect to a DB defined in Metadata and randomly get a value from a table/column. My "code" is below (no laughing, guys, be nice). This function would be defined for a column in the Map Editor of the tRowGenerator component.
I know that this way is not efficient at all because for each row created in tRowGenerator, a connection to the DB is created. opened, data read and then the connection is closed and destroyed. I would love to be able to persist the first connection and reuse it for the rest of the rows to be created by the tRowGenerator component.
I tried dropping a tOracleConnection linked to the tRowGenerator via OnComponentOK, but I could not get the connection passed to my function with (java.sql.Connection) globalMap.get("conn_" + "tOracleConnection_1") but I get a null pointer error, as if the connection object never gets created.
How can I get access to a connection object that points to the object that, I assume, must be created by dropping the tOracleConnection component? I was thinking that I could pass the connection object as a parameter to my function like this: (java.sql.Connection) globalMap.get("conn_" + "tOracleConnection_1").
Any ideas?
Many thanks
GD
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public static String getRandomDBValue(String tableName, String columnName) {
//java.sql.Connection DBConn,

String returnValue = null;
Statement lStatement = null;
ResultSet lRS = null;

String lSQLString = "select distinct " + columnName + " from (select distinct " + columnName + " from " +tableName + " order by dbms_random.value ) where rownum=1";

try {
java.lang.Class.forName("oracle.jdbc.driver.OracleDriver");
String url_tOracleConnection_1 = "jdbc racle:thin:@" + "localhost" + ":" + "1521" + ":" + "XE";
String dbUser_tOracleConnection_1 = "user";
String dbPwd_tOracleConnection_1 = "pwd";
java.sql.Connection lConnection = null;

lConnection = java.sql.DriverManager.getConnection(url_tOracleConnection_1, dbUser_tOracleConnection_1,dbPwd_tOracleConnection_1);
lStatement = lConnection.createStatement();
lRS = lStatement.executeQuery(lSQLString);

if(lRS.next()) {
returnValue = lRS.getString(1);
}
lRS.close();
lStatement.close();
lConnection.close();
return returnValue;
}
catch (Exception ee) {
return "Error connecting/executing statement/query at the DB ";
}
}

[Anonymous]

Hi,
I would guess that you "tRowGenerator-function" is called before the database connection is opened. Could you show your job design or try to connect the components with an onComponentOk - link.
Bye
Volker

[Anonymous]

Hi Volker,
Thank you for taking time to answer my plea for help.
I tried dropping an OracleConnection component and linked it to the tRowGenerator component via onComponentOk (see screenshot sc1 below). Assuming that the connection is created at runtime, I changed my custom function to below:
public static String getRandomDBValue_WIP(Object object,String tableName, String columnName) {
String returnValue = null;
Statement lStatement = null;
ResultSet lRS = null;

String lSQLString = "select distinct " + columnName + " from (select distinct " + columnName + " from " +tableName + " order by dbms_random.value ) where rownum=1";
Connection lConnection = (Connection) object;

try {
lStatement = lConnection.createStatement();
lRS = lStatement.executeQuery(lSQLString);

if(lRS.next()) {
returnValue = lRS.getString(1);
}
lRS.close();
lStatement.close();
lConnection.close();
return returnValue;
}
catch (Exception eee) {
return "no --- " + eee.toString();
}
}
I call this function as a parameter for the target column in the tRowGenerator Editor (see sc3):
my_custom_code.getRandomDBValue_WIP(globalMap.get("conn_" + "tOracleConnection_1"),"EVENT2", "CREATED_BY")
EVENT2 is the table I am targeting
CREATED_BY is the column that I want to randomly pick a value from and insert as value for my generated column
When I run this I get the error in sc2: java.sql.SQLException: Closed
I am thinking that the connection is never opened in the first place.
Any ideas?
Thank you in advance
Gallas

[Anonymous]

If you want to persist your hardwired connection avoiding an open each time, place the Connection object outside the method, as a static member in the class; then you can reuse it.

[Anonymous]

Emaxt6,
I would like to avoid using the hardwired connection altogether. I want to reuse the connection created by having an tOracleConnection component that is defined in the repository.
I was trying your solution by placing the connection object and code in a static function outside, but I still have to call it in my code in tRowGenerator for every row. I will have to check for connection existence and all of that.
Please note I am new to Java and just learning.
Many thanks

[Anonymous]

I think I may have found the source of my problem after looking at the result in tLogRow. The first row was correct in getting a random value from a table in the DB as I expected but subsequent rows (therefore calls to my custom function) would have the connection closed error.
I remove the lConnection.close() code and VOILA, now I get random values for my targeted column for all generated row.
It will be nice to have this kind of DB lookup capability added to the tRowGenerator component. I will try to make it a request