SqlDataManager.getColumns etc. not working with Oracle?
Hi,
I'm trying to add data and such to my database with the new SqlDataManager. I have two questions.
1: Problem #1 is that I can't get it to work. I get an NullExecption with this line:
s = dm.getColumns("MY_TABLE");
I can't find out where I went wrong. Might it be an Oracle specific thing?
2: Is it possible to use Eclipse for this? I can't get it to work, because the SqlDataManager takes a session parameter.
import com.screenscraper.datamanager.sql.*;
import com.screenscraper.datamanager.*;
import org.apache.commons.dbcp.BasicDataSource;
import java.util.ArrayList;
// BasicDataSource
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName( "oracle.jdbc.driver.OracleDriver" );
ds.setUsername( "myusername" );
ds.setPassword( "mypassword" );
ds.setUrl( "jdbc:oracle:thin:@myip:myport:mysid" );
ds.setMaxActive( 10 );
// Create Data Manager
SqlDataManager dm = new SqlDataManager( ds, session );
List l = new ArrayList();
l.add("competitor_category");
s = dm.getColumns("MY_TABLE");
session.setVariable("test", s);
session.breakpoint();
dm.buildSchemas(l);
DataRecord dr = new DataRecord();
dr.put("STOREID",107);
dr.put("NAME","test name");
dr.put("DESCRIPTION","test desc");
dr.put("ROOT",1);
dm.addData("MY_TABLE",dr);
dm.commit("MY_TABLE");
import com.screenscraper.datamanager.*;
import org.apache.commons.dbcp.BasicDataSource;
import java.util.ArrayList;
// BasicDataSource
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName( "oracle.jdbc.driver.OracleDriver" );
ds.setUsername( "myusername" );
ds.setPassword( "mypassword" );
ds.setUrl( "jdbc:oracle:thin:@myip:myport:mysid" );
ds.setMaxActive( 10 );
// Create Data Manager
SqlDataManager dm = new SqlDataManager( ds, session );
List l = new ArrayList();
l.add("competitor_category");
s = dm.getColumns("MY_TABLE");
session.setVariable("test", s);
session.breakpoint();
dm.buildSchemas(l);
DataRecord dr = new DataRecord();
dr.put("STOREID",107);
dr.put("NAME","test name");
dr.put("DESCRIPTION","test desc");
dr.put("ROOT",1);
dm.addData("MY_TABLE",dr);
dm.commit("MY_TABLE");
You are looking for the data dictionary
This article explains a bit about it.
http://www.eveandersson.com/writing/data-model-reverse-engineering
this is an example of sequences:
select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE
from USER_SEQUENCES
this is an example of indexes:
SELECT INDEX_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME='TABLE_NAME'
ORDER BY INDEX_NAME
/Gustav
DataManager fix for Oracle
Okay I think that I have everything *mostly* working for Oracle (the version I used was 10g Express Edition), and for the stuff that isn't working there is a workaround (mostly the parsing of database keys). I will add some notes in the documentation but in the meantime you will need to do the following to get the DataManager working for Oracle:
1. You will need to update your screen-scraper installation to the latest alpha. Go to Options->Settings and check Allow upgrading to unstable versions, then Options->Check for updates. The update (5.0.11a) should be sometime tomorrow.
2. For the url I had to put the username/password in the url for the connection to work. Not sure why the ds.setUsername/Password wasn't sufficient for me, but I don't know much about Oracle.
ds.setDriverClassName( "oracle.jdbc.driver.OracleDriver" );
ds.setUsername( "system" );
ds.setPassword( "secretpassword" );
ds.setUrl("jdbc:oracle:thin:system/secretpassword@localhost:1521:XE");
3. Instead of using DataManager.buildSchemas() use DataManager.buildSchemas(List tables), so if you have tables "person", "address", and "occupation" in your database you would use the following code:
tables.add("person");
tables.add("address");
tables.add("occupation");
dm.buildSchemas(tables);
Otherwise it will try to parse out all the database administration tables also, which takes a long time and caused problems.
4. If you have any tables with auto incremented sequences for column values add the following for each of those tables
//"PERSON" is the table name and "PK" is the column that is auto incremented
5. If you are using relationships between tables, say a one to many between a table "PERSON" and "ADDRESS", and you want the datamanager to automatically take care of the relationship for you then you will need to manually tell the DataManager what the foreign key relationships are.
dm.addForeignKey("PERSON_SUB", "PERSON_PK", "PERSON", "PK");
Some other notes after looking at your code again:
- The addData("TABLE", Map data) method can accept anything that implements the Map interface(which dataRecord does) so you don't have to use a dataRecord in your own code, you could use a HashMap, HashTable, TreeMap, etc. There is also the method addData(String table, String column, String value)
- I probably should have used better vocabulary but "commit" doesn't write to the database, instead it takes the data that you have added and sets it aside to be written, so the next time addData is called it will be put in a new record(row in the database). To perform the write call DataManager.flush(). I suggest calling flush once you have an atomic chunk of data to write, say a row and all related rows.
- Here is a quick example that adds data to a table "PERSON" and a child table "ADDRESS". In this example there is an autoincrement column named "PK" for the table "PERSON" and another for the table "ADDRESS". There is also a foreign key relationship between a column "ADDRESS"."PERSON_PK" and "PERSON"."PK". All of the linking data between the two columns is taken care of behind the scenes by the DataManager.
import com.screenscraper.datamanager.sql.*;
import org.apache.commons.dbcp.BasicDataSource;
import java.util.*;
SqlDataManager dm;
// Oracle datasource, I used 10g Express Edition with the thin client driver
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName( "oracle.jdbc.driver.OracleDriver" );
ds.setUsername( "system" );
ds.setPassword( "secret_password" );
ds.setUrl("jdbc:oracle:thin:system/secret_password@localhost:1521:XE");
// Create Data Manager
dm = new SqlDataManager( ds, session );
// set the logging level to debug, this will output the sql the is generated.
dm.setLoggingLevel(org.apache.log4j.Level.DEBUG);
//create a list of the tables you need.
List tableList = new ArrayList();
tableList.add("person");
tableList.add("address");
//read in the database info, this needs to done before you add data or retrieve any info about the database
//you can use dm.getConnection before this, however.
dm.buildSchemas(tableList);
//tell the datamanager which columns are auto incremented.
dm.getSchema("PERSON").addAttribute("PK", Schema.AUTOINCREMENT, Schema.AUTOINCREMENT_TRUE);
dm.getSchema("ADDRESS").addAttribute("PK", Schema.AUTOINCREMENT, Schema.AUTOINCREMENT_TRUE);
//tell the dataManager about all the foreign key relationships you need.
dm.addForeignKey("ADDRESS", "PERSON_PK", "PERSON", "PK");
DataManager dm
for(int i=0;i<10;i++)
{
//add data to person, the columns in "PERSON" are "PK" (autoincrementing primary key) and "NAME" (VARCHAR2)
dm.addData("PERSON", "NAME", "test name " + i);
for(int j=0;j<2;j++)
{
//add data to a child table "ADDRESS", the columns in "PK"(autoincrementing primary key) ,"PERSON_PK" ("PERSON"."PK" foreign key),"ADDRESS", and "CITY"
Map addressData = new HashMap();
addressData.put("ADDRESS", "test address " + i);
addressData.put("CITY", "New York");
dm.addData("ADDRESS", addressData);
dm.commit("ADDRESS");
}
//all the data for the row AND all if its children(ADDRESS) need to be committed before the parent(PERSON) is commited
dm.commit("PERSON");
//this is where the data is actually written out to the database
dm.flush();
}
//example of using getConnection(), this uses a connection pool so always close the connection when you are done with it
//or you could get a resource deadlock.
Connection con = dm.getConnection();
try
{
PreparedStatment ps = con.prepareStatement("SELECT name,address FROM person JOIN address ON person.pk = address.person_pk");
ResultSet rs = ps.executeQuery();
while(rs.next())
{
session.log(rs.getString("name") + ":" + rs.getString("address"));
}
ps.close();
}
finally
{
con.close();
}
//close down the datamanager, this releases all the database connections in the pool
dm.close();
Hi Ryan, I didn't see you
Hi Ryan, I didn't see you finished the 5.0.11a before now!
Fantastic work!
I will test it in the next few days.
I do however have a few notes that might help you, if you decide to dive deeper into it.
When you log in with system, you will see all tables with buildschemas, simply because you have access to everything (well...almost everything, the user sys has a bit more).
Normally, one would use another user than sys or system in a project, and the user would have a seperate tablespace with own schemas.
If my user is named "ryan" I might create a default tablespace called "ryanspace". The user will access all in the "ryan" schema by default, but if user rayn wants to access the user gustav's tables, ryan would need to write
Regarding commit, you might want to make commit actually do the changes to avoid confusion, because commit is used in oracle to actually MAKE the changes, and every DML (data manupulation) before that will not be committed, but kept in the buffer, ready to be committed or rolled back.
Glad you are using the
Glad you are using the datamanager. It was a tool I developed for internal use and then we decided to add it for others to use, so bits of it are rough around the edges still, especially with databases we don't use internally much. I am very willing to fix it/tweak it as users need though.
For the first question I am not sure what is going wrong, but I have a pretty good idea. Your code looks good. Oracle was working at one point but I think that some optimisations I added on the buildSchemas method use a feature not supported by the driver and it broke it. I am downloading the Oracle database now and will test it and fix it. Hopefully we will have an alpha release really soon to resolve it.
For the second question, yes you can use it in Eclipse. Just include the screen-scraper.jar file from the install directory in your build path. The session object is of type com.screenscraper.scraper.ScrapingSession. If you are writing your own code to be accessed from inside screen-scraper I would just pass that session variable.
The reason I have it pass in the session is so that the logging is tied together and also for the methods that do stuff with session variables, but I added that stuff after it was mostly written so if you require an instantiation method that doesn't require the session variable let me know and I could add that in really easily.
Any news?
Hi Ryan,
Any news about this?
It would help me a lot in planning my project if you could give some kind of estimate on timeframe for the alpha.
Best regards
Gustav
Hi Ryan, thank you for the quick
Hi Ryan, thank you for the quick reply.
I was really happy when I read about you making an Oracle fix! We are looking forward to it :-)
If it performs even moderately well we will use the DataManager, it seems like an easy way to integrate ScreenScraper with our database.
I will use the .jar file and pass the session from SS as you suggested, so no need to write another method.
Bonus question: If I need to do other stuff with the database, like doing something that I can't use the DataSet for, and I need to get a connection using SqlDataManager.getConnection(), how do I release it? I can't find a release method, to release it back to the pool. If I just close it, wouldn't it create some overhead establishing it again, when the pool-reaper finds out that it is closed? Or am I looking at this in a wrong way?
/Gustav
Sorry it has taken me some
Sorry it has taken me some time to get to this fix. I am having to learn how to use Oracle as I go. As for the connection it does use a connection pool (apache commons dbpool) in the background so just call close() on it when you are done and it will be returned to the pool but left open so there is minimal overhead.
oracle
I might be able to help you if its query optimizing you are looking for. :-)
I have been working with it for a number of years now.
/gustav
The biggest issue I have had
The biggest issue I have had is extracting metadata from the database for foreign keys and which columns are attached to a sequence for autoincrementing keys. I have tried to stick to JDBC functions via the java.sql interface but they don't seem supported very well for Oracle. Any suggestions of queries I could use to extract this data? For now you just have to manually tell the datamanager what they are for Oracle. It looks like my queries are all working properly as I tried to reduce it down to sql supported by all databases. Let me know if you have issues with the fix posted above.