Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL validation!

 
Akhilesh Trivedi
Ranch Hand
Posts: 1599
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a requirement wherein i wish to allow my customer to specify sql query by himself (assuming the fact that customer is say half sql-sensitive).

He should be able to save a query and later run the same query if needed. I wish to validate the query without hitting db. Is there any sql-syntax-checker api available?
[ November 11, 2008: Message edited by: Akhilesh Trivedi ]
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any sql validator will atmost validate the syntax, what about if a user puts a name of table which at all does not exists.

I would rely on database to validate sql. for instance if user puts a query select column1,cloumn2 from mytable

I will change this query to
select column1,cloumn2 from mytable where 1=2 .

This way I will ensure that my query does not return any row after execution, and query will be validated.



I have found a similar thread on javaranch itself, I guess you might have checked this
http://www.coderanch.com/t/324923/java/java/Programatically-validate-SQL-Syntax

Shailesh
 
Akhilesh Trivedi
Ranch Hand
Posts: 1599
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks buddy.


I could rely on db as you said and come back with error messages at caught-exceptions if any, but i think hints would still be not sufficient to indicate user what is/where exactly things are wrong. Thus I am opting for a static-syntax-validator.

I did not check out the "EasySQL Checker " for Jeanne had mentioned two things about it, first it was shareware and second "it may help if you don't actually need the code."

However SQLJ seems of interest. I was not aware it was from oracle. From few places, i was directed here (doesn't work) for the sqlj-jars.

Let me work around at the options. Thanks.
[ November 12, 2008: Message edited by: Akhilesh Trivedi ]
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
on the other thought if you are not relying on database, so whenever provide this feature to your users, restrict user as much as possible to type anything.

Ask user to select tables/columns from list/dropdowns, provide user to list of keywords to choose.

I recall a mathmatical formula builder was written by one of my friend in same way.

Shailesh
 
Akhilesh Trivedi
Ranch Hand
Posts: 1599
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have opened further more options. Ultimately, we only need the right query before it hits db, either we have a wrong one in say text-box and parse it right or have a right one framed through the options provided in gui.
We will have to check with customer if he will be happy with, one power-pack text-box or easy-clicking list-boxes, damn! i probably know what would be his answer...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic