Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

I need the to link two tables but I'm not sure where to start.

 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I have 2 tables in PSQL v10 and I need to join them?  or just add another column somehow?

I'm very new to Tables and SQL/PSQL/JDBC
It is highly likely that I'm doing something in a more complicated way than I need to base on ignorance.

ITEMRECORDNUMBER is how I would join them.
table 1 is JRNLROW, it has QTYORDERED,ROWDESCRIPTION,UNITCOST,POSTORDER,ROWNUMBER,ITEMRECORDNUMBER, plus many more I don't need.
table 2 is LINEITEM, it has ITEMRECORDNUMBER, and PRICELEVEL1 that I need.


what I really want would be something like   "select QTYORDERED,ROWDESCRIPTION,UNITCOST,POSTORDER,ROWNUMBER,PRICELEVEL1 from table.JRNLROW, table.LINEITEM"  joined by ITEMRECORDNUMBER


I thought I was almost done with my project, but then I realized that I only had UNITCOST and not PRICELEVEL1
This is what I've been using it does not have LINEITEM Table, or PRICELEVEL1.


 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Rawley wrote:what I really want would be something like   "select QTYORDERED,ROWDESCRIPTION,UNITCOST,POSTORDER,ROWNUMBER,PRICELEVEL1 from table.JRNLROW, table.LINEITEM"  joined by ITEMRECORDNUMBER


You already know what column is common to both tables. So you just write that out. Your current sql posted didn't use the 2nd table hence the PRICELEVEL1 column.
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not really sure what you mean.  I tried

query =  "SELECT CAST(QTYORDERED AS DECIMAL(10,0))AS QUANTITY,ROWDESCRIPTION,CAST(UNITCOST AS DECIMAL(10,2))AS COST,POSTORDER,ROWNUMBER,ITEMRECORDNUMBER, PRICELEVEL1, FROM LINEITEM,JRNLROW WHERE POSTORDER = "+postOrder+" and ROWNUMBER >0";

but it doesn't work.


or did you mean write it out with "join" or maybe inner join?
thanks again.
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try:

 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got  [LNA][Pervasive][ODBC Engine Interface]Error in expression: ITEMRECORDNUMBER
java.lang.NullPointerException

I took ITEMRECORDNUMBER off my original select and then it locked up when I ran it.   I don't really need ITEMRECORDNUMBER other than to get PRICELEVEL1.
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
and then it locked up when I ran it

meaning it never returned control to the command window?

There is on more change you should make.  I'm assuming POSTORDER is a String:


That's POSTORDER <space> = <space> <single quote> <double quote> + postOrder + <double quote> <single quote> <space>
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


POSTORDER is int;    but my full query is a String.   Thanks
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did the JOIN work?
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


gets
java.sql.SQLException: [LNA][Pervasive][ODBC Engine Interface]Error in expression: ITEMRECORDNUMBER
[LNA][Pervasive][ODBC Engine Interface]Error in expression: ITEMRECORDNUMBER
java.lang.NullPointerException
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you run these queries in the database directly, rather than via your Java code.
It's never a great idea to try and debug SQL issues via JDBC calls.

Can I ask why you're casting those columns?
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you try:
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


locked  up. 
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm casting because I wanted my tables to only have 2 decimal places.  I'm sure that I could do it from Java, but as is, I'm making my table from the resultSet of that query.
Then I hide the columns I don't want to see.


I have read that I should use render options on the table and not on the query, but so far I've never done that.

Thanks.

 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you use the Pervasive Control Center (PCC) to do your queries?

What does "locked up" mean?  Java became unresponsive?
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes the java program locks up.

I'm using Peachtree 2010 (like quickbooks)

I tried to find PCC but could not
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you're going to need to find a way to do this using a db front end.

That way you can build a functioning query before trying to add it to JDBC.

How big are the tables?

Is ROWNUMBER a db keyword for that database?  I know in Oracle you get wrong results if you attempt to filter on ROW_NUM at the same level on the query (ie ROW_NUM is not from a subquery).
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why don't we try a simpler query to find out what's causing the problem:
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


"locks up"
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've been looking for a db front end but still can't find one.


Also I'm not sure if this would matter, but I am connected as read only.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you sure it's the query that "lock's up"?
How do you determine that?
What does the code look like where you execute this?
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is my gui button action
jdbcRS.setRsForOnePO(postOrder)   //   sets the command up to run with the PO# provided.
dbTable =  DbUtils.resultSetToTableModel(jdbcRS.runQuery());   //   runs the query that was set from the above command.  then sets up tablemodel




this is the database
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, but I don't see anything in there that would show exactly where it is getting stuck.
There ought to be some more logging to track exactly where it stops working.
Yes, it possibly is the query, but that's still an assumption, and one that is going to be hard to test without logging as there doesn't seem to be a DB GUI you can use to try the query directly.

One thing, you don't need to import the pervasive specific classes.  Just use the java.sql ones.

It could be the connection causing the issue.

Ah!
You don't close anything.
Does the connection ever work?
Does the other query work (setRsPurchaseOrders)?

There's several options now, but some more logging (like lots and lots) might show up the issue.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In fact I'd bet that's it.
You are going to end up with multiple open connections, statements and result sets.
If the db you are hitting cannot handle that (and if this is hitting something like Sage it could well be the case) then may well lock up on the connection.

If this is a desktop app then you could create a single connection and re use it.
But you still need to close your statement and result set objects.
You shouldn't be passing result sets around, you should do the query and convert the results into a model that you then use elsewhere in your code.
This prevents db stuff leaking into the rest of your app.
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I run these two


I can go between the to all day without an error.       Search for a PO  -> select PO(Shows Items)  then I can Search for PO -> select .......
until I join it seems to work correctly.

Also I thought that if you put your connection in the try area that you didn't need to close it.    ( that does not mean I did it correct )
each time java.sql.ResultSet runQuery() is called I thought It would close it by itself.  

Thanks again for all the help.


On a side note, maybe I'm going about it wrong.
I might be better to take the results from the second query and pass the results of each ITEMRECORDNUMBER, but how would I combine that to my existing table.
 
Tim Rawley
Ranch Hand
Posts: 34
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also I thought that if you put your connection in the try area that you didn't need to close it.    ( that does not mean I did it correct )
each time java.sql.ResultSet runQuery() is called I thought It would close it by itself.   

To get the autoclose to work in a try/catch, you need a different syntax:
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, but you still need to stick some logging in there to show exactly where the issue lies, otherwise you are guessing as to the cause.

Also, if you do auto-close the connection you will lose your ResultSet, so you have to convert that into a model before then, which means your execute method needs to return a model, not a ResultSet.
Which means you will not be using a single query method.

A standard DAO takes the form (using an interface):

It doesn't pass ResultSets out, and it doesn't try and shoehorn all queries into a single method.
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think this should close better.    First is GUI buttons next is DB Connection
I run the query first to set the ResultSet then use  getRS() to return the last ResultSet.

As far as logging, I'm not sure how to do that other than getting another program that I can run just the query on.  I've tried a few but haven't found one that works for me yet
Thanks again for all the help.






 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
now passes TableModel and closes.



GUI
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is everything running smoothly?
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Regarding logging, you can just you sysout calls like you are doing currently in your Exception catch blocks.
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it is currently running smoothly, but that is without the join.
With the join it does the same old Java lockup.
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I went for the workaround.  It seems to work fine, but takes about .5 second per item.   Most of our PO's have under 20, so not to bad.





Thanks again for all the help.
 
Tim Rawley
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
bottom should have been.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic