can dynamic sql be used in java?
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   indrani_mondal
Posted On:   Monday, May 20, 2002 08:12 PM

I have taken user inputted text (in a text area), set it equal to a string, and now I want to compare it to existing records in an access database. I know that it is connecting to the database and retrieving data, becuase my executeQuery statement works when write "WHERE Function = 'free') for example (function is a field name in the database)..however i cannot set function equal to a variable in the where clause. I was talking to some SQL people I know and they said that I should use cursors and dynamic sql to do this, but i'm not sure if those are usable in java/jdbc......... here is some example code: //takes information from text area and saves it in a string // public void insertUpdate   More>>


I have taken user inputted text (in a text area), set it equal to a string, and now I want to compare it to existing records in an access database. I know that it is connecting to the database and retrieving data, becuase my executeQuery statement works when write "WHERE Function = 'free') for example (function is a field name in the database)..however i cannot set function equal to a variable in the where clause. I was talking to some SQL people I know and they said that I should use cursors and dynamic sql to do this, but i'm not sure if those are usable in java/jdbc.........

here is some example code:



			
//takes information from text area and saves it in a string
//

public void insertUpdate(javax.swing.event.DocumentEvent evt)
{

updateLog (evt, "inserted into");

}
public void changedUpdate (javax.swing.event.DocumentEvent evt)
{
//not fired by plain text components
}

public void removeUpdate(javax.swing.event.DocumentEvent evt)
{
updateLog(evt, "removed from");
}

public void updateLog(javax.swing.event.DocumentEvent evt, String ac)
{
Document doc = evt.getDocument();
if( doc.getProperty("name") == "function")
{
try{
functionname = doc.getText(0,doc.getLength());
}catch (BadLocationException ble) {
// Shouldn't get here
}
}
else if ( doc.getProperty("name") == "description")
{
try{
descriptionname = doc.getText(0,doc.getLength());
}catch (BadLocationException ble) {
// Shouldn't get here
}
}
else if ( doc.getProperty("name") == "notes")
{
try{
notesname = doc.getText(0,doc.getLength());
}catch (BadLocationException ble) {
// Shouldn't get here
}
}
else if ( doc.getProperty("name") == "return values")
{
try{
rvvaluesname = doc.getText(0,doc.getLength());
}catch (BadLocationException ble) {
// Shouldn't get here
}
}
else if ( doc.getProperty("name") == "return values description")
{
try{
rvdescripname = doc.getText(0,doc.getLength());
}catch (BadLocationException ble) {
// Shouldn't get here
}
}
}

//
// relevant code from actionPerformed
//

Statement stmt = con.createStatement();

if (evt.getSource()==load)
{
try{
//following line works if Function = 'free' for
//example instead of Function = " + functionname
ResultSet result = stmt.executeQuery("SELECT * FROM function_description, return_code_description " + "WHERE function_description.Function = return_code_description.Function_Name AND Function = " + functionname);
result.next();
function.setText(result.getString("Function"));
description.setText(result.getString("Description"));
notes.setText(result.getString("Notes"));
rvalues.setText(result.getString("Return Codes"));
rvdescrip.setText(result.getString("Return Codes Description"));

}catch (SQLException e) {System.out.println(e.getMessage());}
} catch (Exception e) {lfunction.setText("Cannot load function.");}
}

Please, all help is appreciated. I've been racking my brains and reading lots of text, but i cannot figure out how to fix the problem

thanks!

   <<Less

Re: can dynamic sql be used in java?

Posted By:   Philip_Sheppard  
Posted On:   Monday, May 20, 2002 11:54 PM

Hi,


You should not really get problems, if your String is correctly put together. However, maybe I could point out a neater way of doing such chores. Look at the following code:



String sql = "SELECT * FROM People p WHERE p.id = ? AND p.name = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1,id);
ps.setString(2,name);
ResultSet rs = ps.execute();


An advantage of using the PreparedStatement is that the string isn't dynamically created. Here's an example of a dynamically created string:

String sql = "SELECT * FROM People p WHERE p.i = "+id;

This allows the Java Virtual Machine (JVM) and Driver/Database to cache statements and strings and improve performance.


(this is taken from an article in href="www.techrepublic.com">www.techrepublic.com)


I hope this helps you in some way.


P Sheppard

About | Sitemap | Contact