• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQLite & JavaFX: Select Query to Find Outstanding Stocks based on Stock Status with Inner Joins

 
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

i am trying to find the Outstanding Stocks to a Label and when User enters the Quantity to check if the Quantity is withing the Available Stocks for Selected Items.

for which i am trying to make a Query and failed. i have reached so far (Below Codes); Seeking Expert help from coderanch.com

This is the Code containing the Query:


Highlighting the SQL Query Below from Above Code:





Table Script for Products and Stocks is as follows, incase of Creating Table to Check the Example;



Thanks
 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ifraz Imanudeen wrote:
Below is the OUPUT ERROR for above;



org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ".": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.throwex(DB.java:886)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
at org.sqlite.core.DB.prepare(DB.java:227)
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)
at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235)
at loginfx.NewOrderController.LoadByProductName(NewOrderController.java:225)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8411)
at com.sun.javafx.scene.control.skin.ComboBoxListViewSkin.handleControlPropertyChanged(ComboBoxListViewSkin.java:179)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase.lambda$registerChangeListener$61(BehaviorSkinBase.java:197)
at com.sun.javafx.scene.control.MultiplePropertyChangeListenerHandler$1.changed(MultiplePropertyChangeListenerHandler.java:55)
at javafx.beans.value.WeakChangeListener.changed(WeakChangeListener.java:89)
at com.sun.javafx.binding.ExpressionHelper$Generic.fireValueChangedEvent(ExpressionHelper.java:361)
at com.sun.javafx.binding.ExpressionHelper.fireValueChangedEvent(ExpressionHelper.java:81)
at com.sun.javafx.binding.ExpressionHelper$Generic.fireValueChangedEvent(ExpressionHelper.java:361)
at com.sun.javafx.binding.ExpressionHelper.fireValueChangedEvent(ExpressionHelper.java:81)
at com.sun.javafx.binding.ExpressionHelper$SingleInvalidation.fireValueChangedEvent(ExpressionHelper.java:137)
at com.sun.javafx.binding.ExpressionHelper.fireValueChangedEvent(ExpressionHelper.java:81)
at javafx.beans.property.ReadOnlyIntegerPropertyBase.fireValueChangedEvent(ReadOnlyIntegerPropertyBase.java:72)
at javafx.beans.property.ReadOnlyIntegerWrapper.fireValueChangedEvent(ReadOnlyIntegerWrapper.java:102)
at javafx.beans.property.IntegerPropertyBase.markInvalid(IntegerPropertyBase.java:113)
at javafx.beans.property.IntegerPropertyBase.set(IntegerPropertyBase.java:147)
at javafx.scene.control.SelectionModel.setSelectedIndex(SelectionModel.java:68)
at javafx.scene.control.SingleSelectionModel.updateSelectedIndex(SingleSelectionModel.java:215)
at javafx.scene.control.SingleSelectionModel.select(SingleSelectionModel.java:149)
at com.sun.javafx.scene.control.skin.ComboBoxListViewSkin.lambda$createListView$323(ComboBoxListViewSkin.java:484)
at com.sun.javafx.binding.ExpressionHelper$Generic.fireValueChangedEvent(ExpressionHelper.java:349)
at com.sun.javafx.binding.ExpressionHelper.fireValueChangedEvent(ExpressionHelper.java:81)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$353(GlassViewEventHandler.java:432)
at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
at com.sun.glass.ui.View.notifyMouse(View.java:937)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$147(WinApplication.java:177)
at java.lang.Thread.run(Thread.java:748)

 
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't have a complete answer, but here are some issues that I can see:

   - For INSTOCK and OUTSTOCK, you probably want COUNT() rather than SUM()
   - StockStatus is an INTEGER, but you are trying to match to 'IN' and 'OUT'
   - Missing ON in JOIN statement
   - Its GROUP BY not GROUPBY
   - You are referencing a placeholder in your PreparedStatement (PIN.setString(1, ...)), but there is no placeholder defined
   - I don't think you can SUM two aliases as you have specified.  Why not just calculate INSTOCK - OUTSTOCK in your code?

 
Ron McLeod
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Likes 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I suggest trying to get your SQL statements working using the command line or workbench such as SQLiteStudio before integrating with the Java code.
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Something important to note is that it is very dangerous to use upper/lower case in SQL database table and column names. Not all SQL DBMS's handle mixed case the same way and some have very strange ways of dealing with mixed case.

In SQLite, I believe that mixed case is handled literally, so you have to use the exact same case usage all the time, and thus, for example, OUTSTOCK would not match OutStock. What I recommend actually would be out_stock as the safest name, but lots of SqlLite projects do use mixed case.

And ORDER BY is 2 words. ORDERBY is invalid.
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also GROUPBY is GROUP BY (which is what I think Tim meant).
 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you very much. Appreciate you help.
SUM() worked not the COUNT().

This worked with TextField but not with Tableview.

I whated to show the outstanding stocks in a column for each productcode in second column, tried many ways, doesn't work. All data gets displayed except outstanding stocks for all productcode shows only first rows outstanding stock

Below is my code, kindly help.


StockProductCode is the Tableview Column Id for ProductCode
AvailableStock is the Tableview Column id Outstanding Stocks


Ron McLeod wrote:I don't have a complete answer, but here are some issues that I can see:

   - For INSTOCK and OUTSTOCK, you probably want COUNT() rather than SUM()
   - StockStatus is an INTEGER, but you are trying to match to 'IN' and 'OUT'
   - Missing ON in JOIN statement
   - Its GROUP BY not GROUPBY
   - You are referencing a placeholder in your PreparedStatement (PIN.setString(1, ...)), but there is no placeholder defined
   - I don't think you can SUM two aliases as you have specified.  Why not just calculate INSTOCK - OUTSTOCK in your code?

 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you 👍

Tim Holloway wrote:Something important to note is that it is very dangerous to use upper/lower case in SQL database table and column names. Not all SQL DBMS's handle mixed case the same way and some have very strange ways of dealing with mixed case.

In SQLite, I believe that mixed case is handled literally, so you have to use the exact same case usage all the time, and thus, for example, OUTSTOCK would not match OutStock. What I recommend actually would be out_stock as the safest name, but lots of SqlLite projects do use mixed case.

And ORDER BY is 2 words. ORDERBY is invalid.

 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you 👍

Knute Snortum wrote:Also GROUPBY is GROUP BY (which is what I think Tim meant).

 
Marshal
Posts: 28177
95
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
That code from line 14 to 21: you execute it before the first row of the ResultSet has been read, so it should throw an exception. However at lines 29 and 30 you have code which runs regardless of whether an exception was thrown, in effect ignoring the exception. I don't think you should be doing that, unless it's really true that it doesn't matter to 29 and 30 whether the SQL was processed correctly or not.

And in line 24 you create a new TableModel for each row of the ResultSet and add it to... something. Not sure what the something is but I suspect that you maybe should have only one TableModel.
 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

You mean that i have you place the a within
Like below



If there was a way i can do the Calculation of and in the SQL Query itself, it would be very easy.



Paul Clapham wrote:That code from line 14 to 21: you execute it before the first row of the ResultSet has been read, so it should throw an exception. However at lines 29 and 30 you have code which runs regardless of whether an exception was thrown, in effect ignoring the exception. I don't think you should be doing that, unless it's really true that it doesn't matter to 29 and 30 whether the SQL was processed correctly or not.

And in line 24 you create a new TableModel for each row of the ResultSet and add it to... something. Not sure what the something is but I suspect that you maybe should have only one TableModel.

OStocks.JPG
[Thumbnail for OStocks.JPG]
Shown in the Image, that There is only One OUT of 3 on First Row, out of 30 which is 27 left, is Fine, but for Other Rows, its Incorrect, How do i achieve this.
DB-Browser-Stocks.JPG
[Thumbnail for DB-Browser-Stocks.JPG]
This is the Stock Table for your reference.
DB-Browser-Executive-SQL.JPG
[Thumbnail for DB-Browser-Executive-SQL.JPG]
This is which i tried to query
 
Paul Clapham
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, like I said already, you're doing that calculation of IN and OUT and Res before you start looping through the ResultSet. I'm surprised that doesn't throw an exception but at any rate since you only do that calculation once, you can't expect to see different values for it in your output.
 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:Well, like I said already, you're doing that calculation of IN and OUT and Res before you start looping through the ResultSet. I'm surprised that doesn't throw an exception but at any rate since you only do that calculation once, you can't expect to see different values for it in your output.



Isn't there a way to Calculate the the IN - OUT in the SQL Query itself.

Something Like




then i can assign  TableView Column

Please Help me.
 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:Well, like I said already, you're doing that calculation of IN and OUT and Res before you start looping through the ResultSet. I'm surprised that doesn't throw an exception but at any rate since you only do that calculation once, you can't expect to see different values for it in your output.



Will it Solve my problem, if i have Two columns in the Table for IN and OUT instead of doing the SUM with the StockStatus.
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I'm not mistaken, this:


Is doing it the hard way. Just do an inner join and the "where" clause won't be needed.

You can do just about any math you want in an SQL statement, subject to the constraint that each row is calculated separately, so every term in the expression either has to be one of the columns in the row being calculated OR it must be an aggregrate value for the entire selection such as SUM() or COUNT(). You can't look at column values from any other row while calculating.

Incidentally, we're being conservative in assuming that stock MIGHT have other states besides IN and OUT. Obviously if it can be only one or the other, a lot of simplifications are possible.

Also, this expression:
bothers me. Every SELECT you do is expensive and I think if things were arranged properly a single SELECT might be able to tally both in-stock and out-stock. But it's too early in the morning to think that hard.
 
Paul Clapham
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ifraz Imanudeen wrote:Will it Solve my problem, if i have Two columns in the Table for IN and OUT instead of doing the SUM with the StockStatus.



It might do that. But it would be very much simpler if you put the calculation of IN, OUT, and SUM inside the loop so that it was done for every row.
 
Ifraz Imanudeen
Ranch Hand
Posts: 41
Netbeans IDE MySQL Database Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ron McLeod


Paul Clapham


Tim Holloway


Knute Snortum



I got the solution from above people's help and managed to complete my project and able to deploy it to the client.

THANK YOU VERY MUCH GUYS and THANKS to coderanch.com

God bless you all...
 
reply
    Bookmark Topic Watch Topic
  • New Topic