RE: How to call external JAVA program from SQL SELECT statement? - Shankar Unni - com.mysql.lists.java

6 messages in com.mysql.lists.javaRE: How to call external JAVA program...

From	Sent On	Attachments
Stepan RYBAR	24 Jun 2003 01:01
Nick Stuart	24 Jun 2003 05:31
Matthew Pocock	24 Jun 2003 08:25
Mark Matthews	24 Jun 2003 08:40
Shankar Unni	24 Jun 2003 09:01
Stepan RYBAR	24 Jun 2003 22:45

Subject:	RE: How to call external JAVA program from SQL SELECT statement?
From:	Shankar Unni (shan...@cotagesoft.com)
Date:	06/24/2003 09:01:10 AM
List:	com.mysql.lists.java

You're coming from Oracle, aren't you? Oracle has an embedded JVM, and allows you to declare "stored procedures" in Java (a valuable feature, by the way! I'm hoping this gets into the MySQL feature list soon), and call them from SQL.

Can't do that in MySQL. Or rather, you could, but it involves a lot of pain. See the MySQL documentation for how to create your own builtin functions - you could write a set of JNI stubs that'll load a JVM that'll load your libraries, and wrap MySQL C wrappers around those stubs (making them "builtin" functions), and invoke them from SQL that way. But this is all very painful, and you have to be very very careful about the stability of the mysqld process when you do this.

The safest way to go down this path would be to have a separate process to interface to the Java process, and communicate to it via IPC (which is what Oracle does). So this is all a lot of pain.

Do what Mark suggested: load the Java code into your app, call the function there, and pass the result to the SQL query.

-----Original Message----- From: Stepan RYBAR [mailto:xryb...@seznam.cz] Sent: Tuesday, June 24, 2003 1:02 AM To: ja...@lists.mysql.com Subject: How to call external JAVA program from SQL SELECT statement?

Hallo!

Can I ask You, if it is possible to call Java program from SQL SELECT statement? Something like:

SELECT id, name FROM parcels WHERE id IN (Intersects('parcels', 'POLYGON((0 0,1 0,1 1,1 0))'))

Where Intersects is my own java program (Intersects.class) with function declaration e.g.:

String tokenisedIds Intersects(String tableName, String geometryInWKT)

If it is possible now, please send me any how-to with example, or at least any documentation. Or will it be possible in any near future version of MySQL? Also the same using C++ is acceptable.

Thank You

Stepan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Stepan RYBAR e-mail: xryb...@seznam.cz mobil: +420607636256 Modra 6, Stodulky 1979, Praha, 155 00 IC: 71078592 AC: 22123199

----------------------------------------