Solved

How do you write a prepared statement?

Posted on 2002-07-04
16
318 Views
Last Modified: 2012-06-27
Can you show me how do you make a prepared statement out of this in ASP? Just curious to know if it is even remotely possible.



set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=PS01;UID=DBlogon;PWD=DBpasswd"

sql = "select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_num in (1,2,3,4,5) "
set rs = objConn.execute(sql)

'Parsing stuff here
objConn.close
set rs = nothing
set objConn = nothing
0
Comment
Question by:yes4me
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7130783
My understanding is that prepared statements come from having the SQL called as a stored procedure and that the preparedness comes from the database side of things. I don't believe that you can have a SQL statement prepared when calling it from the body of ASP code.

Fritz the Blank
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7130797
Take a look at the Command object's Prepared property.  Of course, whether you can use this property or not depends on whether the provider supports it.  If it does not, it will report an error when this property is set to True, or simply ignore the request and set the property to False.

Anthony
0
 

Author Comment

by:yes4me
ID: 7139136
Then how would you then convert my code to some prepared statement?



i.e. In Java, you could write it the following way:

String query = "select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = ?";
java.sql.PreparedStatement stmt = con.prepareStatement(query);
stmt.setInt(1, 8);
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7139213
you might try the following:

dim objCMD, strSQL

strSQL = "select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_num in (1,2,3,4,5) "

set objCMD = server.createobject("ADODB.Command")
objCMD.Prepared = true
objCMD.CommandText = strSQL
objCMD.ActiveConnection = objConnection
objCMD.Execute


Fritz the Blank
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7139214
Please maintain your open questions:
Make it so that the user cannot select the text for NS4.79 Date: 04/22/2002 05:22PM PST
http://www.experts-exchange.com/javascript/Q_20292461.html

Parsing XML - method3: SAX Date: 06/17/2002 01:26PM PST
http://www.experts-exchange.com/asp/Q_20312721.html
This last can be deleted as no solutions were offered.

Thanks,
Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7139353
Fritz,

I think you meant:

dim objCMD, strSQL
Dim rs         '<-- add this

strSQL = "select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_num in (1,2,3,4,5) "

set objCMD = server.createobject("ADODB.Command")
objCMD.Prepared = true
objCMD.CommandText = strSQL
Set objCMD.ActiveConnection = objConnection  'change this
objCMD.CommandType = 1    'adCmdText  'Add this
Set rs = objCMD.Execute     'But most importantly, change this.

Anthony
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7140565
Anthony,

You are, of course, absolutely right. If Yes4me wants to do anything with the data, S/he will need to dimension a recordset object and then set it to the results of the command execution.

Fritz the Blank
0
 

Author Comment

by:yes4me
ID: 7142233
Anthony, I don't plan to remove these posts for the next 2-3 months. I just too busy right now to even look answers right now, but as often as I can.

I am also curious what kind of answer someone may end up with all my questions.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:yes4me
ID: 7142263
Oh oh... after reading all your posts, I think I may have confused you all (Somehow I am good at doing so. Doh!).



Let's take my previous example of Java:

// connect to database
String query = "select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = ?";
java.sql.PreparedStatement stmt = con.prepareStatement(query);
stmt.setInt(1, 8);
// parsing the result



Oh... ok to make it clearer here is the almost full code:
import java.sql.*;
...
private static java.sql.Connection con = null;
private static String url = "";
private static String user = "";
private static String password="";
...
try
{
     if (con == null)
     {
          url = "jdbc:oracle:thin:@pa.keynote.com:1521:PS01";
          user = "DBlogon" ;
          password = "DBpasswd";
          try
          {
               Class.forName("oracle.jdbc.driver.OracleDriver");
          }
          catch (java.lang.ClassNotFoundException e)
          {
               System.err.println();
          }
          con = DriverManager.getConnection(url,user,password);
     }
     String query = "select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = ?";
     java.sql.PreparedStatement stmt = con.prepareStatement(query);
     stmt.setInt(1, 8); //!= stmt.setString(1, "8");
     java.sql.ResultSet rs = stmt.executeQuery();
//Retrieve an query result
}
catch(SQLException ex)
{
     System.err.print("SQLException: ");
     System.err.println(ex.getMessage());
}
...



This code is actually running 8 different queries! It is the same as running the following queries, and run whatever operation you want on it:

"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 1";
"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 2";
"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 3";
"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 4";
"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 5";
"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 6";
"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 7";
"select slot_alias,page_seq,page_alias from jzhi.trans_steps where page_seq = 8";



Now I heard that doing such operations on the DB is called a prepared statement. The DB saves a partial query on the DB to use it later on multiple times. This is supposed to improve the performance. Is is possible to run something these 8 queries in ASP using prepared statements? If yes, how?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7142267
>>Anthony, I don't plan to remove these posts for the next 2-3 months. I just too busy right now to even look answers right now, but as often as I can.

I am also curious what kind of answer someone may end up with all my questions.
<<
Than I am afraid, I have no longer any interest in helping out.

I wish you the very best of luck.

<unsubscribing>

Anthony
0
 

Author Comment

by:yes4me
ID: 7142284
Oh man... I didn't mean that to happen. Maybe I should I have say nothing. Hopefully he doesn't hate me, cause I will probably still need his help.

What I meant was I am busy one one of my project for the next 2-3 months. But I still check answers in this site everyday and verify if they are actually what I am looking for. If they are, I just accept them as soon as I can write an extended solution if needed (i.e. http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20261745).
0
 

Author Comment

by:yes4me
ID: 7142295
Ohhh... damn... I just understood what Antony said!!

He wrote: "Please maintain your open questions"
I read: "Please maintain open your questions"

Ouch!! I throught he wanted me to keep them open!! Ohh... ok I fix that... sometimes I wish I could edit some of my posts. Sorry for that mess.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 7143578
Yes4Me,

Anthony is simply asking you to close out your questions as soon as possible. If you don't have the time to evaluate an answer, most of us here--who all volunteer our time to help--don't have the time to provide you with one.

It is a pretty rare occasion that one needs to leave a question open for more than a day. After a couple of days, experts rarely continue to contribute. Most often, a question being left open that long is considered an act of rudeness

Fritz the Blank
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 75 total points
ID: 7143637
Pino contacted me and I am back.  I realize that you misunderstood what I said and it is not a problem.

But to get back to your question.  The closest thing that you have to Java's Connection.prepareStatement is the Command's Prepared property.  But as I have stated before, this depends on the provider and to be quite honest in the 4 years that I have been using ADO I have never used it and I suspect I am not the exception.  For the most part, to get the benefits of a pre-compiled statement, most of us rely on SQL Server's or Oracle stored procedures.  But if you are not using any of these DBMS's, this may not be an option.

Anthony
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 7143666
All is well that ends well :)
0
 

Author Comment

by:yes4me
ID: 7144158
Impossible eh? Damn... oh well. That's good to know.
Thanks you all.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now