aspose file tools*
The moose likes JDBC and the fly likes Stored procedure cannot be imported from java...? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored procedure cannot be imported from java...?" Watch "Stored procedure cannot be imported from java...?" New topic
Author

Stored procedure cannot be imported from java...?

prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
Hi Members,

Im having my 'create procedures' syntax in a file and im reading it line-by-line and storing it in a string and finally executing that string.....

however this is not accepting the 'Drop function if exists' syntax within the file.... it throws an sql exception error.

example: (which throws error)
***********************
DELIMITER |
DROP FUNCTION IF EXISTS GETREGRESSIONRESULTID|
CREATE FUNCTION GETREGRESSIONRESULTID (resultWithCycle VARCHAR(255), scheduleInstance INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE resultWithoutCycle VARCHAR(255);
SET resultWithoutCycle = CONCAT(SUBSTRING(resultWithCycle,
LOCATE(CONCAT(CAST(scheduleInstance AS CHAR),'~'), resultWithCycle) + 2), '::');

SET resultWithoutCycle = SUBSTRING(resultWithoutCycle, 1, LOCATE('::', resultWithoutCycle) - 1);
RETURN resultWithoutCycle;

END|


example(which works fine)
*******************

CREATE FUNCTION GETREGRESSIONRESULTID (resultWithCycle VARCHAR(255), scheduleInstance INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE resultWithoutCycle VARCHAR(255);
SET resultWithoutCycle = CONCAT(SUBSTRING(resultWithCycle,
LOCATE(CONCAT(CAST(scheduleInstance AS CHAR),'~'), resultWithCycle) + 2), '::');

SET resultWithoutCycle = SUBSTRING(resultWithoutCycle, 1, LOCATE('::', resultWithoutCycle) - 1);
RETURN resultWithoutCycle;

END

Please let me know where im making mistake....

even 'create or replace procedure' is not working..

Regards,
Prabhu
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42608
    
  65
What's the error? Are you executing the "drop function" by itself, or does the string also contains the "delimiter" and "create function" commands? If the latter, have you tried executing the commands separately?


Ping & DNS - my free Android networking tools app
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
@ulf

I can execute the 'Create procedure' syntax alone that i have mentioned in my last post.
But when i try to execute the 'Drop function' along with 'Delimiter'
it throws an error....

Error:

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TR' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42608
    
  65
If it's not possible to break up the command into several separate ones, then I'd use the command-line client for your database to craft a query that you can pass in all in one go. That's easier than running the Java code until you get the syntax right.
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
How to use the command line client from java.....
to run those script...
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42608
    
  65
Google seems to know a lot about that: http://www.google.com/search?q=mysql+command+line+client
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
@ulf,

No ulf, i can do by using command line client, but the requirement is to do through jdbc. I have to execute through a java application.

Please let me know what is wrong in my syntax to execute the stored procedures with 'Drop procedure' syntax.

Regards,
Prabhu.
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
however this logic works fine for 'tables'... Drop table if exists and create table..... But which is going wrong in the syntax im not sure... in case of stored procedures....
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42608
    
  65
You misunderstood what I suggested. I meant that using the command line client to debug the SQL command is easier than to use a file and a Java program.
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
yes i understood ulf, however i have to use file and java program to execute mysql script.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42608
    
  65
What do you mean by "however"? How those commands are executed at runtime has no bearing on how you debug them.
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
http://www.daniweb.com/forums/thread153923.html

This is the link from which i got idea to execute my database sql script. But in that i have some stored procedures and functions, but with the logic specified in the above link, i could not execute the routines. Hope you understand me.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42608
    
  65
Yes, I understand what you want to do. I'm trying to help you debug it because you said it's not working.
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
Please have a look at my post number # 172570, in this i have mentioned two examples. In that one example is working and the other is not working.... im facing some exception while im trying to execute.....

I need to execute a 'create procedure' syntax with 'drop procedure if exists' syntax....

I got solution for this issue... ulf......,

Now i tried to execute 'drop procedure' syntax and then i tried to execute 'create procedure' syntax. It is working fine now..
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored procedure cannot be imported from java...?