Teiid and Rails

[johnp]

All,

I am trying to use Rails to interact with a VDB on Teiid but am running into a few problems ...

• I have Teiid running successfully with a VDB deployed which I can connect to using SQuirrel
• I also have Torquebox running (on AS7) with a JNDI datasource for my Teiid database (connection works fine)
• I also have Rails applications running within TorqueBox and connecting (via JNDI) to several databases (DB2, MySQL)

So far so good ...

The problem comes when I am trying to run a Rails app talking to a Teiid VDB and I get the following error:

ActiveRecord::JDBCError (The driver encountered an unknown error: unable to choose type for decimal from:

["boolean", "byte", "long", "char", "bigdecimal", "biginteger", "integer", "short", "float", "double", "string", "xml", "date", "time", "timestamp", "object", "blob", "clob"]):

I assume that the standard activerecord-jdbc-adapter is having issues with the Teiid datasource/driver but was wondering if anyone had come across something similar. It would be great to be able to deploy Rails apps to JBoss and interact with Teiid but it looks like some work may be required to provide an adapter for ActiveRecord.

Does anyone have any suggestions or has had any similar issues

Thanks,

JP

[shawkins]

It must be going by the type names and not the type codes.  Our bigdecimal is roughly equivalent to the standard decimal type.  Can you provide a type mapping to Rails?  We could also add our built-in standard type aliases to our driver for varchar, tinyint, smallint, bigint, real, and decimal.

Steve

[johnp]

Steve,

Thanks for the reply - I will need to do some more investigation before I can give you an answer. I am using the standard activerecord-jdbc-adapter at the moment and you can certainly create another adapter which is specific to Teiid (there are already flavours for MySQL, Postgresql, h2 etc). Alternatively, if you could use the base adapter and the teiid driver jar file did the work that would also be nice.

I did check the TorqueBox mailing lists but couldn't find anything there.

Thanks again,

John

[johnp]

Steve,

Hopefully a bit of progress ... I monkey patched the typeconverter.rb in the jdbc adapter and added a type of bigdecimal for the decimal conversion

        :decimal     => [ lambda {|r| Jdbc::Types::DECIMAL == r['data_type'].to_i},

                          lambda {|r| r['type_name'] =~ /^decimal$/i},

+                        lambda {|r| r['type_name'] =~ /^bigdecimal$/i},

                          lambda {|r| r['type_name'] =~ /^numeric$/i},

                          lambda {|r| r['type_name'] =~ /^number$/i},

                          lambda {|r| r['type_name'] =~ /^real$/i},

                          lambda {|r| r['precision'] == '38'},

                          lambda {|r| r['data_type'].to_i == Jdbc::Types::DECIMAL}],

I also had to subsequently add an arel2_visitor method for JdbcAdapter and this appears to have made the connection to the VDB although I now get an ActiveRecord::JDBCError: Table xxx does not exist when doing a find.

I assume that this is something to do with a qualified table name in my VDB so hopefully I am a bit closer.

Cheers

[johnp]

Ok I have managed to progress this further by changing the jdbc_columns method in the jdbc adapter so that it gets the correct column details from Teiid - the default jdbc adapter method returns null and therefore you get the "table does not exist" error

def jdbc_columns(table_name, name = nil)

    sql = "select * from sys.columns where tablename = '#{table_name.to_s}'"

    execute(sql, 'col_split_' + table_name.to_s).map do |field|

        ::ActiveRecord::ConnectionAdapters::JdbcColumn.new(field["Name"], field["DefaultValue"], field["DataType"], field["NullType"] == "Nullable")

    end

end

After doing this I am about to access vdb tables from the raw connection (tbles = ActiveRecord::Base.connection.tables) or using a normal Rails model (rc = Provider.count).

Based on these early problems I think that there will probably be some other issues with using the standard jdbc adapter and an activerecord-teiid-adapter would be nice to have - maybe the Torquebox gang could write one

I am now able to deploy my Rails app to TorqueBox and everything seems to be ok except that I am using the Kaminari gem for pagination and the SQL that is generated causes errors ...

SELECT * FROM providers  ORDER BY providers.id desc LIMIT 30 OFFSET 0

I believe it is because OFFSET is not supported and should be replaced with a comma...

SELECT * FROM providers  ORDER BY providers.id desc LIMIT 30, 0

This returns no rows but if the values are reversed then it's all ok

SELECT * FROM providers  ORDER BY providers.id desc LIMIT 0, 30

Any help or suggestions welcomed - do you know if the Torquebox people monitor this - there is no user forum on the TB site for this type of discussion.

Cheers,

JP

[shawkins]

John,

That looks like good progress.  Yes, we can certianly bring these findings to the attention of the Tourquebox people and at least get something documented on our side.  The Teiid limit clause syntax is the same as MySQL, which is "LIMIT [offset,] limit" where the limit is logically applied after the offset.  We also support the ansi standard FETCH FIRST syntax as an alternative.  Are there options for changing the SQL generated by Kaminari?

Based upon your previous post it does look like adding the standard type name to our typeinfo would eliminate the need for the typeconverter.rb patch as there wouldn't be confusion between the biginteger and bigdecimal types.  I'll log a JIRA for that.

Steve

[shawkins]

John,

https://issues.jboss.org/browse/TEIID-1908 added the standard type names to getTypeInfo.  This fix is in 7.7 Beta1 if you have a chance to work with the Beta release.  Do you have anything else fleshed out for a Teiid JDBC adapter / Kaminari dialect that could be captured in either Teiid or TorqueBox?

Steve