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

Exporting database to a text file

 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Im fairly new to this, Everything is fine but the only problem is that the data that is exported from the database , (java derby)is incorrect. the text file has random letters for some reason;


 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your description of the problem isn't very helpful. Could you explain this "random letters" thing a bit more? Like for example showing us an actual example of the output along with an explanation of what you expected instead?
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And, welcome to the Ranch!
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:And, welcome to the Ranch!
yes, the actual output is 'ashj Enter Number : ' and i dont know why and how the text file contains this.
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Your description of the problem isn't very helpful. Could you explain this "random letters" thing a bit more? Like for example showing us an actual example of the output along with an explanation of what you expected instead?

I guess that OP is talking about unreadable characters he or she finds in the text file...Maybe an encoding problem?
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And in the resultset i defined i wanted the data within the first 2 columns, which are only names and numbers. I dont know why it doesnt send that data to the text file
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:And in the resultset i defined i wanted the data within the first 2 columns, which are only names and numbers. I dont know why it doesnt send that data to the text file

Instead of dumping data on a text file, try to output data to console (use simply System.out).
What happens?
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moreover, try to use "Select column1,column2 from contacts list" as query. I wonder if the second column is actually a number column, so that getString method retrieves garbage...
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please do not use SELECT * in your code. SELECT * should only be used in EXISTS() (because it is redundant anyway) and ad hoc queries. Always spell out the column names. This not only avoids bugs due to column addition, subtraction, renaming, and reordering, it is self-documenting too! And, if SELECT * is used to build a dataset where only the first two columns are used, it is wasteful to memory and storage, and can cause the RDBMS to use a slower query plan, while the other columns are retrieved and temporarily stored.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Claude Moore wrote:
dave walpole wrote:And in the resultset i defined i wanted the data within the first 2 columns, which are only names and numbers. I dont know why it doesnt send that data to the text file

Instead of dumping data on a text file, try to output data to console (use simply System.out).
What happens?
i tried this now, and still get the same output ? its blowing my mind
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Claude Moore wrote:Moreover, try to use "Select column1,column2 from contacts list" as query. I wonder if the second column is actually a number column, so that getString method retrieves garbage...
I tried this too, still gives me the same output. And the second column is varchar too and thats also inputted as string
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:Please do not use SELECT * in your code. SELECT * should only be used in EXISTS() (because it is redundant anyway) and ad hoc queries. Always spell out the column names. This not only avoids bugs due to column addition, subtraction, renaming, and reordering, it is self-documenting too! And, if SELECT * is used to build a dataset where only the first two columns are used, it is wasteful to memory and storage, and can cause the RDBMS to use a slower query plan, while the other columns are retrieved and temporarily stored.
Can you give me a specific line of code please, im not sure what else to replace with SELECT
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:Can you give me a specific line of code please, im not sure what else to replace with SELECT

The SELECT stays. The * should be changed into the exact column(s) you want to retrieve. Also, test the statement from the console, to see if it returns the data the way you expect it.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:Can you give me a specific line of code please, im not sure what else to replace with SELECT

The SELECT stays. The * should be changed into the exact column(s) you want to retrieve. Also, test the statement from the console, to see if it returns the data the way you expect it.
Yes, i have tried this again but still the same output...
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:
Brian Tkatch wrote:
dave walpole wrote:Can you give me a specific line of code please, im not sure what else to replace with SELECT

The SELECT stays. The * should be changed into the exact column(s) you want to retrieve. Also, test the statement from the console, to see if it returns the data the way you expect it.
Yes, i have tried this again but still the same output...

And what does running the query from the console produce?
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:
Brian Tkatch wrote:
dave walpole wrote:Can you give me a specific line of code please, im not sure what else to replace with SELECT

The SELECT stays. The * should be changed into the exact column(s) you want to retrieve. Also, test the statement from the console, to see if it returns the data the way you expect it.
Yes, i have tried this again but still the same output...

And what does running the query from the console produce?
  it produces this --  [ashj Enter Number : ] 
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:I tried this too, still gives me the same output.


Not random characters then. Anyway my theory at this point is that you are indeed copying the contents of the database table to the file, or the console, or wherever. In other words the table actually does have one row and the first two columns of that row actually do contain the data which is mystifying you.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
dave walpole wrote:I tried this too, still gives me the same output.


Not random characters then. Anyway my theory at this point is that you are indeed copying the contents of the database table to the file, or the console, or wherever. In other words the table actually does have one row and the first two columns of that row actually do contain the data which is mystifying you.
Well, there isnt any data such that within the database though? i only have 3 records as of now and contains real names and numbers, i do not undertsand why it doesnt also show all 3 of the records i have in the database too. I seriously do not understand why it will output something that is not within my database at all. like can it be hidden or something, even if why arent my other records of data not included
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:
Paul Clapham wrote:
dave walpole wrote:I tried this too, still gives me the same output.


Not random characters then. Anyway my theory at this point is that you are indeed copying the contents of the database table to the file, or the console, or wherever. In other words the table actually does have one row and the first two columns of that row actually do contain the data which is mystifying you.
Well, there isnt any data such that within the database though? i only have 3 records as of now and contains real names and numbers, i do not undertsand why it doesnt also show all 3 of the records i have in the database too. I seriously do not understand why it will output something that is not within my database at all. like can it be hidden or something, even if why arent my other records of data not included

To understand the behavior, we need to go step by step. The first step is to guarantee the data can be retrieved from the console. Only after that should we venture into your code. I did not see it so clearly from your reply: Were you able to query the data from the console using the same query that appears in your code?
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:
Paul Clapham wrote:
dave walpole wrote:I tried this too, still gives me the same output.


Not random characters then. Anyway my theory at this point is that you are indeed copying the contents of the database table to the file, or the console, or wherever. In other words the table actually does have one row and the first two columns of that row actually do contain the data which is mystifying you.
Well, there isnt any data such that within the database though? i only have 3 records as of now and contains real names and numbers, i do not undertsand why it doesnt also show all 3 of the records i have in the database too. I seriously do not understand why it will output something that is not within my database at all. like can it be hidden or something, even if why arent my other records of data not included

To understand the behavior, we need to go step by step. The first step is to guarantee the data can be retrieved from the console. Only after that should we venture into your code. I did not see it so clearly from your reply: Were you able to query the data from the console using the same query that appears in your code?
yes, but what i tried now is that i deleted all the records from my database now and tried to run the code and ouput to the console, as there isnt anything in my database, it still output the same result . How can it output something that's not even anywhere around my database?
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:
Brian Tkatch wrote:
dave walpole wrote:
Paul Clapham wrote:
dave walpole wrote:I tried this too, still gives me the same output.


Not random characters then. Anyway my theory at this point is that you are indeed copying the contents of the database table to the file, or the console, or wherever. In other words the table actually does have one row and the first two columns of that row actually do contain the data which is mystifying you.
Well, there isnt any data such that within the database though? i only have 3 records as of now and contains real names and numbers, i do not undertsand why it doesnt also show all 3 of the records i have in the database too. I seriously do not understand why it will output something that is not within my database at all. like can it be hidden or something, even if why arent my other records of data not included

To understand the behavior, we need to go step by step. The first step is to guarantee the data can be retrieved from the console. Only after that should we venture into your code. I did not see it so clearly from your reply: Were you able to query the data from the console using the same query that appears in your code?
yes, but what i tried now is that i deleted all the records from my database now and tried to run the code and ouput to the console, as there isnt anything in my database, it still output the same result . How can it output something that's not even anywhere around my database?

Are you sure it is connecting to the correct database and schema?
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, i double checked that , indeed it is connected to the correct database and set as default schema
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:Yes, i double checked that , indeed it is connected to the correct database and set as default schema

Which database is this?  I ask because i wonder if you need a commit on that delete. Or something related.

You can also try to write to the database from your script and see if you can see it from the console. We just need to verify they are looking at the same database.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:Yes, i double checked that , indeed it is connected to the correct database and set as default schema

Which database is this?  I ask because i wonder if you need a commit on that delete. Or something related.

You can also try to write to the database from your script and see if you can see it from the console. We just need to verify they are looking at the same database.
Its Java Derby , i had problems with adding and deleting from my coding , thats another topic... but i can insert and delete records in my database table which is why i wanted to test if the script is checking the database or not. 
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:Its Java Derby , i had problems with adding and deleting from my coding , thats another topic... but i can insert and delete records in my database table which is why i wanted to test if the script is checking the database or not. 

Oh, i did not realize Derby was a database. Silly me.

Based on what we have seen. That is, you deleted your records and can still see something from your code, i would suggest that you are not connecting to the same database.

Maybe some rudimentary tests will help. Try these two things:

1) Change the query to SELECT COUNT(*), see how many records it thinks there are.
2) Drop the table from the console and see if the script complains about it not existing.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, i did not realize Derby was a database. Silly me.

Based on what we have seen. That is, you deleted your records and can still see something from your code, i would suggest that you are not connecting to the same database.

Maybe some rudimentary tests will help. Try these two things:

1) Change the query to SELECT COUNT(*), see how many records it thinks there are.
2) Drop the table from the console and see if the script complains about it not existing.  

i tried changing the query as you told me and got an error ' java.sql.SQLException: The column position '2' is out of range.  The number of columns for this ResultSet is '1'
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:Its Java Derby , i had problems with adding and deleting from my coding , thats another topic... but i can insert and delete records in my database table which is why i wanted to test if the script is checking the database or not. 

Oh, i did not realize Derby was a database. Silly me.

Based on what we have seen. That is, you deleted your records and can still see something from your code, i would suggest that you are not connecting to the same database.

Maybe some rudimentary tests will help. Try these two things:

1) Change the query to SELECT COUNT(*), see how many records it thinks there are.
2) Drop the table from the console and see if the script complains about it not existing.


And i deleted the table and ran the  code, it didnt throw any exception and gave me the same result, but i have connected to the right database i checked this couple of times now how can this happen
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote: tried changing the query as you told me and got an error ' java.sql.SQLException: The column position '2' is out of range.  The number of columns for this ResultSet is '1'

To do that, you would have to comment out the retrieval of the second item. Or, to make it really easy, add a literal to the query:
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:And i deleted the table and ran the  code, it didnt throw any exception and gave me the same result, but i have connected to the right database i checked this couple of times now how can this happen

We'll need to get specific here. Did you "delete" the table or did you "drop" the table. The former removes all records but leaves it in place. The latter removes it from the schema.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote: tried changing the query as you told me and got an error ' java.sql.SQLException: The column position '2' is out of range.  The number of columns for this ResultSet is '1'

To do that, you would have to comment out the retrieval of the second item. Or, to make it really easy, add a literal to the query:
tried the code, and obviously it output i love code ranch but i re created the table and tried, "SELECT COUNT(*) Name, Number FROM Contacts_List" , however the it throw an exception, java.sql.SQLSyntaxErrorException: Column reference 'CONTACTS_LIST.NUMBER' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions. 
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:And i deleted the table and ran the  code, it didnt throw any exception and gave me the same result, but i have connected to the right database i checked this couple of times now how can this happen

We'll need to get specific here. Did you "delete" the table or did you "drop" the table. The former removes all records but leaves it in place. The latter removes it from the schema.
I deleted the table and now recreated it again
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:
Brian Tkatch wrote:
dave walpole wrote:And i deleted the table and ran the  code, it didnt throw any exception and gave me the same result, but i have connected to the right database i checked this couple of times now how can this happen

We'll need to get specific here. Did you "delete" the table or did you "drop" the table. The former removes all records but leaves it in place. The latter removes it from the schema.
I deleted the table and now recreated it again

Drop the table again, and run the query from the console. You should get an error. If you do see the error, try it from your code, and see if you get the same error.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:
Brian Tkatch wrote:
dave walpole wrote:And i deleted the table and ran the  code, it didnt throw any exception and gave me the same result, but i have connected to the right database i checked this couple of times now how can this happen

We'll need to get specific here. Did you "delete" the table or did you "drop" the table. The former removes all records but leaves it in place. The latter removes it from the schema.
I deleted the table and now recreated it again

Drop the table again, and run the query from the console. You should get an error. If you do see the error, try it from your code, and see if you get the same error.
I dont get an error when i run my code even though the table is deleted now again. it produces the same output
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Should i just delete the database and re create it? Would that solve it somehow, because i believe the coding is correct
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:Should i just delete the database and re create it? Would that solve it somehow, because i believe the coding is correct

We're just trying to verify beyond a shadow of a doubt that they are both connecting to the same database, which does not seem to be the case. If dropping the databse is easy enough, go ahead, but run the code before you re-create it. If you do not get an error, it is obviously connecting elsewhere.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
dave walpole wrote:Should i just delete the database and re create it? Would that solve it somehow, because i believe the coding is correct

We're just trying to verify beyond a shadow of a doubt that they are both connecting to the same database, which does not seem to be the case. If dropping the databse is easy enough, go ahead, but run the code before you re-create it. If you do not get an error, it is obviously connecting elsewhere.
How can it connect to a different database when i specify the path and etc . I dont understand this but i will update when i create the database again
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dave walpole wrote:How can it connect to a different database when i specify the path and etc .


You're "specifying the path" in this way:



In this case you're specifying a path relative to the current working directory. So you might have one version of the database table in a directory which contains what you think it contains, but another version in whatever the current working directory is for that code, and that other version contains the crap data. How about if you specify the full path instead of what you have?
 
dave walpole
Greenhorn
Posts: 25
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
dave walpole wrote:How can it connect to a different database when i specify the path and etc .


You're "specifying the path" in this way:



In this case you're specifying a path relative to the current working directory. So you might have one version of the database table in a directory which contains what you think it contains, but another version in whatever the current working directory is for that code, and that other version contains the crap data. How about if you specify the full path instead of what you have?
You were right , i made such a rookie mistake, i had the database in 2 folders, one was within the working folder and the other was in my database folder which i have other databases in, i connected to my databases folder rather than the working one. I realised it when i recreated the database again, and now its fully working , and that 'Enter number : ' was from my JLabel in my add class which was meant to get it from a JTextField instead of JLabel. lol Thank you for enlightening me i appreciate for your help Paul
 
Paul Clapham
Sheriff
Posts: 21572
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good to hear you got that sorted out. So Brian was right all along ("You got two versions of the database floating around"), it just took a while to figure out where they were.

Rookie mistake... sure, but not as bad as the time I deleted the production version of the corporate sales analysis database at work.
 
dave walpole
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Good to hear you got that sorted out. So Brian was right all along ("You got two versions of the database floating around"), it just took a while to figure out where they were.

Rookie mistake... sure, but not as bad as the time I deleted the production version of the corporate sales analysis database at work.
Everybody makes mistakes, small or big , but yours is hell of a big one . Sorry to hear that to be honest
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic