Sql Scripts

[mikedaruke]

Any way to run a sql script from JAVA.

I use this right now:

stmnt = conn.prepareStatement("select * from table where field ='abc'");

But I have a big script that is like a page long. How do I enter it? Also I have like 4 values that I am getting from a from that I need to insert into it.

Any help appreciated.

 

[sedj]

For your *big script* I would write a stored procedure, and then call it with a CallableStatement - this is by far the cleanest design.

For dynmaic values :

String value = "abc";
stmnt = conn.prepareStatement("select * from table where field = ?");
stmnt.setString(1, value);

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[mikedaruke]

It is the cleanest.

but I cannot add Stored procedures to the database though. Its all red tape here.

So usually I run the sql script in query analyzer which is fine. I just want to create an application for users that is going to popup a form asking values, get them insert them in the script, run it, then return a result.

So can you add multiple lines to a stmnt?

thanks

 

[sedj]

I think I would ask the DBA's whether they would rather create a stored procedure which they can check over and manage, or whether they would prefer you running arbitrary untuned and unchecked SQL ! I reckon the red tape might losen up then ...

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[sedj]

BTW, ASFAIK, JDBC has no method in the API specifically for executing multi-line statements. Your JDBC driver may do however - I don't know.

Another way to do it is to read the file in line by line, executing each statement as you go.

But I would really fight to go down the stored procedure method - its just seriously dangerous allowing users to in effect execute arbitrary SQL via a programme.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk