• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

preparedStatement

 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have table Customer (id, name), Address(id, st_add, city, state), Order(randomKey, id, name, st_add, city, state..)

The logic is --

1. I want to do something like

SELECT c.id, c.name, a.st_add, a.city, a.state
FROM Customer c, Address a
INTO
Order
WHERE c.id = a.id

But that's not exactly. I also need to create a run time randm number (by java code) and assign this random key to each row. Since this key is not from any table, how to I modify the abve SQL into any preparedStatement and input that random number as a "?". Can I do that and how ?

Thanks.
 
Mohamed Sanaulla
Saloon Keeper
Posts: 3165
34
Google App Engine Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use PreparedStatement for this. Create a SQL query with "?" for the random number. And you can use


You can have a look at the API for more information.

Also there are certain advantages of using PreparedStatement over the usual Statement. You can read about that as well.
 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can I make my PreparedStatement like this ---



You see I put a "?" in SELECT clause but it doesn't look rigth to me. How should I modify this ?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most databases have some function to provide random numbers. You should be able to specify that function as part of your SELECT statement, eg. in Oracle it could be the dbms_random.value() function, as in
.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using prepared statements for projection values isn't really correct use of them. For example, if you did "SELECT ? FROM", the prepared statement might perform a data conversion on a column name. While some drivers may allow this, strictly speaking its incorrect as prepared statements should only be used on binding variables such as "SELECT * FROM Widgets WHERE AGE = ?".

Unfortunately, if you want things like ORDER BY or changing projection clauses, you should create the SQL yourself. In fact, you should never expose the external fields to a client application. For example, if the user enters a value it should be converted to the proper database column name and never sent directly to the database in a query.
 
Mohamed Sanaulla
Saloon Keeper
Posts: 3165
34
Google App Engine Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Raj Ohadi wrote:Can I make my PreparedStatement like this ---



You see I put a "?" in SELECT clause but it doesn't look rigth to me. How should I modify this ?


Why would you want to select the Random value from the Table? You should be using the Value substitution in the INSERT query. I dont know why you are using the random number in the SELECT there.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott,

in Oracle it is legal to use binds wherever literals can be used, definitely including the SELECT clause (I've done it lots of times before) and even ORDER BY or GROUP BY clauses -- I believe technically this should be possible, but it is meaningless, as the bind is constant for single execution of the statement and ordering or grouping by a constant does not affect the results. The only exception from this rule I'm aware of is using "positional" ORDER BY clause, as in ORDER BY 1,3,2. These "values" are actually references to the columns in the SELECT clause, therefore are hard-coded into the statement execution plan, so it is not feasible having them bound.

I'd consider it strongly short-sighted from the JDBC designers if the driver specification didn't take these possibilities into account, and I don't believe it's true. I've certainly used binds in the SELECT clause many times, most often in INSERT INTO ... SELECT ... statements, somewhat like Raj is trying to do.

May be there are databases that have problems using binds in the SELECT clause, but in general, if the database allows it, then probably the driver is capable of doing it too. After all, the driver's work is just to send the binds to the database, which takes care of interpreting the query.
 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe I did not make it clearly. My scenario is --

I have table A and table B, I need to join them and insert some of their fields into Table C. Plus during the insertion into Table C, I need to populate one or two columns in Table C with some code generated value (so it may not be random number, it may be some business logic related value created by java code). So I want to do this whole thing using a prepared statement because the code generated value need to be passed into Table C's column. That's why I asked if I can do something like

SELECT ? a.name, ...


Where "?" will have value passed by code.
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If your desired SQL is something like "Select 42 as random, name, address from Customer" then perhaps "Select ? as random, name, address from Customer" would work with your database. Do you not have a way of trying this out?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic