And it's better not to use "SELECT *". Use the SELECT real column names".

I've changed the code accordingly. I've also changed the select statement to only retrieve one column.
The resultset is still empty though, I can add that it works about 50-60% of the time and I'm trying to figure out why java ignores some rows in the database
where as it gladly accepts others ...

Just to be clear, when I execute these statements, depending on what ID I'm looking for java will either give me a resultset or it won't.
Where as the SQL Server Management Studio ALWAYS gives me a resultset ..

... and please don't change your original first post. Other people will now not understand what we were talking about.

Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16

posted Jan 05, 2011 06:46:12

0

Jan Cumps wrote:Have you committed all the inserts that you did in SQL Server Management Studio ?

I'm afraid I don't know what you mean, the rows have been in the database for at least a week and the problem will persist no matter if I restart the server or not.
The only "commit" I know of is the one in java where you basically execute the changes you've made through an 'update' or the like.

As far as I can understand the data I'm asking the database for is committed since it's there after I've restarted both the sql server and the windows 2003 server.

Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16

posted Jan 05, 2011 06:51:51

0

Jan Cumps wrote:... and please don't change your original first post. Other people will now not understand what we were talking about.

SQL Server Management studio auto commits by default. If you are not sure what "commit" means in a database context I would recommend you stop coding and take a bit of time to read up on database concepts. Life will be far less painful if you do.

It might be an idea to use the SQL Server profiling tool to make sure you are running the SQL you believe you are. It could be you are using different schemas or your statement is subtly different.

As another aside, this:

kind of defeats one of the purposes of JDBC. You might want to change that call to use a String read from somewhere.

Paul Sturrock wrote:SQL Server Management studio auto commits by default. If you are not sure what "commit" means in a database context I would recommend you stop coding and take a bit of time to read up on database concepts. Life will be far less painful if you do.

It might be an idea to use the SQL Server profiling tool to make sure you are running the SQL you believe you are. It could be you are using different schemas or your statement is subtly different.

As another aside, this:

kind of defeats one of the purposes of JDBC. You might want to change that call to use a String read from somewhere.

I don't know what purpose it defeats but after some googling.. I've changed the connection call. I know what commit means, it's the means of making a transaction permanent. I just didn't know what you meant with "Have you committed all the inserts that you did in SQL Server Management Studio ?" because I haven't done any inserts there I've imported the database from an MS Access database that has its input from a CRM software. So I really have no control over what goes into the database, I'm just trying to learn how to read the data that the crm software populates the database with. I obviously should have told you that.. so that's my bad.

It's been working for quite some time now though and for some reason it just stopped working...

Can you check the type of the key column in SQLServer. Maybe when importing it from Access, the type is no longer numeric.

What happens if you query: from table where KEY1 = '16775342' ?

Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16

posted Jan 05, 2011 08:27:26

0

Jan Cumps wrote:Can you check the type of the key column in SQLServer. Maybe when importing it from Access, the type is no longer numeric.

What happens if you query: from table where KEY1 = '16775342' ?

Things are just getting crazier for every test I do .. I have these larger SQL-statements that give me a resultset where as the simple select statement doesn't .....................

I'll try to explain, this larger statement basically:
1) fetches all the objects that belong to "today" and creates a temporary table and fills it with these objects.
2) the statement now fetches all the objects that belong to "me"(userID) out of this temporary table
3) now when we have all the objects that belong to me and the current date the statement will create new temporary tables for information about the attending entrepreneurs, insurance details, address etc, fetches the needed info and fills these temp tables.
4) to produce a resultset from these temp tables a simple select statement is made on each of them
5) drop all the temp tables

Now this larger sql statement doesn't give me any objects for today, even though management studio will, however it will give me objects for tomorrow..and the day after ..
Whereas the simple select statement I showed earlier won't give me anything, for any day ......

And you might be on to something about the type changing .. since both:
select * from table where KEY1 = '16775342'
select * from table where KEY1 = 16775342

works just fine in management studio, and makes no difference from java either. And when I look at the type in management studio
it says Key1(PK,int, not null) so it's supposed to be an integer, I don't understand why management studio nor java are complaining..
it sure does complain when I forget the '' for the Data field which is a (nvarchar(255),null)..

Where can I find an easy tutorial about the "sql server profiling tool" since I've never used it.

You say you get an empty ResultSet... but you would get the identical output from that code if an Exception were thrown, namely no output. You haven't eliminated that possibility, you've just completely ignored it. Replace line 35 in your original post by this:

Alexander Selling
Greenhorn

Joined: Apr 27, 2010
Posts: 16

posted Jan 05, 2011 10:42:14

0

Paul Clapham wrote:You say you get an empty ResultSet... but you would get the identical output from that code if an Exception were thrown, namely no output. You haven't eliminated that possibility, you've just completely ignored it. Replace line 35 in your original post by this:

I changed to jtds and it started working, I've also changed a chunk of other code so.. I really don't know what made it work.. I can't wait to actually take some courses in software engineering cause I'm obviously not the type that can learn how to develop software well on my own.. The downside of just reading math and physics the first year of college I guess..

So your code is working now? Here's a free software-engineering tip: it's still bad code if it ignores errors. Put in that line of code which notifies you of an exception, even if the exception didn't happen the last time you ran the code.