|
yaoi prophet posted:Cursors are not evil, they are just vastly overused by people who aren't comfortable with implicit cursors or complex queries. Implicit cursors perform better and result in much cleaner code, and it's pretty rare to actually NEED an explicit cursor.
|
# ? Apr 22, 2008 10:52 |
|
|
# ? May 14, 2024 10:34 |
|
Ok, this MySQL problem has me stumped and maybe you guys can point me in the proper direction. I've got two tables: autos and dealerships I want to select all of the autos from the autos table, however I need to group all autos that are new by their dealership, make, model and year. So my result set should list every used car seperately, and have 1 row for every new car for each dealership grouped by make, model and year. To be honest, I don't even know where to begin to do this. The only way I can imagine doing this is with two seperate select statements, however I can't order the results properly if I have two seperate queries. Any help would be greatly appreciated if this makes sense whatsoever.
|
# ? Apr 22, 2008 22:01 |
|
Stephen posted:To be honest, I don't even know where to begin to do this. The only way I can imagine doing this is with two seperate select statements, however I can't order the results properly if I have two seperate queries. Why can't you? Just UNION the two resultsets together and order on that.
|
# ? Apr 23, 2008 00:12 |
|
Stephen posted:Ok, this MySQL problem has me stumped and maybe you guys can point me in the proper direction. Can you provide the column names for the autos and dealership tables? Also, how do you determine if car is used or new? What you want as output also is not very clear - do you want a list of all cars, new cars first, then used cars with the new ordered by make model and year and the used ordered by make model and year?
|
# ? Apr 23, 2008 14:30 |
|
Sorry I was going to post a reply yesterday, but the forums were unavailable. I actually just read up on UNIONs and I think that should work properly. I wasn't previously aware of their existence. Thanks for the heads up!
|
# ? Apr 23, 2008 14:43 |
|
From your description it sounds like there may be a better way to write the query than using an UNION. If you can post the information that Puddleboy requested above then someone here may be able to suggest an alternate method that's more efficient. If you have to use a UNION you should look at the difference between UNION and UNION ALL.
|
# ? Apr 23, 2008 18:56 |
|
Sure, here goes The relevant table data: table: autos columns: - id - dealership_id - make_id - model_id - auto_year - status_id The simplified version of the query I wrote: code:
Stephen fucked around with this message at 21:01 on Apr 23, 2008 |
# ? Apr 23, 2008 20:47 |
|
genericadmin posted:It won't affect the data at all, just what characters are used to build the fulltext index. Remember that '+' is a boolean operator also, and that increasing the indexed character set will increase the index size and lower performance. We have everything set to UTF-8, and there doesn't seem to be a UTf-8.xml or anything of the sort. I can only find one other person who complained about this. Do I have to change charsets? is their some reason there is no UTF-8 XML file? I have only found people complaining basically, but no resources. If the docs hard to wade through, and the search engine on there website not that great. I think it is using full text.
|
# ? Apr 23, 2008 23:27 |
|
Anyone know a way I can make a procedure/function in Oralce that will allow comparisons of any two objects of the same type?
|
# ? Apr 25, 2008 04:20 |
|
Not sure what you mean by that, could you give an example of what you want to do?
|
# ? Apr 25, 2008 13:04 |
|
yaoi prophet posted:* COMMIT or ROLLBACK in pl/sql code:
|
# ? Apr 25, 2008 13:50 |
|
Do not ever, under any circumstances, commit or rollback in your procedure. Doing so ruins the whole transaction model of Oracle, which is based on the idea that an entire set of operations can be transparently undone at the client level. If individual procedures decide to commit/rollback at their own time, it literally breaks the integrity of the client's transaction. Remember that when you commit or rollback, you are deciding to commit or rollback the entire transaction. Take for example, the following: code:
In short, a procedure does not have enough information, ever, to decide if it's ok to either commit or rollback. That is up to the client program. The only reason I've ever put commit in pl/sql is when I'm writing a utility script for maintenance, where it's never intended to be used as a component in anything (such as doing a mass data migration as a one-off script). It's still a bad practice, but it's a one-off. If you're doing a test case for something (which it appears you might be) then do what you want, it's not going to incur the wrath of the Oracle gods or anything. Just be advised that it doesn't belong in actual production code anywhere. Edit: Personally, I think the keywords COMMIT and ROLLBACK should not even be allowed in pl/sql code. They just don't belong. If you really have the need (like with a one-off script, as is sometimes needed), you should have to run it through EXECUTE IMMEDIATE, just to drive home how gross it is to be doing it. npe fucked around with this message at 14:44 on Apr 25, 2008 |
# ? Apr 25, 2008 14:39 |
|
yaoi prophet posted:Do not ever, under any circumstances, commit or rollback in your procedure. Doing so ruins the whole transaction model of Oracle, which is based on the idea that an entire set of operations can be transparently undone at the client level. If individual procedures decide to commit/rollback at their own time, it literally breaks the integrity of the client's transaction. Does this apply to C# development (with SQL Server)? Our stored procedures are full of CREATE/COMMIT/ROLLBACK trans.
|
# ? Apr 25, 2008 15:01 |
|
It applies to anything that uses Oracle, it's always the job of the client program to manage the transaction. I can't speak for other platforms, if you're using MS SQL I don't know if it's considered more acceptable or not (although it seems sort of suspect to me if it is, but I don't know). I seem to remember reading somewhere that older versions of SQL server had issues where certain types of things (like code in triggers) didn't necessarily operate as part of the actual transaction, so you couldn't count on a full rollback being transparent. It wouldn't surprise me if this has created a culture of people who are just used to managing the transaction all over the place even if it's no longer necessary. Victor probably knows more about that. npe fucked around with this message at 15:06 on Apr 25, 2008 |
# ? Apr 25, 2008 15:04 |
|
Is there a way to limit the scope of a transaction to some block that you've defined yourself? Would it be acceptable to use ROLLBACK in a procedure in this case? Say... code:
code:
Barrackas fucked around with this message at 15:13 on Apr 25, 2008 |
# ? Apr 25, 2008 15:11 |
|
Yes, you are talking about an autonomous transaction. The problem is that 95% of the time, autonomous transactions are still a very bad idea. They're less of a problem than outright committing or rolling back, but it's very possible to create situations where you've lost data integrity. The most accepted use of an autonomous transaction is for auditing. For example: code:
But again, you need to be very careful. If you find yourself writing business logic inside of autonomous transactions, you've probably done something wrong to get there because you are still stealing control away from the calling program. If you think you have a situation where it's necessary, it might be worth posting some example code and discussing the more correct form it should probably take. To take your example and improve it a little, it would probably look something like... code:
npe fucked around with this message at 15:42 on Apr 25, 2008 |
# ? Apr 25, 2008 15:21 |
|
Well, I'm investigating the most appropriate way to convert an MS-SQL DB over to Oracle, and autonomous transactions are the best analogy I've found so far for mirroring the existing TSQL logic. For example, this is a common pattern in the existing code...code:
code:
I appreciate that it is probably not a great approach, but this is such a huge undertaking as it is, that moving transaction handling completely to the calling client is probably infeasible unfortunately. Barrackas fucked around with this message at 15:55 on Apr 25, 2008 |
# ? Apr 25, 2008 15:51 |
|
Autonomous transactions are probably the closest thing here.Barrackas posted:I appreciate that it is probably not a great approach, but this is such a huge undertaking as it is, that moving transaction handling completely to the calling client is probably infeasible unfortunately. If you change "probably not a great approach" to "a terrible thing that should never be done and will get you yelled at by any Oracle developer worth his pay", I'll agree. I mean, I understand sometimes people have to do things due to the reality of their job situation and everything, so I'm not unsympathetic. But on the other hand, this is sort of a glass bottle or old shoe kind of question.
|
# ? Apr 25, 2008 16:03 |
|
Heh, true. Thanks for your input on this, it's been very helpful. To be honest, I don't have a good enough overview of this task yet to make any concrete judgement on which approach will be more appropriate, but I will definitely take your advice into account when it comes to decision time. Cheers again!
|
# ? Apr 25, 2008 16:17 |
|
One thing to consider, is maybe looking into using savepoints instead of autonomous transactions. They allow you to control rolling back small chunks of the transaction, without breaking the overall transaction. Here's a quick demo: code:
npe fucked around with this message at 02:27 on Apr 26, 2008 |
# ? Apr 26, 2008 02:23 |
|
Cool, I had looked into savepoints a little bit, but your example is the closest match to the behaviour I'm after that I've seen so far. Let me see if I've understood the Oracle transaction model correctly; Transactions should be completely managed by the calling client, and stored procedures / triggers should never attempt to take matters into their own hands with COMMITs and ROLLBACKs. Doing so can possibly invalidate your data's integrity (from the client's point of view). Savepoints are OK to use in stored procedures though, because the scope of the ROLLBACK TO statement is explicitly defined within the stored procedure itself, and so the proecure will not do anything to the transaction that the client is not expecting, thus maintaining data integrity from the client's point of view. Does that make any sort of sense?
|
# ? Apr 28, 2008 10:41 |
|
That's pretty much it. One additional thing to note with Oracle is that unhandled exceptions that propagate out of the top level of pl/sql and are returned to the client, are automatically rolled back to the start of the block being called. This is referred to as an implicit savepoint. An easier way to describe it is to imagine that your client automatically has all calls wrapped in code:
An interior call that raises an exception that is handled won't implicitly do this. See: http://www.psoug.org/reference/exception_handling.html
|
# ? Apr 28, 2008 12:19 |
|
That's a really useful link, thanks. One point that I'm not clear on though - you mention that interior calls do not implicitly do the ROLLBACK stuff if bad statements are encountered, but I've found that they do. I've probably misunderstood what you were getting at, but if I have two procedures, as follows;code:
code:
Putting exception handling into SUB_THING results in what you'd expect, rows with 1,2 and 5 and an exception thrown. Thanks again mate, your advice has been an immense help.
|
# ? Apr 28, 2008 17:05 |
|
I wasn't really clear, all I meant was that there is no implicit savepoint set before blocks inside of the "outermost" one. For example:code:
|
# ? Apr 28, 2008 18:09 |
|
Which of these is the "canonically best" way to represent tags (e.g., for blog posts, etc.) in a relational DB? Option #1 Delimited text field in the Posts table in the form of "/tag1/tag2/tag3/". Adding a tag appends "newtag/" to the field, looking up posts by tag is "SELECT * FROM Posts WHERE tags LIKE "%/newtag/%" Option #2 Tags table (tagname|postid) for m:n relation with Posts table. Adding a tag is "INSERT INTO Tags VALUES 'newtag', <postid>", looking up posts by tag is "SELECT * FROM Posts WHERE postid IN SELECT postid FROM Tags WHERE tagname LIKE 'newtag'". Hey and also now I can easily make one of those silly tag clouds all the kids are going crazy for these days. Option #3 Two new tables, Tags (tagid|tagname) and PostTags (tagid|postid) to stay normalized, with more complex queries and all kinds of gobbledigook. #1 is a lot simpler, at the cost of giving me the gibblies because I'm not using a relational strategy to solve the problem. #2 seems good, but not normalized, and #3 seems kind of like overkill.
|
# ? Apr 30, 2008 04:30 |
|
Do not go for Option 1, ever. Horrible search performance, horrible update logic, no referential integrity, almost impossible to get aggregate statistics efficiently... the list goes on. Option 2 is normalized. It's only unnormalized if you're considering having an integer tag_id to go with every tag name, and there may not be much point in that since it's just a 1-1 relationship. So I don't see too much difference between Option 2 and 3. Are your tags case-sensitive? If not, you'll have to add "lower(tagname)" all over the place. This might affect indexing too - if your DB supports indexing on expressions, index on "lower(tagname)" rather than just tagname.
|
# ? Apr 30, 2008 04:49 |
|
I'd choose #3, because I think it would be the easiest way to relate tags for like posts to eachother, the joins would really not be that bad, and you can still implement your cool cloud of tags. Also, #1 would just end up being a huge mess after a while.
|
# ? Apr 30, 2008 04:50 |
|
That's what I was thinking. Option 1 only appealed to me because it was so incredibly easy to implement... but... dumb to use. Option 2 it is. Thanks! I have another SQL design question in the works, a bit more difficult.
|
# ? Apr 30, 2008 04:54 |
Is there a way to use mysqldump to dump a database and stuff it into a different one in a single command?
|
|
# ? Apr 30, 2008 18:48 |
|
fletcher posted:Is there a way to use mysqldump to dump a database and stuff it into a different one in a single command? Since mysqldump just outputs SQL you can do this on the command line: code:
|
# ? Apr 30, 2008 19:18 |
fansipans posted:Since mysqldump just outputs SQL you can do this on the command line: Hah! Good stuff, thanks. Is there a way for it to hesitate to let me put in passwords when I do something like: code:
Enter password: Enter password:
|
|
# ? Apr 30, 2008 19:32 |
|
I have a table called categories. It has 2 fields, Id(which is the PK) and CategoryName. Is there a way to insert a new row into the table and instantly get back the ID of the new added entry? I'm sure there is, I just can't figure it out.
|
# ? May 1, 2008 02:38 |
|
What platform?
|
# ? May 1, 2008 02:58 |
|
mySQL
|
# ? May 1, 2008 03:22 |
|
MySQL has last_insert_id(), additionally if you're using PHP I believe there's a wrapper for it (mysql_insert_id() I think?) as well.
|
# ? May 1, 2008 03:26 |
|
I am using PHP. i'll look it up! Thanks!
|
# ? May 1, 2008 04:54 |
|
Okay gentlemen, I need some clarification. My boss is under the assumption that grouping a fuckton of queries in a single SqlCommand execution classifies as a "transaction". Like so: "insert into blah values(something, something);insert into blah values(something2, something2);insert into..." and so on. Now, I'm trying to convince him to let me rewrite our transaction wrapper [which basically, I'm not even joking, concats a string of queries together like that] into an actual, real transaction system. My question is, let's say we execute the above pile of queries, and detect an exception, and execute a rollback. My understanding is that the rollback would only rollback the last executed query in this case, so if it fails at the fifth query in the list, the other four would still stay, if it failed on the second, the first one would stay, etc. This is causing serious loving problems and needs to change - I just want to make sure that I have my facts straight, since he's the type to be dead set in his ways unless I can point out exact reasons why things aren't written right. It should be noted that my boss doesn't have a CS degree, and really... really needs to not be near SQL, obviously. For reference, we're running MS SQL 2005 and the pages/libraries running queries are in a mix of C# and VB.NET 2005.
|
# ? May 2, 2008 17:54 |
|
Code Jockey posted:Okay gentlemen, I need some clarification. Why don't you just make a small test and show him some results? It shouldn't take too long. Either throw an error (raiserror) or deliberately make one of the statements break (e.g. delete a record that is being referenced by a foreign key and on cascade is not set).
|
# ? May 2, 2008 17:57 |
|
chocojosh posted:Why don't you just make a small test and show him some results? Oh man that is probably the easiest possible way. Thanks!
|
# ? May 2, 2008 18:01 |
|
|
# ? May 14, 2024 10:34 |
|
Code Jockey posted:Okay gentlemen, I need some clarification. I can't speak for MS SQL, but on the Oracle side this would sort of work as he expects (although it's still a bad way to do it). The reason is that you have to explicitly commit, and if you don't it's still the same transaction. If you set autocommit on your db driver, then it will automatically call commit on each separate call, so stacking them into one statement would effectively treat them as one transaction. A far better way to accomplish this same thing is to turn off autocommit, call each one in sequence, and then commit when you are ready. Now, maybe MS SQL is different in how it handles transactions, I'm not sure if it's the same in this regard. Hopefully someone can clarify. npe fucked around with this message at 18:40 on May 2, 2008 |
# ? May 2, 2008 18:27 |