How do you write a prepared statement?

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
yes4meAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
fritz_the_blankCommented:
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
 
Anthony PerkinsCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
yes4meAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
fritz_the_blankCommented:
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
 
yes4meAuthor Commented:
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
 
yes4meAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
yes4meAuthor Commented:
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
 
yes4meAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
caraf_gCommented:
All is well that ends well :)
0
 
yes4meAuthor Commented:
Impossible eh? Damn... oh well. That's good to know.
Thanks you all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.