• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

JList Remove Selected and SQL Query Results

 
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey all,

Today I have not one, but two questions.

First I would love You to show my software on picture so You can easier get point.



In JList I have paths to my MS-SQL scripts. With double click on it text has been readed to JTextArea1 (Some SQL code).

The execution is done without any problems.

JTextArea should show any results or errors and this is my first problem.  (Like "Query executed successfully", "Table XXXXX Altered", "Table XXXXX Created","Table XXXX doesn't exist".
I know there is a way to show specific results for specificied query in code but my querys are always loaded by user and are always random so it can't be fixed in code.
I show errors with try\catch block but there are too much that I don't need.


Second problem - Like You see in JTextArea1 I have path to script (planty of them actually). You can notice my "Remove Selected" button too.

Lets say that script 1 do "Select * from coderanch" and script 2 do "Select * from mysqleditor".

When I remove selected it sucessfully remove it from the selected script (lets say script 1) from the list.
But now script2 has "Select * from coderanch" when I load it. I think I messed index for deleting or reading scripts somewhere?
My code for delete script is:



 
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Issue 1: You could use the exception class's getMessage() method to retrieve the short message.

Issue 2:
Are you using a ListSelectionListener ?
Did you try using list.updateUI() method?

The solution depends on how you are selecting list items. When you delete an item (or element) in the list, the item gets removed and the next item gets selected. The remove action can have the logic to select the next item and then display the script text (or description).

For example, the list has:
script_1 - SELECT * FROM EMPLOYEES;
script_2 - TRUNCATE TABLE EMPLOYEES;

When script_1 is selected "SELECT..." is displayed in text area 1. When script_1 is deleted, the Remove Selected button's actionlistener gets executed. This action listener can have logic to:
- delete the script_1 item from the list
- clear the text area 1
- select the next list element - the script_2
- display the script_2's description ("TRUNCATE...").

Hope this helps.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank You.

Yeah, for errors that may be solution, but what if I want to show results and other SQL messages?

Well I am missing last three steps than I guess:

- clear the text area 1
- select the next list element - the script_2
- display the script_2's description ("TRUNCATE...").

I am not using ListSelectionListener as I find out that this suit me better:



Now I have to add "Enter" action that can be able to select item in JList as double click that is working now.
 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For the ENTER action you can use the KeyListener.

And, you can look at this example I had written sometime back about using a JList and a JTextArea and the interaction between the two components (like adding to the list and removing an element from the list, updating the text area, etc..): http://www.javaquizplayer.com/examples/notesapp-using-swing-h2database-JPA-example.html
 
Rancher
Posts: 3324
32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Did you try using list.updateUI() method?



No you should NOT be using the updateUI() method. That method is invoked internally by Swing on a LAF change. You are not changing the LAF so there is no need to use that method.

Changes should be made to the ListModel and the list will automatically repaint itself.

See the ListDemo example from the Swing tutorial on How to Use Lists, for a working example of this approach.

For the ENTER action you can use the KeyListener.



Don't use a KeyListener. Swing was designed to be used with Key Bindings. This is how all the default Actions of Swing Components are implemented.

Check out List Action. Its shows a simple implementation that will invoke an Action when the Enter key is pressed or the mouse is double clicked.

Today I have not one, but two questions.



In the future, one question per thread. It gets too confusing trying to have a conversation with two topics at the same time.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am working with the JList model, I am not working directly with the list.

Yeah I am sorry about 2 questions in 1 post, just didn't want to open too much threads.

Anyway, back to problem:

I go to SSMS and enter command



As only one record exist in that table with F105PIPZ = '3' result is: "(0 rows affected)"

I do same command within my software and I am catching exception with:



Now it prints error (I tried with GetMessage and SQL but it doesn't return nothing):



Ok, now I try to  do command:



Yeah, that line does exist in the record.

Now when I execute that command with my software results are:



I checked in my database and SQL command went successfully but I get that error anyway.

It's strange that I am not able to get results of SQL Query in NetBeans also.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I Guess I am getting this message when SQL QUERY is done successfully, so solution would be to find:



And replace it with: "Query has been completed successfully!"?
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you show us the code that executes the entered queries?
Because whatever code it is it seems to be thinking that has a ResultSet expected, which isn't the case iwth a DELETE operation.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Can you show us the code that executes the entered queries?
Because whatever code it is it seems to be thinking that has a ResultSet expected, which isn't the case iwth a DELETE operation.



This is part of code that execute SQL query:

                                 
 
Sheriff
Posts: 7126
185
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

That's going to cause an error with an SQL command that doesn't return a ResultSet, like DELETE.
 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ResultSet rs = stmt.executeQuery(sql);

As Dave Tolls mentioned delete doesn't return a result set. You should use executeUpdate instead.

From Java API: executeUpdate executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If this system needs to handle any SQL entered in the front end then I suspect some parsing is going to be needed.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well we work alot with SQL Queries to maintain our database and do some software updates (If we need new table, data, or something).
Sometimes we have around 300 .sql files and You have to click on every single one to open it and execute it.
You can't imagine how time we are losing (Or you can).
We usually execute .sql on the test database (exact copy of main database) then we execute .sql on main database. ~600 .sql to execute
Now that is for MSSQL, we have ORACLE database too...

Anyway this software should load all queries that has to be executed and execute one by one (remove one that executed successfully) and if find any error stops there and let you fix it.

So, for now everything seems to work but this, this seems to be biggest problem to solve for now.

Ofcourse, if somebody has same problem I'll be happy to share source with him.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Prasad Saya wrote:ResultSet rs = stmt.executeQuery(sql);

As Dave Tolls mentioned delete doesn't return a result set. You should use executeUpdate instead.

From Java API: executeUpdate executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.



This work as a charm. Thank You.

Last thing is cutting error to not show everything.

Example:



SQL Error:



JAVA Code:



JAVA Error:



 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try getMessage.
I think that should hold just the "Incorrect syntax near the keyword 'FROM'" part.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Try getMessage.
I think that should hold just the "Incorrect syntax near the keyword 'FROM'" part.



Keep getting same error:

 
Marshal
Posts: 28425
102
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, normally when the database server tells you there's an error in your SQL, you would look at the SQL, right? So is your software not designed to show you the SQL with the error? I'd work on fixing that if it doesn't.
 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is he SQL script you are trying to run?
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, that output is from a printStackTrace, so presumably you are still using that method (as in the code above)?
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Prasad Saya wrote:What is he SQL script you are trying to run?



This is Script:




Now I'm getting:



I tought I am not storing my query well in my String variable than I try to remove storing at all and I do:



Now I do:



Everything printed fine and I tried to run query in SSMS.
So in SSMS everything works, and in JAVA I am getting errors.
 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am not familiar with SQL Server; but it looks like you have to execute sql statements as a batch. Look for something like JDBC execute batch on Google search.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As I posted same question both here (coderanch.com) and on stackoverflow.com somebody mention to me that I can't do that and I would love to apologize all members that I offend with that.
Anyway, I deleted my post on stackoverflow.com as I find this forum much better and more usefull to me.

My problem is still not solved and this is not last message from me here I just want to thank You all for Your time and help.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm a bit lost now.
Are you trying to run those SQL statements one at a time, or are you trying to run them all at once?

What is the query being run by this line?


If it's all the stuff in that block of statements then that will not work in JDBC.

What exactly is it you are trying to do?
What are your requirements?

Earlier you asked about getting the response back for individual statements, but the above looks like you are trying to run dozens of statements at once.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well sometimes SQL Query will have 1 line of code and sometimes more.
I am not sure how this connection between SQL and JDBC works, I was thinking that JDBC send SQL query (In my case array of strings) and than SSMS is executing line by line.
I see now that I have to send line by line to SSMS and I have to find the way to read line by line from JPane and send it to SSMS with:

 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The GO command of sql server and its usage: http://www.sqlines.com/sql-server/sql_batch_go

You need to figure how to create batches of queries and how the GO affects the way the batched sql statements will be executed.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well this is waht I do, I saved JPane to .txt file.
After that I am reading line by line and sending it to SQL, if it finds "GO" just skip it.




Problem is if I have:



Code will now read line by line and will try with:



But that just won't work without whole code.

Does anybody have some idea to fix this?

Maybe to write in SQL Queries "GO" between break points, and read from "GO" to "GO" but that is not great solution.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why is spliiting that text on GO incorrect?
That does seem to be the delimiter.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Why is spliiting that text on GO incorrect?
That does seem to be the delimiter.



Well it is not wrong to wirte GO as delimiter.

But I am not the one who is writing the SQL scripts so the worst problem is teach 40 people to use GO as delimiter.

And if somebody forget to write GO as delimiter script is breaking down and writing errors.

That is why it is not greatest solution.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, so this goes back to exactly what your requirements are.

In detail.

As this is starting to look like something that was defined on the back of a beer mat and not analysed properly.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are not far from the truth.

I still need to find way to send SQL query properly from JAVA to SSMS.

Maybe I should consider to switch this software from JAVA to C# as C# should be more native with MS-SQL but that will take too much time and work?

 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I also tried with:



Error:

 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think the GO command need to be studied and list out the use cases. Then, work how these cases can be handled within this application.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well I append all lines to one row and that does indeed work with SQL SSMS.



Now I'm getting java.lang.NullPointerException and have to find out where.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I still think the requirements need to be defined.
Because if this is supposed to take in any old stuff from the user then there's likely to be more work involved.

Are they expecting to run actual SELECT queries for example?

As for the exception, the stack trace will tell you exactly where it occurred, from which you should be able to determine what is null.
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:I still think the requirements need to be defined.
Because if this is supposed to take in any old stuff from the user then there's likely to be more work involved.

Are they expecting to run actual SELECT queries for example?

As for the exception, the stack trace will tell you exactly where it occurred, from which you should be able to determine what is null.



Well I strongly agree for requirements that need to be defined.

I was thinking to allow (Well not allow but to be able) run actual SELECT also but as I can't get results back from random queries who are not fixed in code and as we are usually not working only with SELECT as SELECT is for management purpose (to see and discover something) I will "throw it out".
 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well here is the code that finally works. Now I will find out the way to display errors correctly. Thank You all!

 
Gaios Augustus
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I just wanted to post solution for catching errors in SQL, with this code everything is working:



Don't forget to call it in catch block:

 
Prasad Saya
Rancher
Posts: 517
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Good to know you are finishing your code without any issues   . While posting the code make sure its properly indented (and do remove the commented code). Also, if code comments (saying what it does) are placed strategically helps others to read the code and respond with a comment or a suggestion.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic