replace sql string question

[GoSooJJ]

Hi all,
I have this sql string:

select lname, fname from users where lname = '#lname#' and fname = '#fname#'

now i want to read that sql string and convert to:

select lname, fname from users where lname = '"+lname+"' and fname = '"+fname+"'

I have hard time to read each characters and replace function. Can anyone help?
Thx.

 

[GoSooJJ]

I got this one working ^^.
but now i'm facing another problem. I'm reading sql statement from the text file and trying to insert that into stmt.executeQuery(sqlStr). But the variables didn't parsed at all. Will this ever work?

 

[JVZ]

This is because java does not consider them as variables, it is consider to be part of the string...I not sure if the below code is what you are looking for..but only way I could get this to work is with regular expressions. I sure there are other ways...however I can't think of one. Also may I ask why your are doing it this way, instead of using something like a Prepared statement?

hope this help you....

import java.util.regex.*;
import java.util.Date;
import java.io.FileReader;
import java.io.BufferedReader;
import java.io.BufferedInputStream;
import java.text.SimpleDateFormat;
import java.sql.*;


public class GoSooJJ
{
	public static void main(String[] args) throws Exception
	{

		String path 			= "C:\\";
		String file 			= "SqlStrings.txt";
		BufferedReader input 	= null;

		FileReader fileReader 	= new FileReader(path+file);
		input 					= new BufferedReader(fileReader);

		String newLine 			= System.getProperty("line.separator");

		String  inputSqlStmt	= input.readLine();

		//Below is what word to search for within the string
		Pattern lastName 		= Pattern.compile("#lname#");
		Pattern firstName 		= Pattern.compile("#fname#");

		//If two argument are not passed from command line, a ArrayIndexOutOfBoundsException
		//will be thrown.
		String lname 			= args[0];
		String fname 			= args[1];

		//Supply a string for the program to search
		Matcher matcher1 		= lastName.matcher(inputSqlStmt);

		//A tempory holding place
		String tmpSQL 			= null;

		//holds the final SQL Statement
		String SqlStatement 	= null;

		//attempts to find "#lname#" within the string from the txt file, if it find it, the replaceAll
		//method will replace "#lname#" with lname.
		if(matcher1.find())
		{
			tmpSQL 				= matcher1.replaceAll(lname);
		}

		//Need to create another Matcher to find the "#fname#" within the string supplied.
		Matcher matcher2 		= firstName.matcher(tmpSQL);

		//attempts to find "#fname#" within the string from the txt file, if it find it, the replaceAll
		//method will replace "#fname#" with fname.
		if(matcher2.find())
		{
			SqlStatement 		= matcher2.replaceAll(fname);
		}
		System.out.println("The following SQL Statement was created: " + newLine  + SqlStatement);


		SimpleDateFormat sd 	= new java.text.SimpleDateFormat("hh:mm:ss");
		String time 			= sd.format(new Date());

		System.out.println("The process of creating the SQL Statement Completed at: " + time);

		//Starting the process of  connecting to DB.
		Connection aConnection	= null;

		try
		{



			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			//Creating a DSN to DB
			aConnection 		= DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C:/testDB.mdb","","");
			Statement stmt 		= aConnection.createStatement();
			ResultSet rs 		= stmt.executeQuery (SqlStatement);

			//A test to see if the query returned any rows, I asumed that only one will be returned by
			//the query.
			if (rs.next())
			{

				String fName 	= rs.getString("fname");
				String lName 	= rs.getString("lname");

				System.out.println("Found a match: " + fName + ", " +  lName);
				System.out.println("The Database Process Completed at: " + time);
			}
			else
			{
				System.out.println("No match was found");
				System.out.println("The Database Process Completed at: " + time);
			}
		}
		catch (SQLException sqlException)
		{
			System.out.println("There was a problem, please review: " + newLine + sqlException);
		}
		catch (ClassNotFoundException clsNotFoundEx)
		{
			System.out.println("There was a ClassNotFoundException thrown, please review: " + newLine + clsNotFoundEx);
		}
	}
}