• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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
0
yes4me
Asked:
yes4me
  • 6
  • 5
  • 4
  • +1
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Anthony PerkinsCommented:
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now