Prepared Statements

Advertisement
I'm trying to create a prepared static method, I'm unsure how to rewrite the actual query, can anyone help please?
public static synchronized Vector nameRecords(Connection connection, String clientName, String active) throws SQLException{
        String preparedQuery = "SELECT * FROM Clients WHERE ClientName LIKE '" + SQLUtil.encode(clientName) + "%' AND Active = '"+ active + "'";
        PreparedStatement ps = connection.prepareStatement(preparedQuery);
        ResultSet records = ps.executeQuery(preparedQuery);
        Vector results = new Vector();
        while (records.next()== true){
            //Create a Client object.
            Client client = new Client();
            ps.setString(1, client.setClientNo(records.getInt("ClientNo")));
            ps.setString(2, client.setClientName(records.getString("ClientName")));
            ps.setString(3, client.setAddress1(records.getString("Address1")));
            ps.setString(4, client.setAddress2(records.getString("Address2")));
            ps.setString(5, client.setAddress3(records.getString("Address3")));
            int status = ps.executeUpdate();
            ps.close();
            results.add(client);
        return results;
    }
Advertisement

Replay

I think I nearly have this working, but I receive an SQL exception:
Clients SQLException: java.sql.SQLException: Syntax error or access violation: Y
ou have an error in your SQL syntax near '? AND Active = ?' at line 1. Any clues?
public static synchronized Vector nameRecords(Connection connection, String clientName, String active) throws SQLException{
        String preparedQuery = "SELECT * FROM Clients WHERE ClientName LIKE ? AND Active = ?";
        PreparedStatement ps = connection.prepareStatement(preparedQuery);
        ResultSet records = ps.executeQuery(preparedQuery);
        Vector results = new Vector();
        while (records.next()== true){
            Client client = new Client();
            ps.setString(1, SQLUtil.encode(clientName)+"%");
            ps.setString(2, active);
            client.setClientNo(records.getInt("ClientNo"));
            client.setClientName(records.getString("ClientName"));
            client.setAddress1(records.getString("Address1"));
            int status = ps.executeUpdate();
            results.add(client);
        ps.close();
        return results;
    }

Handling ' and " in a Prepared Statement

Hi, I am using a prepared statement (WebLogic Portal Server having JDBC communication with MS SQL server) I HAVE to pass a string like '"*ABC*"' i.e. <single quote><double quote>*ABC<double quote><single quote> If i hard

Weblogic and prepared statement

We had an odd situation in production. We had a situation where we would always get a ORA-00904 Invalid Column name when running the following bit of code (via the prepared statement):                preparedStatement = con.prepareStatement("select *

Prepared statement help... pls...

hey all... need help... this prepared statement is driving me mad... keeps throwing an error in my servlet.... can somebody pls help      PreparedStatement updateWiki = con.prepareStatement("UPDATE sportinfo SET pageinfo,parent = (?,?) WHERE sport LI

Problem with Oracle Prepared Statement

Hi all, Please help me.. I Have 2 pages, in first page i had table with rows with update image for each row, when i click update it leads to 2nd page which shows the particular row information my problem is, i am executing prepared statement with 2 p

Resultset from a batch select using prepared statement

Hi, Is it possible to add results of batch execute of select statements and retrieve the result set .. I am running select statement in a batch mode setting in value for a column.I add this to a prepared batch. I would like to get the reults as a bat

Using a Prepared stat - Can a select SQL exist inside an Insert SQL

Trying to insert values into the DB2UDB 7.1 database , using Prepared statement. I am getting a SQL exception as follows: SystemErr R java.sql.SQLException: Could not execute sql command - Original message: [IBM][CLI Driver][DB2] SQL0418N A statement

Best Practice: Combine prepared statements with ;

Hi, I would like to know what the best prctice is for combining prepared statements to give the query below INSERT INTO my_table (value) VALUES (?); SELECT LAST_INSERT_ID()The reason for this is that i have written a simple DB wrapper to handle my da

Performance of Stored procedures against Prepared Statements

What will be the exact difference in the performance between implementing stored procedures and prepared statements ?Short answer: it depends. There will probobaly be very little difference in performance difference for a sipmle insert / update etc.

How to close a prepared statement

Hi guys how do you close a prepared statement???? PreparedStatement prep = db.conn.prepareStatement(query);      for (int lkk=0; lkk<m; lkk++) {                prep.clearParameters();                prep.setString(1,x[contador]);                Resul

FindByPrimaryKey: Could not find prepared statement with handle 3

I've inherited a WL61 application and been asked to make it work under WL81. We're using SQL Server 2000. We only access two tables. The XML got auto-converted during the upgrade, but I had to correct the RDBMS column names in the weblogic-cmp-jar.xm

Executiong prepared statement with Like% in SQL Query?

Hi, We are developing GUI project.In which we need to retrieve the contract details based on the Name which we enter in eVision page.Here we will not enter the whole name,we will enter only the part of the name,for example if the actual name is Sun S

Prepared Statement nested calls (Oracle)

I have a prepared statement like select type , id from ms_page_position where page_id = (select id from ms_url where url = ?) and column_id = (select id from ms_column where name = ?) and preview = ? order by position asc. If i place in pstmt.setStri

Prepared statement parameter order?

We have prepared statements for insert and update where changing the order of columns in the statement gives different results. Problem 1: For an update statements setting 8 column values via preparedStatement.executeUpdate, we got the following exce

Prepared Statement Flooding

Hi Friends, Need your help to fix this prolonging problem in one of our applications. We observe that the following queries are declared as prepared statements but have dynamic value ROWID in each. SELECT ROWID, C. * FROM Table1 C WHERE C.VENDORLOGIN

Unable to set string in prepared statement

Hi all, I just want to set an string to a prepared stmt. the setting string is in the format..... the integers with comma saparated.... str="23,55,22" ps.setString(1,str) The prepare statement is taking only the first integer... i.e. 23 can any

Using prepared statements in java

Hi I have question on proper usage of prepared statements. PreparedStatement ps; public methoda(){ ps=conn.prepareStatement(sqlQry); ps.setInt(1,2); ps.execute(); In the above case I am creating new references to prepared statement (ps)each time I in

Sql server 2000 stored procs vs. prepared statements performance

Hi I have observed that using stored procedure in sql server 2000 is much much faster than using a prepared statements. I had worked with oracle before and did not notice this much difference. I would like to use prepared statements or regular sql (f

Keep prepared statement across commit

Hello everyone! Is there any way the Oracle keeps prepared statements across commits (same connection, same process)? Thank you in advance WolfgangCaching this occurs transparently, even across sessions. No need to do anything special...Read other 3

Ouput sql from prepared statement

I'm using prepared statements and I want to output the sql that is being run in the statement to the console. For example: Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@xx.xx.x.xx:xx

URGENT :::: Prepared Statement : Invalid Conversion Error

Hi, I have prepared statement to update a database record. It is working fine but sometime it throws follow. SQLException. I checked the data I'm passing to the prepared statement. Everything works fine on the desktop but sometime it throws the foolo