SQL Power Software Forum

I've just download the latest version of Java (6-29) on my machine.

PA now hungs when I connect to the DB. I cannot connect (or test the connection) as it hungs with no error codes: the application is completely hung.

I've tried to re-install PA and re-install Java to no avail.

++++UPDATE++++
I found JRE 6-27 in one of my systems. I've disinstalled 6-29 and installed 6-27 and now PA is back to work!!!!

It begs the question why??

Many thanks in advance for any help

Lou

Hi

After few months of absence, having had problems recenty with PA and SQL Server 2008 (R2 this time!), I thought to test the various drivers and report the errors.
This post is about using jtds jtds-1.2.5 and SQL Server R2.
With this driver I manage to drag and drop the table AWBuildVersion successfully (better than jdbc4-3.0).
Issues:
1. the drver does not recognise the NVarchar data type and convert it to Varchar.
2. PA is representing for each DB as schemas objects that are not schemas, like db_accessadmin etc. Please see a discussion on this subject between me and Jonathan last year (SQL Server 2008 no schema), where we discussed the type of query PA needs to use to select the schema(s) and nothing else.
3. When I try to export to pdf the content of the playpen I get the following error:
java.lang.IllegalStateException: Event for a transaction rollback must fired on the foreground thread.
at ca.sqlpower.object.AbstractSPObject.fireTransactionRollback(AbstractSPObject.java:494)
at ca.sqlpower.sqlobject.SQLDatabase.setPlayPenDatabase(SQLDatabase.java:535)
at ca.sqlpower.architect.swingui.PlayPen.setDatabase(PlayPen.java:783)
at ca.sqlpower.architect.swingui.PlayPen.<init>(PlayPen.java:626)
at ca.sqlpower.architect.swingui.PlayPen.<init>(PlayPen.java:609)
at ca.sqlpower.architect.swingui.PlayPen.<init>(PlayPen.java:605)
at ca.sqlpower.architect.swingui.PlayPen.<init>(PlayPen.java:669)
at ca.sqlpower.architect.swingui.action.ExportPlaypenToPDFAction.doStuff(ExportPlaypenToPDFAction.java:122)
at ca.sqlpower.architect.swingui.action.ProgressAction$2.doStuff(ProgressAction.java:11

at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:104)
at java.lang.Thread.run(Unknown Source)

The tests are done using Windows 2008 R2 64 bits and SQL Server R2 (Nov. CTP).

Hi
After few more tests I have a better control of the situation.
Previous email reports errors using PA 0.9.16 & Microsoft jdbc driver sqljdbc4. After dis-installing PA & clearing the old PL.ini & creating a new one I understand that the driver loaded with this version of PA is sqljdbc4-3.0.
I run tests on SQL Server 2008 R2 (Nov. CTP) and this driver.
Dragging a table into the playpen (from AdvetureWorks DB) I get the following conversion error:
java.lang.RuntimeException: ca.sqlpower.sqlobject.SQLObjectException: Failed to populate columns of tables
at ca.sqlpower.sqlobject.SQLObject$1.run(SQLObject.java:192)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: ca.sqlpower.sqlobject.SQLObjectException: Failed to populate columns of tables
at ca.sqlpower.sqlobject.SQLTable.populateAllColumns(SQLTable.java:421)
at ca.sqlpower.sqlobject.SQLTable.populateColumns(SQLTable.java:354)
at ca.sqlpower.sqlobject.SQLTable.populateImpl(SQLTable.java:117

at ca.sqlpower.sqlobject.SQLObject.populate(SQLObject.java:185)
at ca.sqlpower.sqlobject.SQLTable.getChildren(SQLTable.java:1849)
at ca.sqlpower.sqlobject.SQLObject.getChildren(SQLObject.java:284)
at ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.ensurePopulated(PlayPen.java:1545)
at ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.doStuff(PlayPen.java:1520)
at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:104)
at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from char to SMALLINT is unsupported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1129)
at com.microsoft.sqlserver.jdbc.IntColumnIdentityFilter.apply(SQLServerDatabaseMetaData.java:2293)
at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:114)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1981)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1966)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getObject(SQLServerResultSet.java:2255)
at ca.sqlpower.sql.jdbcwrapper.ResultSetDecorator.getObject(ResultSetDecorator.java:269)
at ca.sqlpower.sql.CachedRowSet.populate(CachedRowSet.java:250)
at ca.sqlpower.sql.CachedRowSet.populate(CachedRowSet.java:209)
at ca.sqlpower.sql.CachedRowSet.populate(CachedRowSet.java:197)
at ca.sqlpower.sql.jdbcwrapper.SQLServerDatabaseMetaDataDecorator.getColumns(SQLServerDatabaseMetaDataDecorator.java:56)
at ca.sqlpower.sqlobject.SQLColumn.fetchColumnsForTable(SQLColumn.java:360)
at ca.sqlpower.sqlobject.SQLTable.populateAllColumns(SQLTable.java:389)
... 9 more
The tale (AWBuildVersion in AdventureWorks DB) contains a tynint pk; a nvarchar and 2 timestamp fields.
________________________________
I get this error on every table I tried to drag and drop to the playpen.

I've had problems with the other drivers I've tested: please see other messages for details.

Lou

Hi guys

I've been on other sort of projects for a while and I've not used PA since September 2009.
Last version I've used successfully (just minor bugs) was 0.9.13.
JDBC driver was Microsoft sqljdbc4.jar.

Today I've installed the 0.9.16 version (downloading Architect-Setup-Windows-jdbc-0.9.16.exe from Hudson website).
Disaster!!
I try to srag and drop on the playpen the table HumanResources.Employee and I get a series of errors (see below), including errors referring to other tables in other schemas!

Considering that my installation hasn't changed but the only thing that has changed is the version of Power Architect, what is exactly going on?

Incidentally, I keep on seeing messages of guys having SQL Server 2008 / 05 problems and the PA guys saying that all is fine "in SQL Server 2000"!!!
Please note that the two versions are different (e.g. one has schemas the other not), and from previous messages I understand that your treatment of the two is very different.

Any light you can shed is useful.

Error messages are:
java.lang.RuntimeException: java.lang.IllegalStateException: FK table Production.Document (TABLE) is missing columns, cannot populate relationships.
at ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.cleanup(PlayPen.java:1652)
at ca.sqlpower.swingui.SPSwingWorker$1.run(SPSwingWorker.java:114)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.IllegalStateException: FK table Production.Document (TABLE) is missing columns, cannot populate relationships.
at ca.sqlpower.sqlobject.SQLRelationship$2.run(SQLRelationship.java:704)
at ca.sqlpower.architect.swingui.ArchitectSwingSessionImpl.runInForeground(ArchitectSwingSessionImpl.java:1106)
at ca.sqlpower.sqlobject.SQLRelationship.fetchExportedKeys(SQLRelationship.java:747)
at ca.sqlpower.sqlobject.SQLTable.populateRelationships(SQLTable.java:603)
at ca.sqlpower.sqlobject.SQLTable.populateRelationships(SQLTable.java:583)
at ca.sqlpower.sqlobject.SQLTable.createTableFromSource(SQLTable.java:285)
at ca.sqlpower.sqlobject.SQLTable.createInheritingInstance(SQLTable.java:272)
at ca.sqlpower.architect.swingui.PlayPen.importTableCopy(PlayPen.java:1260)
at ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.cleanup(PlayPen.java:1600)
... 9 more

java.lang.NullPointerException
at ca.sqlpower.sqlobject.SQLRelationship$2.run(SQLRelationship.java:703)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

java.lang.RuntimeException: java.lang.NullPointerException
at ca.sqlpower.sqlobject.SQLTable$4.run(SQLTable.java:650)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.NullPointerException
at ca.sqlpower.sqlobject.SQLTable$4.run(SQLTable.java:629)
... 8 more
java.lang.NullPointerException
at ca.sqlpower.sqlobject.SQLRelationship$2.run(SQLRelationship.java:703)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

java.lang.IllegalStateException: Transaction ended outside of a transaction.
at ca.sqlpower.architect.swingui.dbtree.DBTreeModel$DBTreeSPListener.transactionEnded(DBTreeModel.java:321)
at ca.sqlpower.object.AbstractSPObject.fireTransactionEnded(AbstractSPObject.java:463)
at ca.sqlpower.object.AbstractSPObject.commit(AbstractSPObject.java:500)
at ca.sqlpower.sqlobject.SQLTable$7.run(SQLTable.java:1191)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

If I change the underlying DB once a PA project is open, refresh does not seem to pick up changes. E.g I drop a table in SQL Server with PA open, then I refresh to see the change in PA: no result...
In a past error message there was reference to the fact that PA keeps the lineage of the changes to the underlying DB. If this is the case it is easy to understand why changes to the underlying DB may srew up things, but changes are facts of life...

Kind regards

Lou

Now that the new jdbc from Microsoft has been successfully integrated in PA (Jonathan, I still expect a comment from my last post!), there are few things we could do to improve the cleanliness of the tree.
1. Considering that we use PA in development machines, it is quite normal to see 20 or more DB on the tree (expecially in SQL Server). It would be good to have a hide /unhide feature to eliminate DBs that are not part of current piece of work. It would require some graphical thing to signal that some DB are hidden.
2. Similarly, why not hide / unhide schemas?
3. It is very good to have in bold DBs and schemas that are used. Why don't we have the same for tables? If we are working on a DB of more that 100 tables it would be very good to identify quickly which tables I've dragged onto the playpen with bold as for DBs and schemas.
4. If I drag an object starting by v, it will be at the end of the list of objects for a certain schema. Currently PA reset the position all the time to the top: would be possible to memorise the position of the last drag operation - or cursor action?

Kind regards

Lou

When I started to use "Compare DM" I thought it was really great and a good time saver.

On the other hand I was working on a 7 tables DB, with all the tables in the playpen.

Now I moved to work on a multi-fact table EDW with nearly 100 tables overall and "Compare DM" is useless:
I typically work on limited areas of the EDW at a time, bringing in the playpen only the table that I need to change and few other they reference. If I try to use Compare DM the tool is either trying to generate the 70/80 tables I do not have in the fact table or is trying to drop them.

What I need is the ability to refer not just to DB structures (like schemas / tables etc.), but the ability to create artificial collection of tables (created for example by business area or fact table) and to have the Compare DM refer to these.

It is not a new feature: Microsoft BI suite allows users of the "data set view" tool (in SSRS and SSAS) to create "views over a complex datamodel. It is a very useful feature for large models.

This is the sequence of events:
I use sql server 2005 management studio & PA 0.9.13-20090505170005.
I have a db created in the playpen.
I've done some changes in the source system, deleting some ref. constraints etc.
Everything seems fine in the source.
I refresh and I get the following error. It seems that there is some lock on a column. Not sure how to get out of this one...
Kind regards Lou

ca.sqlpower.sqlobject.LockedColumnException: Locked column belongs to relationship tblMeasurementUnit_tblOutput_fk
at ca.sqlpower.sqlobject.SQLRelationship.checkColumnLocked(SQLRelationship.java:1333)
at ca.sqlpower.sqlobject.SQLTable$Folder.removeImpl(SQLTable.java:1123)
at ca.sqlpower.sqlobject.SQLObject.removeChild(SQLObject.java:391)
at ca.sqlpower.sqlobject.SQLObject.removeChild(SQLObject.java:400)
at ca.sqlpower.sqlobject.SQLObjectUtils.refreshChildren(SQLObjectUtils.java:272)
at ca.sqlpower.sqlobject.SQLTable.refreshColumns(SQLTable.java:1622)
at ca.sqlpower.sqlobject.SQLObject.refresh(SQLObject.java:853)
at ca.sqlpower.sqlobject.SQLObject.refresh(SQLObject.java:874)
at ca.sqlpower.sqlobject.SQLObject.refresh(SQLObject.java:874)
at ca.sqlpower.sqlobject.SQLDatabase.refresh(SQLDatabase.java:66

at ca.sqlpower.architect.swingui.action.RefreshAction$RefreshMonitorableWorker.doStuff(RefreshAction.java:85)
at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:99)
at java.lang.Thread.run(Unknown Source)

Hi
did you try the query?
I'm working on Power*Architect 0.9.13-20090505170005
with the "old" jdbc on a SQL Server 2005 project. The DB I'm working on has security defined on 10 schemas, but all the tables are on dbo.
It is quite annoying to have all these empty schemas in PA...

Hey, what about introducing the possibility to hide some of them if they are not required??

Kind regards

Lou

Reply reposted because it didn't appear in recent topics...

Hi
did you try the query?
I'm working on Power*Architect 0.9.13-20090505170005
with the "old" jdbc on a SQL Server 2005 project. The DB I'm working on has security defined on 10 schemas, but all the tables are on dbo.
It is quite annoying to have all these empty schemas in PA...

Hey, what about introducing the possibility to hide some of them if they are not required??

Kind regards

Lou

(originally posted on a wrong thread)

Posted on the wrong thread!!

Source DB info:

Well, I think that PA is not just a tool for ETL users, but a tool for data modelling in a wider sense, starting from provide documentation of existing DBs.

It is good to have a column source description, but I think we should also have a table one.
A table is either created in the playpen as new, or dragged from an existing DB.
I think that the existing Table property menu could do well with a field that says whether the table was generated as new or taken from an existing DB.

Will any of these information be visible when the cursor hovers on a table / column, in some future release?

Kind regards

Lou

1. At the table level it would be good I think to have somewhere the info on the source db.
It could be a default remark, or maybe a special field that I could read hovering on a table for few seconds...
2. It would be good to be able to write on the playpen directly and add comments, arrows etc. directly there, for example to the benefit of the pdf export.

Some more nice to have:
Say I change the order of columns in a table in the playpen, then I want to align the source system to these changes, but preserving the data in the tables. In some DB, e.g. MySQL is out-of-the-box in other like sql server I need to copy data to a temp table etc. because there is no standard sql to do this (as far as I know). It would be a cute feature...

Kind regards

Lou

Hi
I open a table on the explorer tree (I mean the tree on the left of the playpen).
I see the data types of each column, for example nchar(50).

I drag the column to the playpen, and the datatype of the column becomes char(50).

Similarly datetime becomes timestamp in the playpen.

Maybe I just do something wrong?
how can I have consistency bentween what I see on the tree and the playpen (until I deliberately change it on the playpen of course...).

Kind regards

Lou

Hi

Would it make sense for future releases of PA to be able to specify within the tool, for a table, a group of tables or indexes, the filegroup (sql server) or tablespace where they have to be created?

Kind regards

Lou