• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Console SQL Query App Not Working Correctly

 
Ray mann
Ranch Hand
Posts: 39
Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys, my console SQL Query app is not giving me the desired output.I am sure its might have something to do with my SQL Query.Its missing the description of the stock items and it prints a new line for each user as per number of stocks an repeats the user details and then prints the stock listed by that user(only want the user listed once with all its stocks). I will add my files and current output and desired out put below.

Thanks in advanced for all and any assistance!

MakeDB.java:





QueryDatabase.java:



Output:
Stock holdings by User

User ID User Name
Stock - Description
-------------------------------------------
admin01 Default Admin
DELL
admin01 Default Admin
MSFT
admin01 Default Admin
ORCL
user01 Bill Buyout
DELL
user01 Bill Buyout
MSFT
user02 Fran Futures
MSFT
user02 Fran Futures
ORCL

Press any key to continue . . .

I would like the output as follows :

Stock holdings by User

User ID User Name
Stock - Description
-------------------------------------------
admin01 Default Admin
DELL Dell Computer Corporation
MSFT Microsoft Corporation
ORCL Oracle Corporation

user01 Bill Buyout
DELL Dell Computer Corporation
MSFT Microsoft Corporation

user02 Fran Futures
MSFT Microsoft Corporation
ORCL Oracle Corporation

Press any key to continue . . .

Any help would be awesome ..
 
Campbell Ritchie
Sheriff
Posts: 51349
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That code was dreadfully difficult to read because of the long lines. I have broken them so you can see how to do it, and also got rid of some unnecessary blank lines and commented‑out lines which do not aid legibility. Now you can see how to do it.
I shall move this discussion to our databases forum.

You cannot expect to debug SQL via such JDBC code. Run each of those lines individually at the command line to your database and see what happens. Then you can see from the select statements which column in the result set corresponds to the name of the stocks. And also which fields are filled in.
What does the bit about prevID mean? Are you trying to avoid the same user twice? Why are you doing that in Java® rather than letting the SQL do it with things like GROUP and DISTINCT?
Why are you catching Exception? Avoid catching plain simple Exception. You ought to be going for SQLException which has error codes and messages which will give you more information.
 
Ray mann
Ranch Hand
Posts: 39
Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
awesome thanks.. yes trying to avid the same user twice am very new to SQL so yeah useing what i know
 
Campbell Ritchie
Sheriff
Posts: 51349
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome
I presume you have a tutorial somewhere: if not try the Java Tutorials. My SQL is very rusty.
 
Ray mann
Ranch Hand
Posts: 39
Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
after many more jours of googling this is what i have , but im getting a SQL syntax error at runtime :

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in que
ry expression 'UserStocks.userID = Users.userID LEFT JOIN Stocks ON UserStocks.symbol = Stocks.symbol'.



Anyone know what im doing wrong?
 
Ray mann
Ranch Hand
Posts: 39
Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok so I found Access Forum that helped me figure out the issue with my Query now I need some help with the java side of the app so I am getting the wrong out put .... I want each user listed once with their all their stocks listed if that makes sense ... do I use a for loop or for each loop and how do I structure it?

Updaated Query:




Current output:

Stock holdings by User

User ID User Name
Stock - Description
-------------------------------------------
admin01 Default Admin
DELL Dell Computer Corp
admin01 Default Admin
MSFT Microsoft Computer Corp
admin01 Default Admin
ORCL Oracle Corp
user01 Bill Buyout
DELL Dell Computer Corp
user01 Bill Buyout
MSFT Microsoft Computer Corp
user02 Fran Futures
MSFT Microsoft Computer Corp
user02 Fran Futures
ORCL Oracle Corp
Press any key to continue . . .


desired output:

Stock holdings by User

User ID User Name
Stock - Description
-------------------------------------------
admin01 Default Admin
DELL Dell Computer Corp
MSFT Microsoft Computer Corp
ORCL Oracle Corp

user01 Bill Buyout
DELL Dell Computer Corp
MSFT Microsoft Computer Corp

user02 Fran Futures
MSFT Microsoft Computer Corp
ORCL Oracle Corp

Report Done.

Press any key to continue . . .




thanks again
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic