Hi,
UDFs are great and we use it often.
--
-- THIS ONE CAN'T BE USED - I dont know why ?
--
create alias LINKED_SCRIPT as ' void doextscript( String
driverClassName, String url, String username, String passwd, String
scriptFileName, String charsetName, boolean continueOnError) throws
SQLException {
try {
java.sql.Driver aDrv = (java.sql.Driver)
Class.forName(driverClassName).newInstance();
java.sql.DriverManager.registerDriver(aDrv);
} catch (Exception e) {;}
org.h2.tools.RunScript.execute(url, username, passwd, scriptFileName,
charsetName, continueOnError) ;
}';
--
-- THIS ONE WORK FINE and we use it some times , but has the same
limitations that linked tables.
--
create alias LINKED_QUERY as 'ResultSet doextquery( Connection con,
String driverClassName, String url, String username, String passwd,
String query) throws SQLException {
try {
java.sql.Driver aDrv = (java.sql.Driver)
Class.forName(driverClassName).newInstance();
java.sql.DriverManager.registerDriver(aDrv);
} catch (Exception e) {;}
return java.sql.DriverManager.getConnection(url, username,
passwd).createStatement().executeQuery(query);
}';
Now to use it in an SELECT this look like:
select a.* from LINKED_QUERY(
org.postgresql.Driver','jdbc:postgresql://127.0.0.1:5432/mydb','sa','********',
'select * from mySchema.someTable' ) as a;
you must agree that isn't a nice syntax ;-)
and this is very redundant with LINKED TABLE's connections management. I
think their external connections pool must be shared by all this
"LINKED" artifacts.
When we know what need to be executed at design time, then we can write
a compiled java UDFs jar that use resources of h2 and applications and
all goes right.
But , many time we DON'T know in advance what need to be executed as
"CUSTOM SCRIPTS" that the us or final user can write in SQL as
application's customer procedures.
In data integration applications , new data inputs are ofted handled as
custom procedures (scripts) executed from an application that has an
embedded H2 database.
For those cases , this UDFs are tolerable but not comfortable or efficient.
I insist that almost anything is just inside H2: drivers and external
connections handling.
We only need some expressive and clean way to access that extenal /
linked connections for use in a more general or less restrictive fashion.
Regards,
Dario
El 07/02/10 14:04, Thomas Mueller escribió:
> You can also use JDBC inside H2, using customer functions. Example:
>
> create alias query as 'ResultSet query(Connection conn, String sql)
> throws SQLException { return conn.createStatement().executeQuery(sql);
> }';
> call query('select * from dual');
> drop alias query;
>
> If needed, this can be extended to access external databases.
>
> Regards,
> Thomas
>
>
>
>
> On Thu, Feb 4, 2010 at 4:27 PM, Dario Fassi <[email protected]> wrote:
>
>> El 03/02/10 20:55, Ryan How escribió:
>>
>>> Do you mean like Access, where you can access heterogeneous data
>>> sources like they are one database, but also have the ability to
>>> "pass-through" to the originating data source?
>>>
>>> Ryan
>>>
>> We don't use Access, but yes, that's the idea.
>> In MS world you have some interesting artifacts to support data
>> exchange, integration and ETL applications like DTSs , but it's very
>> common that this type of apps use as intermediary and access db and VB.
>>
>> Because our main target are *nix+java server environments and
>> heterogeneous db vendor + legacy , H2 fill this role very well as dbm
>> and as ETL tool.
>> An LINK DATABASE feature and LINKED_QUERY() functions it's all we need
>> to remove some uncomfortable limitations of Linked tables.
>>
>> Dario.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
