> > > Please CMIIW, but i thought we only need this when we're doing stuff > like > > "SELECT ?, ? from DUAL". > > Are you saying that we need this too for "normal" binding like "SELECT * > > from myTable where id = ?" ? > What's the definition of "normal"? Why is it more "normal" for a > database to infer / coerce types in one case, but not in another?
I'm sure you know more about these issues than i do, so i apologize if i'm going through some basic definitions first, just so we're on the same page. ... "Normal" binding (as far as i understand it) is when you use a "placeholder" in place of literal values; so you can replace the values later without having to construct the whole statement again. JDBC parses and caches these so the next invocation will be faster. Which brings us to the following use cases: A. SELECT * FROM myTable where (id = ?) and (sex = ?) B. INSERT INTO myTable (id, sex, name) values (?, ?, ?) C. DELETE FROM myTable where (id = ?) I believe none of these cases requires any casting. "Abnormal" (for contrast purposes, not actual terminology) binding is binding that should never be allowed in the first place. Example: A. SELECT * FROM ? B. INSERT INTO ? C. DELETE FROM ? I'm writing this from memory so i don't have any citations for you, but i did a little search and this page came up: http://use-the-index-luke.com/sql/where-clause/bind-parameters I like the following quote, so i'm gonna use this as one of the tenets: > Bind parameters cannot change the structure of an SQL statement. That > means you cannot use bind parameters for table or column names. > Now, you may argue that you'll never write SQL that is a corner case > and you wouldn't need bind value casting as you hadn't needed it so > far. That's probably true if you're in full control of the rendered > SQL. Yet, stuff like "SELECT ?, ? from DUAL" is not so uncommon. SELECT ?, ? from DUAL is an interesting case, and at first i was a bit perplexed on why we would EVER need that. Both the parameters are supposed to be literals in this case, right? (Since jdbc wouldn't allow parameterized column name anyway) So now we have the query -> we supply the parameters -> execute the query -> and get back exactly the values we put in? Why don't just use the value in the first place... ? With > multi-record inserts (simulated by "INSERT INTO .. SELECT .. UNION ALL > SELECT ..") or with MERGE statements, it can be quite frequent. Other > corner cases appear when using functions, operators, etc. > I apologize again, this must be my unfamiliarity with jOOQ that is confusing me. Can you show me the actual statements and how it's used? Here's an example MERGE statement taken from oracle docs http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm > MERGE INTO bonuses D > USING (SELECT employee_id, salary, department_id FROM employees > WHERE department_id = 80) S > ON (D.employee_id = S.employee_id) > WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 > DELETE WHERE (S.salary > 8000) > WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) > VALUES (S.employee_id, S.salary*.01) > WHERE (S.salary <= 8000); With parameter bindings, the statement becomes > MERGE INTO bonuses D > USING (SELECT employee_id, salary, department_id FROM employees > WHERE department_id = ?) S > ON (D.employee_id = S.employee_id) > WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.? > DELETE WHERE (S.salary > ?) > WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) > VALUES (S.employee_id, S.salary*.?) > WHERE (S.salary <= ?); I'm gonna assume that none of these would require casting either... so yeah, i don't get it :D. Regards, ts.
