views:

952

answers:

4

which method do you prefer for creating dynamic sql queries? formating or streaming? Is it just preference or there any reason one is better than other?Or any special library you use to it.

EDIT: Please answer in case of c++.

+4  A: 

In Java you should use a PreparedStatement.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Table WHERE ID = ?");
statement.setInt(1, 17);
ResultSet resultSet = statement.executeQuery();
Bombe
thanks, how to prevent in c++?
yesraaj
No idea, C++ is not my cup of tea. :)
Bombe
+5  A: 

Always use "prepare" there will be an equivalent to prepareStatement but the exact function name will depend on your database and driver combination.

The advantages of a prepared statement over an execute(String) are many:-

The statement is parsed and an access plan determind only once when the "prepare" statement is executed. Depending on how many times you run the statement this can result in much better performance.

You dont need to worry about special characters in string data when you pass it through setString(). In an execute(String) any single quotes or semicolons in the data will result in a parse error.

Worse this is how "sql injection" attacks work. If a string something like "x' from cust_table; delete from cust_table; select " is entered as data it might well result in the delete statement being parsed and executed.

Handling of numbers is much more efficient. A setInt call take an integer value as is the for the equvalent SQL string you must convert to characters then the DBMS has to convert it back to an integer.

Readability. You code a single SQL statement with a few question marks where the variables go which is relatively easy to read, as opposed to mentally parsing and analysing a series of string concatinations will extra noise for escaped quotes etc.

There are however a couple of cases where execute(String) is actually better.

Where your keys are very unevenly distributed. E.G. If 95% of your customers live in the USA and you want to list the 4% who live in Canada then "where country = ?" would normally result in a table space scan while with "where country = 'CA'" you have some chance of using an index.

The other case is where the user can enter or omit several search criteria. Its much better to build an SQL string for the criteria you are given than construct a complex query which copes with all possible permutaions of the input criteria.

James Anderson
A: 

When using prepared statements are not possible, I find using C++ streams is the best way to write the query:

std::ostringstream sql;
sql << "exec loadStuff(" << param1 << ", " << param2 << ")";

Not having to worry about the types of the parameters and the length of the string is great!

small_duck
I hesitate to down-vote, but I am concerned about the threat of SQL injection attacks in that technique. :(
Adam Paynter
Fair enough, but when using a library that does not have prepared statements, in an internal application dealing mostly with numbers, I have found this pattern useful and efficient to call stored procedures. A world-facing web-app would be another thing.
small_duck
Sorry, small_duck. I didn't catch the "When using prepared statements are not possible" bit. I was going to revoke my down-vote, but it claims that my down-vote is too old to reverse. However, if you edit your answer, I can revoke it. Perhaps you could bold the "are not possible" bit to help people that had the same defect as me. :)
Adam Paynter
Made it bold :)
small_duck
I reversed the down-vote. Now I can't up-vote it because it's too old! lol :)
Adam Paynter
A: 

There is some thing called SOCI - The C++ Database Access Library for C++

yesraaj