Hi,
El 13/02/10 12:34, Thomas Mueller escribió:
>> UDFs
>>
> I guess you mean user defined functions?
>
Yes - sorry , I'm contamined with IBM terminology from too many years.
>> -- THIS ONE CAN'T BE USED - I dont know why ?
>>
> I don't understand, why not? Does it throw an exception?
>
Me neither and what's wrong - this is the error :.
Error: Syntax error in SQL statement " 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) ;
}"; SQL statement:
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) ;
}' [42000-128]
*SQLState: 42000
ErrorCode: 42000*
Error occured in:
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) ;
}'
>> 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.
>>
> You could create a method to map a 'data source' to a connection:
>
> LINKED_DEFINE_DATASOURCE(dataSource, driver, url, user, password);
> LINKED_QUERY(dataSource, query);
>
I try something like this with Connection ,
but I can't figure out what place-holder to use for Connection or
DataSource
returned for the first UDF call until until can be used as a parameter
from the second UDF call.
If the first UDF don't create a persistent database object for their
result Connection/DataSource , to use it we must to do:
call LINKED_QUERY( LINKED_DEFINE_DATASOURCE( dataSource, driver, url, user,
password) , query);
> Or you could create a function that creates another function:
>
> LINK(dataSource, driver, url, user, password);
>
> This would then create the 'real' <dataSource>_QUERY alias.
>
>
I had already thought about this, but not tried it yet.
What would be the right way to look up the functions created in the db ??
I try to use the alias bellow , but DatabaseMetaData.getFuntions()
is in JDK 1.6
create alias SHOW_FUNCTIONS as 'java.sql.ResultSet
showfunctions(java.sql.Connection con , String schemaPattern, String
functionNamePattern) throws SQLException {
java.sql.DatabaseMetaData dbmd = con.getMetaData();
return dbmd.getFunctions(null, schemaPattern,
functionNamePattern);
}';
>> 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.
>>
> I don't understand, how is that related to this problem?
>
It's related because in this use case we are limited to scripting and
can't define alias with compiled code in an extension jar.
> As I wrote, it doesn't have high priority for me. I think it doesn't
> make sense to add functionality into the database engine itself if the
> same functionality can "live" outside. Unless many people need it.
>
I agree that db can't grow in all directions in an un-ordered way and
that core must remain small and rock solid.
But I think too, that there are features, like linked tables, that are
incomplete and with a very limited usability for their for typical use
case.
- just an idea -
It maybe be better remove it from the core of h2 and pass it to an
extension module (h2-linked.jar) , as the "contrib" of other databases.
Then only who need this functionality load that jar, and this features
can grow without compromise the core.
I know this is not so easy , and that requires to layout some plug-in or
extension method in the core ;
but I can't imagine some other way to maintain the core small and solid
without restrict the functionality grow-up.
regards,
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.
