We got any Java geniuses here? (JDBC) long

[mmbt0ne]

Please don't bother reading on unless you really know what you're talking about. I know for a fact all the code up until this point is flawless.

Google hasn't been helpful because I'm looking for a much more specified answer. I don't have a book to look this up in, because it's not even a programming class, it's Human Integrated Systems, which usually involves just reading a lot of case studies. Why my professor suddenly decided that we should program an interface using Java, I'll never know.

I'm connecting to a PostgreSGL 7.4.3 database through a Java application, and I'm wondering if anyone here can help me figure out why I'm not accessing the table right.

The table is organized as follows:

create table "gt1234a1234" ("gtid" character varying(11), "ssn" character varying(4), "randomCode" character varying(15));
create table "gt1234a1234b" ("account" character varying(10), "balance" NUMERIC(8,2), "transTime" TIMESTAMP, "notes" character varying(30));

insert into "gt1234a1234b" ("account","balance","transTime","notes") values ('Checking','2608.05','02/15/2005 10:00:00','');
insert into "gt1234a1234b" ("account","balance","transTime","notes") values ('Savings','11349.57','02/15/2005 10:00:00','');

create user gt1234a1234;

insert into "gt1234a1234" ("gtid","ssn","randomCode") values ('gt1234a1234','1234','.scLGiCRFWV9I');
grant select, insert, update on gt1234a1234 to gt1234a1234;
grant select, insert, update on gt1234a1234b to gt1234a1234;


And here is the code I have for accessing the table after connecting:

public void lookupTheDatabase()
throws ClassNotFoundException, FileNotFoundException, IOException, SQLException
{
Connection databaseConnection2;
Statement databaseStatement2;
DatabaseMetaData databaseConnectionMetaData2;

/*
* Replace yourDataBaseName by your gt ID concatenated with your password.
* For instance, if your ID is gta123b and your password is 6789,
* yourDataBaseName is gta123b6789
*/
String tempPass = (new String(jPasswordField1.getPassword()));
// String query = "SELECT * FROM gtg925f7314 WHERE gtid = 'gtg925f7314' AND ssn = '7314'";
// String query = "SELECT * FROM " + "gtg925f7314" + " " + "WHERE gtid = " + "'" + jTextField1.getText() + "'";
// String query = "SELECT * FROM " + "gtg925f7314" + " " + "WHERE gtid = " + "'" + jTextField1.getText() + "'" + " AND ssn = '1234'";
//String query = "SELECT * FROM gtg925f7314 WHERE gtid = " + "'" + jTextField1.getText() + "'" + " AND ssn = '" + tempPass + "'";
// String query = "SELECT * FROM " + jTextField1.getText() + " " + "WHERE gtid = " + "'" + jTextField1.getText() + "'" + " AND ssn = '" + jPasswordField1.getText() + "'";
// String query = "SELECT * FROM studentcodes4009 " + "WHERE gtid = " + "'" + jTextField1.getText() + "'" + " AND ssn = '" + jPasswordField1.getText() + "'";
// String query = "SELECT * FROM studentcodes4009";
// String query = "SELECT * FROM gtg925f7314";

jTextArea1.setText( "");
jTextArea1.append( "User ID: " + jTextField1.getText());
jTextArea1.append( "\nPassword: " + jPasswordField1.getText());
jTextArea1.append( "\nSecurity Code: " + jPasswordField1.getPassword());

databaseConnection2 = databaseConnection;
databaseConnectionMetaData2 = databaseConnectionMetaData;
databaseStatement2 = databaseStatement;
ResultSet rs = databaseStatement2.executeQuery(query);

if(rs != null)
{
while(rs.next())
try
{
jTextArea1.append("\n Your ID: " + rs.getString(1));
jTextArea1.append("\n Code: " + rs.getString(3));
jTextArea1.append("\n Your ID: " + rs.getString(1));
jTextArea1.append("\n Code: " + rs.getString(3));
jTextArea1.append("Welcome ");
jTextArea1.append(jTextField1.getText());
jTextArea1.append("\n");
jTextArea1.append(new String(jPasswordField1.getPassword()));
}
catch (java.lang.NullPointerException nullex)
{
jTextArea1.setText( "No information is available.\n");
}
}
else {
jTextArea1.append("WTF Mate?");
}


I'm trying to figure out how to set up the query String in order to get it to access correctly. None of the commented out options are working.

Thanks for any help anyone can give me.

[Dynasty]

http://img.photobucket.com/albums/v720/DynastyPoker/ngbbs405e55818e412.jpg

Just having fun.

[wacki]

mmbt0ne, hasn't anyone ever told you about LISTSERV's?

[VBM]

I don't get it.

If you create 2 tables, named:
"gt1234a1234b"
and
"gt1234a1234"

then you select from
"gtg925f7314"
studentcodes4009
jTextField1.getText()

but, none of those reference the tables you've created. Nor is there any JOIN clause referencing the created tables. Do those tables in your select strings exist previously? What error message do you get back?

--oj.

[mmbt0ne]

</font><blockquote><font class="small">En réponse à:</font><hr />
mmbt0ne, hasn't anyone ever told you about LISTSERV's?

[/ QUOTE ]

?

Please explain.

[mmbt0ne]

</font><blockquote><font class="small">En réponse à:</font><hr />

I don't get it.

If you create 2 tables, named:
"gt1234a1234b"
and
"gt1234a1234"

then you select from
"gtg925f7314"
studentcodes4009
jTextField1.getText()

but, none of those reference the tables you've created. Nor is there any JOIN clause referencing the created tables. Do those tables in your select strings exist previously? What error message do you get back?

--oj.

[/ QUOTE ]

Yeah, pretend all those are the same. My actual tables are gtg925f7314, but they sent out that txt file as a sample of what it would look like. All of the stuff that is commented out '//' just ignore. I tried it, or it was in the original document and it doesn't work.

All of the methods called, and labels referenced are defined in the rest of the .java file, but it's somewhere around 500 lines, and the rest of it is really not important to the problem. This method is where it's getting hung up. Specifically on the part where it references the query String to set ResultSet rs.

[wacki]

This is why I [censored] hate CS programs. They are designed to make you fail.

A LISTSERV is an e-mail list you can post questions to and people will help you. Kind of like this board but for CS. You name a category, they have a LISTSERV for it.

[AEKDBet]

We call 'em newsgroups here. git.cc.class.somethingjavarelated.

I'd like to help ya mmbt0ne, but that's one big question, and I've procrastinated enough for my math 3215 (prob stat) test. good luck.

[mmbt0ne]

Oh man, good call. It's been 2 years since I was in a programming class, so I completely forgot about that. Now I just need to figure out which CS class will know the most about this. I'm guessing 4400, but we'll see. I also e-mailed the TA my code, but expect to get some cryptic response back that tells me nothing.

Bah, the functionality of this is 90% of the grade, so once I get it to access correctly, I'm golden.

[turnipmonster]

if you post the exception you are getting, it would help as I'm still not sure where exactly your problem is.

so, I haven't done jdbc stuff directly for a while. but a few things I would look at:

doing a "select *" is generally a bad form, but it should work although you may be getting columns back in a weird ordering. try selecting exactly the columns you want instead.

these lines:
databaseConnection2 = databaseConnection;
databaseConnectionMetaData2 = databaseConnectionMetaData;
databaseStatement2 = databaseStatement;
ResultSet rs = databaseStatement2.executeQuery(query);

seem pointless, I am suspicious that the connection/statement is actually getting setup correctly.

--turnipmonster

[Sporky]

well, i'm not an expert on the software you are using...but at first glance it looks like you aren't querying a table with your select statements.

you say:

[ QUOTE ]

The table is organized as follows:

create table "gt1234a1234" ("gtid" character varying(11), "ssn" character varying(4), "randomCode" character varying(15));
create table "gt1234a1234b" ("account" character varying(10), "balance" NUMERIC(8,2), "transTime" TIMESTAMP, "notes" character varying(30));


[/ QUOTE ]

ok, so lets pretend you have two tables named whatever(gt1234a1234) and whateverb(gt1234a1234b) in a database called supbro(gtg925f7314)

here is what some of your queries look like:
[ QUOTE ]

String query = "SELECT * FROM gtg925f7314 WHERE gtid = 'gtg925f7314' AND ssn = '7314'";
String query = "SELECT * FROM " + "gtg925f7314" + " " + "WHERE gtid = " + "'" + jTextField1.getText() + "'";


[/ QUOTE ]

so essentially you are saying select everything from supbro where some fields in whatever equal something. the problem i see is that your query statements look wrong. your select statements should specify a table name. you are already connecting to the supbro database somewhere in your code (i assume that part is left out). once you are connected...you then directly query tables in that database. for example:

SELECT ssn FROM whatever WHERE ssn = '5555'

my format:
SELECT fields FROM tablename WHERE conditions

your format:
SELECT fields FROM databasename WHERE conditions

i believe when you are performing your query, the database server is not sure where you are trying to go. sometimes database servers will have many databases on them. i'm not sure in the database software you are using, but i know in what i'm used to i can specify which database to access the specific tables i'm looking for. for example:

SELECT ssn FROM supbro..whatever WHERE ssn = '5555'

or in your case:

SELECT ssn FROM gtg925f7314..gt1234a1234 WHERE ssn = '5555'

you add the '..' to specify the database. i'm not sure if your software supports this, but if replacing the database name with a tablename doesn't work. it can't hurt to try.

let me know if it works or doesn't.

[Sporky]

as turnipmonster pointed out. you may not be connecting properly. i assumed you were connecting properly in my post. you might want to post your connection code and the exception you are getting as he mentioned.

[turnipmonster]

[ QUOTE ]

ResultSet rs = databaseStatement2.executeQuery(query);


[/ QUOTE ]

obviously this line is not pointless, didn't mean to include that.

[Sporky]

you can ignore this whole part of my post. i tried thinking before i had coffee again. you should be connecting to a specified database on the server in your connection string, so you won't need the '..' in that case. woops.

[ QUOTE ]

sometimes database servers will have many databases on them. i'm not sure in the database software you are using, but i know in what i'm used to i can specify which database to access the specific tables i'm looking for. for example:

SELECT ssn FROM supbro..whatever WHERE ssn = '5555'

or in your case:

SELECT ssn FROM gtg925f7314..gt1234a1234 WHERE ssn = '5555'

you add the '..' to specify the database. i'm not sure if your software supports this, but if replacing the database name with a tablename doesn't work. it can't hurt to try.


[/ QUOTE ]

[Phil Van Sexton]

You need to include the error you are getting.

These lines should work:
/ String query = "SELECT * FROM studentcodes4009";
// String query = "SELECT * FROM gtg925f7314";

Therefore, you probably aren't connected to the database at all. The included code certainly doesn't connect to anything.

[mmbt0ne]

Connecting isn't the problem. The code for connecting is in another method and it's working just fine. Also, the code isn't throwing an exception anywhere, it's just not appending the results to the jTextView like it should be.

These lines:
databaseConnection2 = databaseConnection;
databaseConnectionMetaData2 = databaseConnectionMetaData;
databaseStatement2 = databaseStatement;

are because databaseConnection, databaseConnectionMetaData, and databaseStatement are all private variables with the default values. We were told to make new instances each time we tried to access the database. That part of the code was given to us.

Thanks for everyone's help so far.

[turnipmonster]

[ QUOTE ]
it's just not appending the results to the jTextView like it should be.


[/ QUOTE ]

are you getting a null resultset or no?


[ QUOTE ]

are because databaseConnection, databaseConnectionMetaData, and databaseStatement are all private variables with the default values. We were told to make new instances each time we tried to access the database. That part of the code was given to us.

[/ QUOTE ]

not trying to get in an argument but this explanation doesn't make any sense at all. if they were private variables from another class, you would have to say something like:
databaseConnection2 = Whatever.getDatabaseConnection();

if you can make an assignment like you're making, they are not private variables within that scope and making said assignment doesn't do anything. you can certainly do it and it won't harm anything, but it doesn't do anything either.

[mmbt0ne]

</font><blockquote><font class="small">En réponse à:</font><hr />
are you getting a null resultset or no?

[/ QUOTE ]

I don't know. I put in that else statement to try to catch a null set, but that result isn't showing up either.

</font><blockquote><font class="small">En réponse à:</font><hr />

</font><blockquote><font class="small">En réponse à:</font><hr />

are because databaseConnection, databaseConnectionMetaData, and databaseStatement are all private variables with the default values. We were told to make new instances each time we tried to access the database. That part of the code was given to us.

[/ QUOTE ]

not trying to get in an argument but this explanation doesn't make any sense at all. if they were private variables from another class, you would have to say something like:
databaseConnection2 = Whatever.getDatabaseConnection();

if you can make an assignment like you're making, they are not private variables within that scope and making said assignment doesn't do anything. you can certainly do it and it won't harm anything, but it doesn't do anything either.

[/ QUOTE ]

Ok, I'm at work right now, but I'm on my way back to my apartment right now. When I get back, I'll post some more of the code, and hopefully that will help.

[Sporky]

[ QUOTE ]

These lines should work:
/ String query = "SELECT * FROM studentcodes4009";
// String query = "SELECT * FROM gtg925f7314";


[/ QUOTE ]

gtg925f7314 is a database name and i'm not even sure what studentcodes4009. i just tried SELECT * FROM databasename to make sure on my SQL Server and it says no sir. i agree that SELECT * FROM tablename should work.

also, even if SELECT * FROM database did work, how would it know what tables to filter with the expressions?
SELECT * FROM databasename where ssn = '5555' &lt;-- i don't see how that would work.

[mmbt0ne]

Ok, as promised. All of this is in the DatabaseInteraction.java file.


public class DatabaseInteraction extends javax.swing.JFrame {

private Connection databaseConnection; // The connection to the database
private Statement databaseStatement; // Our statement to run queries with
private DatabaseMetaData databaseConnectionMetaData; // This defines the structure of the database
private boolean isConnected = false;

And then a bunch of stuff he told us not to touch.
This is what happens when you click on the button in question

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
//isConnected = true;
if (isConnected)
{
try
{
jTextArea1.setText(" Result \n\n");
jTextArea1.append(jTextField1.getText());
jTextArea1.append("\n");
jTextArea1.append(new String(jPasswordField1.getPassword()));
//PROBLEM STARTS HERE EVERYTHING BEFORE THIS WORKS
lookupTheDatabase();
}
catch (Exception ex)
{
System.err.println("Exception caught.\n" + ex);
ex.printStackTrace();
}
}
else
{
jTextArea1.setText("\n Sorry, not connected to database.\n");
}
}

And you already have the lookupTheDatabase method. I don't know if this helps. The TA told me to come see him during the help session, but that's not until 5, and if I can't get this working until after 7 it's going to suck trying to get the rest of it to work by 5pm on Friday.

[mmbt0ne]

</font><blockquote><font class="small">En réponse à:</font><hr />
</font><blockquote><font class="small">En réponse à:</font><hr />

These lines should work:
/ String query = "SELECT * FROM studentcodes4009";
// String query = "SELECT * FROM gtg925f7314";


[/ QUOTE ]

gtg925f7314 is a database name and i'm not even sure what studentcodes4009. i just tried SELECT * FROM databasename to make sure on my SQL Server and it says no sir. i agree that SELECT * FROM tablename should work.

also, even if SELECT * FROM database did work, how would it know what tables to filter with the expressions?
SELECT * FROM databasename where ssn = '5555' &lt;-- i don't see how that would work.

[/ QUOTE ]

I LOVE YOU!!!

It's working now as it reads:
String tempPass = (new String(jPasswordField1.getPassword()));
String query = "SELECT * FROM gtg925f7314 where ssn = " + tempPass;

And it returns the beautiful result of:
Your ID: gtg925f7314
Code: .sWZ3/2SWwu5U

Welcome gtg925f
7314

EDIT: Just for clarification, I took the "gtid = " part out of the code, and voila, all of a sudden I'm accessing the database like it's my effing job!

[Sporky]

that's weird dude. you sure it doesn't say:

String query = "SELECT * FROM gt1234a1234 where ssn = " + tempPass;

you're still doing a select * from a database instead of a table. hell, if it works...more power to you. good luck.

[turnipmonster]

doesn't really help much, other than confirm you don't need to instantiate databaseConnection2 at all, just use the private variables. that's beside the point though.

try outputting the size of the resultSet to somewhere (either JTextArea or System.out). beyond that, I don't really know what to tell you, other than use a very general query at the bottom and make sure you are actually getting a connection.

--turnipmonster

[Duke]

You can't guarantee column ordering with *, so explicitly tell it which columns/order that you want. I've had funny things happen when I've tried to reference columns out of order in the past, but that'll be determined by your JDBC driver implementation. Your method should work, but the drivers could be gay and screw up because you're referencing column 1 then 3 then 2 then 3, or whatever.

Whatever it is that you're doing regarding the connection is wrong. Really. You should have either a connection pool that you get connections from and put back to, not some global connection. This is going to break or at least have severe problems the second you try to use code like this in a multithreaded environment.

Also put in the code to nicely close the connection/statement/all of that. Don't just sit back and rely on garbage collection.

Other than that, I see no reason why it won't work as soon as you actually give it a query of some sort to execute (they're all commented out from what I can tell). In principle your code is fine, though.

Other tips:

Do not hard-code anything.

Do not have a connection or a statement as a class variable.

Separate things a bit better. That'll allow you to find out exactly where the problem is happening. I think the new versions of java have some basic assertion functionality. Use it liberally while debugging. You should really know the precise line that your code is failing at before asking around.

Don't you get a statement from a connection?
What are you using the metadata object for?
Why are they class variables (they look like they are)?
Why are you renaming them?

Answer all of these questions for yourself and you'll probably eventually run into your problem. No, I can't tell exactly what your problem is from here since it should basically work.

~D

[mmbt0ne]

yeah, the table is actually named gtg925f7314. The part I put up there earlier was a form letter sent to everyone in the class, demonstrating how their database/tables would be named. Now I just need to gain access to the second table (which should be pretty easy now) and I'll have at least 50% of the grade.

As for duke:
http://img.photobucket.com/albums/v720/DynastyPoker/ngbbs405e55818e412.jpg

I think you overestimate my computer ability. I'm simply an Industrial Engineer who was given a lot of half finished code, and told to fill in the blanks. If I understand you correctly, all of the actual connecting is done for me. I didn't program that, haven't pasted that code on here, and will never ever touch it. It was the accessing that I was having problems with, but it's better now. So far at least.

[mmbt0ne]

OH MAN!!!! SECOND TABLE ACCESSED FLAWLESSLY!!!! Fictional account balances are as follows:

Checking Balance: 1920.05
Savings Balance: 7433.57

I think it would be good form to put a '$' in front of the numbers though. Hell, this project is basically an exercise is interface design anyway, which is why it really pissed me off that the database thing was so complicated if you don't remember anything about java.

Phew, ok, all the majorly crappy stuff has been fought through I believe. Now all I have to do is make a bunch of new frames, copy over some code, and make sure that this acts like an atm. I have to let people withdraw money, transfer money between accounts, and keep a history of the last 5 transactions for each account which can be displayed by selecting an option on one of the frames. Overall, that should be a walk in the park.

Thanks again to everybody, even if all you did was point out how stupid my code is, it made me think about why the hell my professor wants us to do this.

[Sporky]

glad to hear it's working. apparently i was confused by the code you originally posted with it saying a table was actually a database. sounds like you sorted it all out. good luck.

[mmbt0ne]

Ok, this is going to be much easier I'm sure. I'm in class B, trying to call a method from class A. I'm getting this error:

non-static method disconnectFromDatabase() cannot be referenced from a static context

First I moved the reference out of my original method, and made a new public static void method to handle it. That didn't help. I can't make the original method static, because it uses variables that I need to be able to edit. I know this is easy, I'm almost sure I did it a few years ago in class. Is this where I would extend a class?

[NoTalent]

you need to create an instance of the class--that is why you are getting that error. Otherwise you need to make disconnectFromDatabase() static.

[mmbt0ne]

Thanks a lot. See, I knew it was that easy.

I was going to ask about global variables (or the lack thereof) but I was able to figure that one out by myself. So believe me, I really am trying to do these myself. It's just when I get stuck and have no idea what terms to google, I know that there are enough smart people here to find an answer.