aspose file tools*
The moose likes JDBC and the fly likes Save a query to a string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Save a query to a string" Watch "Save a query to a string" New topic
Author

Save a query to a string

Brennen smith
Ranch Hand

Joined: Dec 29, 2010
Posts: 33
Hello I have used these forms to answer many questions but finally I have a question that I am unable to find the answer to. I am trying to query a database 3 records all in one row inside a table. Then take the results and save them to a String.

The table has 4 columns with data in them:
User1, Path, FileName, LastRun

The Query:


I basically want to take path + (date) + filename where user1 determines which row from the table to pull. I then want to simply save this query result all together to a string. The only way I have seen that this can be done is with a dataset. What us the easiest way to accomplish this?

Any help will be more than appreciated.
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
You can combine them together in your SQL statement and return them as a single column. It looks like your select is almost right. What database (Oracle, MySql, etc) are you accessing? Some require you to use '+' as the concatenator. Others require '||'.

FYI, 'filename' should not be in quotes as that would just return the string filename rather than the data in column filename. Also, getDate() returns the current date and you don't need to do a subselect. You should replace the subselect with LastRun. You will probably want to add white space between each column data.

Try getting the SQL correct in your database's command line tool like sqlplus, toad, or mysql before using JDBC.

Alternatively, you can return the three columns and combine them together in the Java code. It depends on why you need to combine the data. Some would argue that if it's just for display purposes, you should combine the three in the presentation layer of your code rather than the data layer (SQL). You specifically asked what is the easiest way. IMO, the easiest way is putting it in the SQL. But the difference is trivial. It's like walking to your mailbox to get the mail. Do you take 5 long steps or seven shorter steps?
Brennen smith
Ranch Hand

Joined: Dec 29, 2010
Posts: 33
Thanks for responding fast.

It is for a SQL Server 2005 DB.

So my query should look like something like this:
Select path + getdate() + filename From xreconnet Where user1 = 'BAL'
How do i specify the date format inside getdate()... getdate(12)??


So this way should combine them inside the SQL statement. Now the part I am still unclear on is how i take the query and return a string. Do I have to use a SQLDataAdapter and fill a dataset? or is their a easier way to go from query to string without filling a dataset and then transferring. Because unless I am mistaken since I already combine everything in the data layer it should only return one record making a dataset over kill.


I will include the code as it sits now so that you might better understand what I am trying to accomplish. Basically I am creating a DTS... SSIS package this code is part of a Dynamic Properties Task that creates a //path/(date)filename.csv based on today's date and the file being exported then exports the result to a Global Variable that is part of the Package. The code is incomplete I am missing the section where the query is executed and the result is (somehow) set to a string.

Again Thanks For the help!


Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18168
    
    8

Hmmm... so this isn't actually a Java and JDBC question. I'm not sure that knowledge of JDBC is going to help anyone who wanted to answer this, but on the other hand I'm not sure that moving it to the "Other Languages" forum (where it really belongs according to the rules) is going to be any better. I will leave it here for now (but if other moderators disagree, they should feel free to move it elsewhere).
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
I was going to ask about your dataset reference in your original post. I see now that you are not using JDBC. You are using .NET.

You don't have to use dataset but you can. I found using datatable is easier. (For the JDBC viewers out there, a .NET DataSet is just a collection of DataTables. They are pretty much interchangeable because they implement the same interfaces.) There may be a way to retrieve a single string in .NET code but I don't remember what it is so the only way I know how to do it is using a TableAdapter. Maybe look into how to call and get data back from a stored proc or function.

Yes, you will need to add convert or cast if you want the date in a specific format. Add that to your SQL. You will also have to add the path/file delimeters ('\'). You will need to add an "as MyColumnName" to your SQL so your VB code can query it from the dataset.

Also consider using prepared statements rather than directly building the SQL in your VB code. It's more secure (and easier once you add prepared statements to your personal toolkit :-)

But now that I understand your requirements better, I would go with just returning the individual columns and combining them in your VB code.
Brennen smith
Ranch Hand

Joined: Dec 29, 2010
Posts: 33
Ok so this is what I have now... I am getting an XML type error when using a DataTable


Anyone seen this error before...

Other than that I think it should work I am writing this inside BIDS and can not get the debugger to work correctly so I feel somewhat blind at the moment.

Anyways here is what I have now....
Brennen smith
Ranch Hand

Joined: Dec 29, 2010
Posts: 33
OK, i fixed the XML error...

From the vsa script environment select: project > add reference. then, choose "system.xml.dll". then, click the "add" button. then, click the "ok" button.

This resolved that issue. Now I am going to try and debug the program if I can get BIDS to cooperate
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
From the vsa script environment select: project > add reference. then, choose "system.xml.dll". then, click the "add" button. then, click the "ok" button.

For those JDBC viewers, the original poster was "missing a jar file on his classpath".

I question your code where you expect three rows to be returned. You should, of course, check the count of rows returned before accessing the data but your original SQL had a where clause that I thought was supposed to filter the data so that it returned a single row. The SQL code now will return every row from table xreconnet. Also you are ignoring the returned path. Is that intentional? Another suggestion is to use the column names when getting the data from the DataTable rather than using the ordinal column count.

You still need to add the path/file separator between your folders and file when building your string. Look at Path.DirectorySeparatorChar to do this in a platform independent way (hah!).
Brennen smith
Ranch Hand

Joined: Dec 29, 2010
Posts: 33


Ok so this is the table hope this will give you a better understanding of what i am trying to do. I assume there are only three rows because there are only three rows. The reason I was using the WHERE user1= was to make sure I were using the correct 'filename' now that i am pulling the entire table into the data table I didnt think i really needed it. so I assumed Trn would be row 1 CK = row 2 and BAL = row 3.

so basically the end goal is to pull the path and filename from each row, Add the current date, and set equal to a string so for example:
sValBal = \\jjmssrv007\Reconnet_test\POS and Bank Files\12292010M500BAL.csv
string = path + (date) + filename

Thanks for bearing with me.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18168
    
    8

I still find that confusing. It starts out sounding like you want to do a calculation on each row, but then at the end it sounds like you only want to have one string as the result of your processing. So what is it? Do you want one string per row, or one string for the whole table?
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
OK. I still question your column indexes as in:
Don't the columns begin with index 0? Also shouldn't "mmddyy" be "mmddyyyy"? And I think you can use:
which is more decriptive. (I may be wrong on the syntax.)
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
Also, you better add an order by to your SQL. Otherwise, you cannot guarantee the row order returned.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Save a query to a string
 
Similar Threads
Java I/O
Data retrivel from DB using Hibernate
JFileChooser text field
Update Table Nested Query (Using Function)
Handling Null Values In Jdbc