Constructing an SQL string in Java

[violinsoundcool]

Re: Constructing an SQL string in Java

Now, how do I make the link from the fields in the html page to the userInputFields in the servlet?

[bryce4president]

Re: Constructing an SQL string in Java

The link is already made. You used the user input fields to build your sql statement. You will now need to put the 'sql' into the call to the resultset method I gave you.

it will look something like this...

ResultSet myRS = RS.resultSet(sql.toString(), connDB);

Its no different then you saying...

String mySQLString = "select mname, maddy, mphone from mylib/myfile where mname='joe'";

and then putting the mySQLString into your resultset call.

Understand what's going on? The only thing you did was use the input fields to build your sql string that is used to make your call to the DB. By using them to build the string you've already made that "link".

[violinsoundcool]

Re: Constructing an SQL string in Java

I understand how to construct the sql string now, but now, I'm not sure what the parts of this statement are:

ResultSet myRS = RS.resultSet(sql.toString(), connDB);

Is ResultSet the name of the java class? Or, is that myRS?

[bryce4president]

Re: Constructing an SQL string in Java

I'm sorry, I wasn't consistent with my naming....This should better explain it...


You have the RS class that I gave you. In that class there is a method called myRS(String,Connection).

myRS returns a value of type ResultSet. So when you call RS.myRS(sql.toString(),connDB) you need to hold the return value in a resultset.

So you would say...

ResultSet MyQuery = RS.myRS(sql.toString(), connDB);

ResultSet MyQuery <------ this creates an instance of a ResultSet called MyQuery to hold the returned values of the class and method call RS.myRS(sql.toString(), connDB)

I do the .toString() because the method accepts a string and not a stringbuffer.

Once the results are returned you just handle the resultset like any other result set

[violinsoundcool]

Re: Constructing an SQL string in Java

What should follow should be something like this, right?

while(______.next())
{
     BeanScrapSearch infoBean = new BeanScrapSearch();
     infoBean.setYard(_________.getString("tdydno"));
     infoBean.setLoad(_________.getString("tdload"));
     beanList.add(infoBean);
}

What needs to go in the blanks?

[bryce4president]

Re: Constructing an SQL string in Java

The name of your ResultSet needs to go in the blanks. See the connection now?

[violinsoundcool]

Re: Constructing an SQL string in Java

Well, I have the name of my ResultSet in there, but it says that it cannot be resolved. I think it's something wrong with the servlet, because it's saying the same thing about ConnDB and RS. Any ideas?

[bryce4president]

Re: Constructing an SQL string in Java

The name of your ResultSet that you hold the query data in is what goes in there...eg

ResultSet MYQUERY = RS.myRS(sql.toString(), connDB);
while(MYQUERY.next())
{
     BeanScrapSearch infoBean = new BeanScrapSearch();
     infoBean.setYard(MYQUERY.getString("tdydno"));
     infoBean.setLoad(MYQUERY.getString("tdload"));
     beanList.add(infoBean);
}

See the connection now?

[violinsoundcool]

Re: Constructing an SQL string in Java

Great. That fixed that problem. For some reason, it still cannot resolve ConnDB.

[bryce4president]

Re: Constructing an SQL string in Java

Yeah, you need to make sure that you have those classes include in your package

if you created a new project then you need to create a new package to copy those classes into.

what you could do is create a package called query and put the classes in that package. Then just copy that package from project to project and at the top of your servlet or whatever class you write you will just do

import query;

I don't want to confuse you but to make this a TRUE MVC you need to get the database connection and result set processing out of the servlet and into more generic java classes that are called from the servlet. Think of the servlet as a traffic cop who just calls on the proper classes to do what it needs and then handles any request/response that is needed with the browser. I will work on expanding the tutorial code to show this, I just didn't want to bog you down with that part before you understood the basics of connecting to the DB, running a query on it, and handling the returned data.

[violinsoundcool]

Re: Constructing an SQL string in Java

That's the confusing thing. I have ConnDB.java within the new package. I would understand the program not being able to resolve it if the class wasn't in the package, but it is. I also made sure that the spelling and capitalization was correct. And, it apparently doesn't have a problem with:

Connection opnConn = ConnDB.opnAS400Conn();

Only the problem comes a few lines later:

ResultSet MyQuery = ScrapSearchRS.myRS(sql.toString(), ConnDB);

If it doesn't have a problem with the former line of code, then I would think that it would be able to resolve ConnDB in the second line. I'll continue searching for solutions, but let me know if you have an idea.

That's a great idea, by the way, to make one query package.

[bryce4president]

Re: Constructing an SQL string in Java

Sorry, you need to change ConnDB to be opnConn in your call to the resultset method. opnConn is the name of your connection.

[violinsoundcool]

Re: Constructing an SQL string in Java

Okay, so I shouldn't be calling the class, then. Great. Now, I have no errors. I have a warning, but I don't think it changes anything since it was also in the CustSrch program and ran fine.

Now, it looks like my only problem is in html. I am not sure how to run the servlet when the "submit" button is pressed. Here is how I have it right now:

<form name="ScrapSearch" action="ScrapSearchServlet" method="POST"><input 
             type="submit" value="Submit"></form>

With CGIDEV2, I was able to put all of the input fields and the submit button in the same form, so that I could pass the values of the input fields. If I try that in the jsp page, then on the line where I have the </form> tag, it wants the <form> tag on the same line, or else it can't find it. Any ideas on that? I know this is getting off of the original subject. I'm not sure what the protocol on starting new threads from previously discussed ideas is.

[violinsoundcool]

Re: Constructing an SQL string in Java

Well, it let me put the <form> tag at the top of the table like I do in CGIDEV2. I tried to run it, and it was moving really slow. Finally, after about a minute and a half, the results.jsp page came up. No results were there yet, but it was still loading the page. So, I gave it a few minutes. About five minutes passed by, and then the program stopped responding. So, I had to quit with the server still running. This is about the fourth or fifth time something like this has happened in the last few days, and when I open the program back up, it gives me an error message dialog that says:

License key check failed: Model2 Base does not have a valid license key to run.

Since the first time it happened, the server has been acting funny. I saw on one of your previous posts, bryce, that you had a similar problem, but it fixed itself. Is this the same or different from what you experienced?

[bryce4president]

Re: Constructing an SQL string in Java

Can you post your whole html file? I think I have an idea of what's going on but I won't know until I see the page