I have the dialect support almost ready to test, so it will likely be in the next snapshot release.

Just curious, but if a Teiid VDB just wraps data sources to other real databases, what is preventing you from connecting to the real database, which probably already has a dialect?  It seems to me that the advantage with that is that Teiid won't be obscuring the data type information, possibly giving you a more precise "create table" statement.  Maybe I am just missing the use-case.  However, suppose you used SQuirreL to generate the "create table" statements from a Teiid alias.  Then you run that on a real physical database. Next, suppose the data types that are chosen by the TeiidDialect differ from the actual ones presented by the physical source database.  That seems undesirable since the source database and the new physical database could then possibly have different table definitions even if they were from the same vendor (MySQL, PostgreSQL, etc.).   In that case, wouldn't you have more work to do if you then wanted to add the new physical database to the existing Teiid VDB ?

Rob

On Tue, Oct 4, 2011 at 1:22 PM, Matt Dahlman <mdahlman@jaspersoft.com> wrote:
Rob,

Yes, all DDL is probably all off the table. In general Teiid requires the use of its own design environment for creating things. Keep this example in mind: 
Teiid defines mappings between a table called "customer" and underlying customer datasources in MySQL, PostgreSQL, salesforce.com, web services, and flat files. There all all sorts of details like how the different sources are cached or accessed maintained in Teiid.

From the client perspective you make a JDBC connection to Teiid and can query "customers". The details of which data source actually has the data for your particular query are transparent to the user. (Different queries might hit different sources.) Selecting data (or updating or inserting) is fine. It's completely standard JDBC functionality.

But... the idea of creating a table or modifying a table definition in the JDBC connection isn't supported. That has to be done on the Teiid admin side.

Yes, this is clearly a big limitation around refactoring functionality in SQuirreL. In general a Teiid user won't be bothered by this, but it's certainly true.

In my case the reason I wanted to generate the create table statement is because I wanted to copy data from Teiid table to another database. I wanted to begin by generating the create table statement, but I would only run it on a different database.

-Matt


On 3 Oct 2011, at 16:45, Robert Manning wrote:

Matthew,

Since there is no dialect that ships with SQuirreL for this database, you are getting the generic dialect.  I looked for info about using telid with hibernate and I saw the following here:

http://docs.jboss.org/teiid/7.0.0/client-developers-guide/en-US/html/using_hibernate.html

"Many Hibernate use cases assume a data source has the ability (with proper user permissions) to process Data Definition Language (DDL) statements like CREATE TABLE and DROP TABLE as well as Data Manipulation Language (DML) statements like SELECT, UPDATE, INSERT and DELETE. Teiid can handle a broad range of DML, but does not support DDL."

Is it true that all DDL is off the table with this database?  If so, that will severely hamper the dialect's support for what SQuirreL can provide (mostly in refactoring).  In any case, we can include the dialect and extension to give you at least basic functionality from the SQL scripts plugin.

Rob



On Mon, Oct 3, 2011 at 5:31 PM, Matt Dahlman <mdahlman@jaspersoft.com> wrote:
I'm trying to use the function where I right-click on a table and generate the "Scripts -> Create Table Script". It fails with this error:
org.hibernate.MappingException: No Dialect mapping for JDBC type: 2

I see that date type "2" corresponds to BigDecimal. But I can't figure out if there is anyway for me to specify somewhere what the mapping for this data type should be.

Are the Hibernate dialects hard-coded into .jar files? Is adding this additional mapping outside the scope of a simple configuration change?

In case it matters, I'm using JBoss Enterprise Data Services (the former "Metamatrix" technology which is based on the Teiid project). This is the JDBC driver class:
org.teiid.jdbc.TeiidDriver
SQuirreL doesn't really have built-in support for this language. Everything else has worked well for me with the Teiid driver in SQuirreL. I speculate that the Hibernate dialect in use is just a default one, but I'm not sure how to confirm this.

Please let me know if anyone has ideas about this.

Regards,
Matthew Dahlman
Jaspersoft

P.S. As a practical matter, it would be useful for me if the script were generated with some sort of placeholder for the unrecognized data type. If I had a creation script with 48 columns correctly defined and 2 with "Undefined data type" then I could pretty easily fix it for this one-time creation script. Does it make sense to log this as an enhancement?



------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2dcopy1
_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users