Rudy Dakota

Ranch Hand
+ Follow
since Jul 27, 2002
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Rudy Dakota

Hi Manzar,

Mark is right here. If you search on SQL Server and timestamps, you 'll find that these are not as precise as you 'd like. I am not sure about the exact magnitude, but I think it may be off by one third of a second. More detail can be found in the SQL Server docs, or on the internet.

Regards,

Rudy.
Hi Matt,

Not knowing why you 'd want to check before insert, let me suggest simply defining a unique index on artist name. Your RDBMS should allow for it, or it isn't worthy of the name. That 'll result in an error when you try to insert a duplicate. The error will obviously be wrapped in a SQLException and one of its members (something like SqlValue, form the top of my head) will give you the database specific error number. Just find out what 's the number for a unique index uniqueness vialotion, and you 're done!

The big advantage of this approach is that you 'll have best performance when everything goes the way it should, I certainly hope this will be the lion's share of your transactions. Only when an error does occur, additional code is executed.

Good riding,

Rudy.
Hi Paul,

Assuming we 're talking Oracle:

- ROWID is a semi-physical value specifying the location of a row (in terms of data file, extent, etcetera). ROWID is not very useful in day-to-day programming, as it is not a constant value (it changes with table re-organizations, imports, and things like that). Therefore it requires caution in its use.
- ROWNUM is a logical value for the sequence of row retrieval in the resultset. This is the position before sorting, so rownum is not always the same as the position in the resultset presented to you. This also requires some caution.

Hope this helps,

Rudy.
Hi guys,

Not based on any practical experience, but reasoning purely theoretical: using bind variables would make it impossible to take the detailed distribution of values over a given column into account when making the execution plan. The latter being one of the main advantages claimed for Oracle's Cost Based Optimizer.

I think you 'll find an advantage in using bind variables in the lion's share of cases anyway. It 's high on my list of interesting point to investigate without a business justification, nevertheless.

Good riding,

Rudy.
Avi, Gunjan,
A view is nothing but a select statement stored within the database. Views and queries should therefore not differ in the execution plan or the performance. One exception that comes to mind: views might be slightly more sensitive to sub-optimal formulation of search conditions, as the underlying query is abstracted from and thus likeley not known at all times. But that doesn't do away with the general rule: same execution plan, same performance.
Good riding,
Rudy.
Hi Carty,
You might want to share it with those among us that were puzzled by your initial post. Share and learn!
Good riding,
Rudy.
Hi Murali,
That 's just the way JDBC works: the ResultSet depends on the Connection. If you close the Connection, the ResultSet is closed as well. We, simple souls uisng the stuff, can try and understand the reasons behind this, but that effort will not change the facts. That 's what the JDBC specification said. Period.
Good riding,
Rudy.
Hi Alex,
Now that 's a different ball game, isn't it? Just now we were talking about browsing through a ResultSet, and already we 're out to delete records. That does require a bit more caution, obviously.
Clearly you 're on a very risky track if you try and delete a record that satisfies some criterion. There may be more than one, as you state. Some implementations of ResultSet allow you to delete given rows in the set, and commit this to the underlying database, but I never worked with it. I wouldn't know whether that 's a good solution or not. It seems to deal with your uniquiness problem, I would say.
Oh, by the way, I don't follow your remarks on the getRowNum() function. It seems you have a different idea of what it does. In this case I 'll join one of the regulars here in saying: 'Javadoc is your friend'. Stay late and you might meet him. He 's a bit talkative at times, but he might learn you a trick or two. Say hello from me when you meet him in the saloon.
Good riding,
Rudy.
Hi Vibha,
This sure is confusing. I tend to agree with Nick, but the explanation you provide also makes sense. I was about to write that the optimizer's workings may vary over products, but then I realized this is the Oracle forum . That 's no variable, then.
One thing you can do is simply trying: it shouldn't be that hard to create the table and do some tests, run explain plans for the statements, and see what the results are. That 'll do away with the confusion.
The other thing is: who 's the source of your alternative explanation? Is it an authorative source on Oracle, or could we question it? An assesment of the authorativeness might help .
Good riding,
Ruud.
19 years ago
Hi ALex,
Let me just chew on this for a while. A non-indexed table? Do I get you correctly to mean a table containing duplicate rows? If so, you can still index the table, but it will not be a uniqie index .
Anyways, I think you can easily get to a row's position in the ResultSet (getRowNum(), wasn't it). If you store that value with the other row data, you 'll always know what row you 're dealing with .
Good riding,
Rudy.
Hi Vinod,
You 're both right and wrong. What you describe is how the fetch size is supposed to work. But! If you were a driver builder, you are under no obligation to build your driver like that. The fetch size has no more status than a suggestion to the implementor. There is every freedom not to implement it and still maintain that you build a JDBC driver
For us programmers this simply means we can never trust on a driver to implement the fetch size if we haven't somehow checked. And even then, this works behind the scenes, so you 'd better be very sure the driver doesn't do some sort of pre-fetching as well
Good riding,
Rudy.
Hi Tony,
You can redefine your column names in view definitions. That might help in this case. They might also help out 'changing' table names by the way, the advantage being that views are standard SQL, whereas synonyms are not (I think).
Good riding,
Rudy
Hi there,
Sounds rather wierd to me. Especially this bit:

he can select business fields (with out applying his domain knowledge as to whether these fields logically relate or not) and he wants to get results


Your customer wants to be able to specify reports without knowing themselves whether these make sense? Next step they 'll be telling you your report is not 'correct'. Also 'with out applying his domain knowledge'? Wouldn't think so.
I 'd say it is either of two ways: or your customer applies his domain knowledge, or you 're free to combine data in sensible or senseless groups as you like.
Good riding,
Rudy.
Hi Bruce,
You 'll need to get rid of the ugly "select *". It 'll have to be something like this
.
Let me also suggest that you 'd do yourself a favour reading up on SQL, if you plan to do this sort of thing more often.
Good riding,
Rudy.
Hi there,
I 'll not be solving your problem but I have got two clues for you:
- looks like you got your dates stored in character columns You 'll need the TO_DATE function with a nice formatting string to make these values real dates (
- in Oracle SQL the difference between two date values is always a number of days, so you 'll have to multiple it by 24 * 3600 to get seconds.
Good luck,
Rudy.
19 years ago