How do you write a prepared statement?

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

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

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);

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

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
https://www.experts-exchange.com/questions/20292461/Make-it-so-that-the-user-cannot-select-the-text-for-NS4-79.html

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

Thanks,
Anthony

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

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

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.

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?

>>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

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. https://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20261745).

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.

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

All is well that ends well :)

Impossible eh? Damn... oh well. That's good to know.
Thanks you all.