| Author |
Message
|
| anurag.munjal |
 Posted: Tue Sep 10, 2013 7:04 pm Post subject: Writing SQL queries in ESQL in the java style - prepareStmt |
 |
|
Voyager
Joined: 08 Apr 2012
Posts: 97
|
I am using JDBC to interact with a Database. No ODBC connection is possible.
So, i have to use Java to write and execute queries.
Right now, am creating SQL Queries using ESQL in the following manner and then passing it to a a java compute node to execute it using jdbc.
| Code: |
DECLARE InsertSQL CHARACTER;
SET Environment.variables.Name = INPUTROOT.XMLNSC.Root_Tag.Name_Tag;
SET SET InsertSQL = 'insert into TableName(Name) values ('''||Environment.variables.Name||''')'; |
And then i pass this Insersql to my java compute node, and i establish a database connection and execute this query.
Now i have been told in my code review that the sql query should be made in java using prepare statement concept wich somewhat like this.
| Code: |
SET SET InsertSQL = 'insert into TableName(Name) values (?);
|
Now i want to understand is there any way to not to re write my entire ESQL in java to prepare a statement like this using esql?
FYI: the esql code is some 300 line code, i just dont want to re write eveything in java!  |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Tue Sep 10, 2013 9:56 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
What is the difference in the query string that you are passing to Java from the one that you have created?
If it is down to how you create the same string then unless one way is vastly more efficient than another I'd tell the code reviewer that, yes thanks for the suggestion but as the end result is the same so it does not reallt matter.
If you are doing a lot of message tree traversing to build your JDBC query then it may well be a lot easier in ESQL rather than Java.
minor nit-pick
Your code has
Probably just a cut/paste problem but you might like to edit your post to fix it.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Sep 11, 2013 3:04 am Post subject: Re: Writing SQL queries in ESQL in the java style - prepareS |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| anurag.munjal wrote: |
| I am using JDBC to interact with a Database. No ODBC connection is possible. |
Out of curiosity, how is it you can make a JDBC conx to db but not ODBC ?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Sep 11, 2013 3:47 am Post subject: Re: Writing SQL queries in ESQL in the java style - prepareS |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| anurag.munjal wrote: |
| I am using JDBC to interact with a Database. No ODBC connection is possible. |
Out of curiosity, how is it you can make a JDBC conx to db but not ODBC ? |
Presumably anurag.munjal is talking to a database that is not supported for ODBC. |
|
| Back to top |
|
|
| anurag.munjal |
| Posted: Thu Sep 12, 2013 11:27 pm Post subject: Pointersss |
|
|
Voyager
Joined: 08 Apr 2012
Posts: 97
|
Thanks for your suggestions team!
i just discussed these points with my Reviewer, and it turns out that:
1. Every Database supports ODBC.
2. JDBC has many advantaged over ODBC . for eg: JDBC is comparitively Quicker to perform db operations - hence - JDBC. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Sep 12, 2013 11:55 pm Post subject: Re: Pointersss |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| anurag.munjal wrote: |
Thanks for your suggestions team!
i just discussed these points with my Reviewer, and it turns out that:
1. Every Database supports ODBC.
2. JDBC has many advantaged over ODBC . for eg: JDBC is comparitively Quicker to perform db operations - hence - JDBC. |
1) whilst this may be true not every DB is accessible from broker over ODBC
2) Citation please.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Fri Sep 13, 2013 3:16 am Post subject: Re: Pointersss |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| anurag.munjal wrote: |
| JDBC has many advantaged over ODBC . for eg: JDBC is comparitively Quicker to perform db operations - hence - JDBC. |
N-O-T !! You're funny.  ODBC is written in machine code, and JDBC is an interpretive implementation. No possible way its 'faster'.
You say 'many advantages'. Name some ? I can think of none for a WMB developer. There may be some for people who write message driven beans. But thats a different platform altogether.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| darioo |
| Posted: Fri Sep 13, 2013 5:30 am Post subject: Re: Pointersss |
|
|
Novice
Joined: 19 Mar 2009
Posts: 15
|
| lancelotlinc wrote: |
N-O-T !! You're funny. ODBC is written in machine code, and JDBC is an interpretive implementation. No possible way its 'faster'. |
Not really. ODBC is most likely written in C and/or C++ (that will get compiled and translated to machine code eventually), while JDBC is NOT "an interpretive implementation". Java's JVM might have been interpreted in its early days, but today it's about on par as native code. Java code gets compiled to bytecode and JITted to highly efficient native code. Source? Google it, you will find many results.
I doubt JDBC drivers are written carelessly, without performance in mind. The biggest bottleneck will be network communication with the database.
I expect well written ODBC and JDBC drivers to perform almost the same.
Anyway, to add constructive feedback to OP: if possible, use ODBC. It's easier to use, cleaner and certainly not slower than JDBC. Interacting with databases using Message Broker's ESQL is a pleasure compared to coding raw JDBC in JavaCompute nodes, which is a pain in the...
Anyway, if JDBC has significant advantages, I'd like to hear them. |
|
| Back to top |
|
|
|
|
