current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have some SQL strings with parameters from a log file such as

select a,b,c from db_table where d=? and e=?
-> params: [10,20]

I can not change the log format such that the question marks are replaced by the respective parameters (at least not as far as I know, I'm using Toplink as an OR mapper). So I would like to have a little utility that I can give the query, the pattern and the parameters and it gives me the result (GUI or no doesn't matter). Something like this:

> insertUtil 'select a,b,c from db_table where d=? and e=?' '?' '10,20'
select a,b,c from db_table where d='10' and e='20'

It doesn't have to be perfect (e.g. date conversions etc.), it should just insert the strings. No need to prevent SQL injection, it's just a tool for debugging the potentially long queries.

I know it's trivial to program such a thing but I'm surprised I could not find any existing program like that.

To make it clear I'm not looking for a way to make prepared statements with JDBC, I'm trying to get that complete query including parameters so I can execute it in another program, for example to generate a query plan for it.

share|improve this question
add comment

2 Answers

up vote 1 down vote

I know it's trivial to program such a thing

"Trivial" is an understatement here: there is nothing to program here at all, because everything has been programmed for you by designers of JDBC.

Question mark is a marker for SQL parameters in JDBC, so all you need to do is preparing a statement with the SQL string, and setting parameters in the order that they are given:

String sqlString = "select a,b,c from db_table where d=? and e=?";
String[] parameters = new String[] {"10", "20"};
PreparedStatement ps = comn.prepareStatement(sqlString);
for (int i = 0 ; i != parameters.length ; i++) {
    ps.setString(i+1, parameters[i]);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    ... // Get back your a, b, and c here
}

JDBC driver will take care of the rest: it would find parameter markers ?, and replace them by the values that you provide before passing the query to RDBMS for execution.

EDIT : (in response to an edit of the question)

I'm not looking for a way to make prepared statements with JDBC, I'm trying to get that complete query including parameters so I can execute it in another program, for example to generate a query plan for it.

This may seem trivial, but it is not: although it seems that you could get this done by a simple textual substitution, that may not work unless you pay a lot of attention to the context around your question marks. For example, you need to skip substitutions of question marks surrounded by quotation marks, or question marks that are commented out. You can get an "almost good" utility coded up quickly, but covering all corner cases is surprisingly tricky. If you wish to do it right, consider using a SQL Parser utility.

share|improve this answer
    
Sorry, I updated my question to clarify that I'm not looking for a way to create prepared statements with JDBC –  Shirky Feb 15 at 16:34
    
Thanks for your edit! I know it's hard to do properly but that's why I don't want to do it myself. I somehow was under the impression that someone more clever than me would have created a program that can do this. It seems to me that there must be a lot of people who would like to execute a query from a OR mapper log file with an external DB tool, e.g. to see its query plan. –  Shirky Feb 15 at 17:24
    
@Shirky Good chances are that many people wanted to do this, but didn't find it worth their time to write a utility to automate it. Looking at a query plan is usually a do-it-once thing, not an ongoing exercise, so it's probably faster to replace parameters manually than to write a utility for doing it automatically. –  dasblinkenlight Feb 15 at 17:40
    
Thanks for your effort but I think I'm gonna mark my own answer as the solution eventually unless sth. better comes along. Do you see anything gravely wrong with it (except for the obvious security problem)? –  Shirky Feb 15 at 20:23
add comment
up vote 0 down vote accepted

Short bash script (not secure (see below))

#!/bin/bash

ORIG_STR="$1"
PATTERN="$2"
ARGS="$3"

paste -d '' <(echo "$ORIG_STR" | sed "s/[$PATTERN]/'\n'/g") <(echo "$ARGS" | sed "s/,/\n/g") | tr -d '\n' ;echo

It uses command substitution. There are more beautiful things on earth and it's not secure with queries you don't trust (SQL injection, weird patters like ']').

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.