SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Barrackas: Aug 12, 2007

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.

Thanks for taking the time to answer these questions, I'm definitely getting a better feel for Oracle now. Cheers!

# ? Apr 22, 2008 10:52

Adbot: ADBOT LOVES YOU

# ? May 13, 2024 23:53

Stephen: Feb 6, 2004; Stoned

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

No Safe Word: Feb 26, 2005

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

Puddleboy: Feb 15, 2008

Stephen posted:

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.

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

Stephen: Feb 6, 2004; Stoned

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

Aredna: Mar 17, 2007; Nap Ghost

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

Stephen: Feb 6, 2004; Stoned

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:

(SELECT * 
FROM autos 
WHERE status_id = 'used' 
)UNION( 
SELECT * 
FROM autos 
WHERE status_id = 'new'
GROUP BY autos.auto_year, autos.make_id, autos.model_id, autos.dealership_id
)
ORDER BY id DESC 
LIMIT 15

This selects all used cars seperately, and then selects all new cars grouped by their year, make, model, and dealership, and then orders and limits the results. It works properly, but I'm sure there's a better way of doing this.

Stephen fucked around with this message at 21:01 on Apr 23, 2008

# ? Apr 23, 2008 20:47

da keebsta knicca: Sep 12, 2000; Oh Ruutu, you are such a card.

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

Xae: Jan 19, 2005

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

npe: Oct 15, 2004

Not sure what you mean by that, could you give an example of what you want to do?

# ? Apr 25, 2008 13:04

Barrackas: Aug 12, 2007

yaoi prophet posted:

* COMMIT or ROLLBACK in pl/sql

Hi again, I was just wondering if you could go into some detail on this point? I'm starting to teach myself about transactions and an example procedure I've come up with is this...

code:

CREATE OR REPLACE
PROCEDURE TRANS_TEST
AS 
  counter INTEGER;
BEGIN
  counter := 0;
  INSERT INTO test_table VALUES('No rollback occured');
  IF(counter = 0) THEN
    ROLLBACK;
    INSERT INTO test_table VALUES('Rollback Occured');
  END IF;
ENDTRANS_TEST;

... where I'm just changing the value of counter to see what happens. Is there something incorrect about using ROLLBACK like I have above?

# ? Apr 25, 2008 13:50

npe: Oct 15, 2004

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:


  BEGIN
    -- this guy works
    foo.do_first();

    BEGIN
     -- this guy fails
     foo.do_second();
    EXCEPTION WHEN some_exception THEN
      foo.do_other_thing();
    END;
  END;

Simply, we call do_first() and then we call do_second(). We expect do_second() might fail so we handle that and do_other_thing(). However, the big problem is when do_second() internally decides to ROLLBACK. He has now rolled back the entire transaction, including whatever do_first() did. Even worse, it's rolled back whatever the client did at all before he called this procedure.

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

chocojosh: Jun 9, 2007; D00D.

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.

...

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.

Does this apply to C# development (with SQL Server)? Our stored procedures are full of CREATE/COMMIT/ROLLBACK trans.

# ? Apr 25, 2008 15:01

npe: Oct 15, 2004

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

Barrackas: Aug 12, 2007

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:

CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
SET TRANSACTION test_transaction
INSERT INTO table VALUES('test');
-- Do some stuff that checks if something is erroneous
IF(error = 1) THEN ROLLBACK END IF;
END test_proc;

I have no idea if this syntax even makes sense, but I'm just wondering if scoping transactions like this is possible? I've read about the PRAGMA autonomous transaction, is that more appropriate? Like...

code:

CREATE OR REPLACE PROCEDURE test_proc IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO table VALUES('test');
  -- Do some stuff that checks if something is erroneous
  IF(error = 1) THEN ROLLBACK END IF;
  COMMIT;
END test;

Would I be correct in saying that this second example is isolated from whatever other changes the client has been making prior to this procedure being called? Does that make this an (slightly more?) acceptable way to use ROLLBACK (and maybe COMMIT)?

Barrackas fucked around with this message at 15:13 on Apr 25, 2008

# ? Apr 25, 2008 15:11

npe: Oct 15, 2004

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:


 BEGIN
   foo.do_something();
 EXCEPTION WHEN OTHERS THEN
   my_audit_pkg.log('failure doing something');
   RAISE;
 END;

In this case, we want to catch an exception on it's way "out" and log it. However, if we do this with a normal procedure then the client can rollback and it will also rollback the audit information. By making my_audit_pkg.log() an autonomous transaction, you can enforce that it will always be recorded.

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:

CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
  INSERT INTO table VALUES('test');
  -- Do some stuff that checks if something is erroneous
  IF(error = 1) THEN 
    RAISE_APPLICATION_ERROR(-20001, 'there was a problem');
  END IF;
END test;

The exception here will stop everything until it's either caught at a higher level, or until it gets back to the client which will have to decide what to do. Of course, if the guy calling him doesn't care about whether or not this operation failed, he's free to catch your exception himself and do whatever with it, but that's up to him.

npe fucked around with this message at 15:42 on Apr 25, 2008

# ? Apr 25, 2008 15:21

Barrackas: Aug 12, 2007

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:

...
DELETE FROM table 
WHERE ID <> @supplied_ID

SELECT @error = @@ERROR
IF(@error <> 0)
BEGIN
	IF @trancount = 1
	BEGIN
		ROLLBACK TRAN
	END
	RETURN @error
END

There's other stuff before that which looks like this...

code:

IF @@TRANCOUNT = 0
BEGIN
	SELECT @trancount = 1
	BEGIN TRAN
END

In-procedure transaction committing and rolling back seem to happen a lot in the existing code, and the desire is to keep the two codebases as similar, in terms of logical flow, as is reasonable. Am I correct in thinking that autonomous transactions are the best way to achieve the same effect as the snippets above demonstrate?

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

npe: Oct 15, 2004

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

Barrackas: Aug 12, 2007

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

npe: Oct 15, 2004

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:

SQL> BEGIN
  2    insert into example (text) values ('this one should stay');
  3    savepoint blah;
  4
  5    BEGIN
  6      insert into example (text) values ('this one should not');
  7      raise_application_error(-20001, 'woof');
  8    EXCEPTION WHEN OTHERS THEN
  9      rollback to blah;
 10    END;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from example;

TEXT
--------------------------------------------------------------------------------
this one should stay

npe fucked around with this message at 02:27 on Apr 26, 2008

# ? Apr 26, 2008 02:23

Barrackas: Aug 12, 2007

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

npe: Oct 15, 2004

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:

 BEGIN
  SAVEPOINT implicit_start;
  
  -- your code here...
  -- ...

  EXCEPTION WHEN OTHERS THEN
    ROLLBACK TO implicit_start;
    RAISE;
 END;

This is only at the top-most level, though. The reason is that Oracle wants one of two things to happen: either the procedure succeeded, or it failed and never happened. The client can't get an exception and then decide to keep on trucking (unless you've gone and put commits/atrans in the procedure).

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

Barrackas: Aug 12, 2007

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:

CREATE OR REPLACE
PROCEDURE TRANS_TEST
AS 
BEGIN
  SAVEPOINT tran_save;
  insert into a_test_table values (1);
  insert into a_test_table values (2);
  sub_thing();
  dbms_output.put_line('No errors occurred - the transaction was committed.');
EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO tran_save;
      dbms_output.put_line('An error occurred - the transaction was rolled back');
end TRANS_TEST;

code:

CREATE OR REPLACE
PROCEDURE SUB_THING
AS
BEGIN
  INSERT INTO A_TEST_TABLE VALUES(5);
  INSERT INTO A_TEST_TABLE VALUES('a');
END;

This results in 0 rows being updated, as the implicit savepoint in TRANS_TEST is rolled back to. Is this what you meant by interior calls or have I got the wrong end of the stick here?

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

npe: Oct 15, 2004

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:

SQL> begin
  2    insert into example (text) values ('first');
  3  
  4    begin
  5      insert into example (text) values ('second');
  6  
  7      raise_application_error(-20001, 'woof');
  8    exception when others then
  9      null;
 10    end;
 11  end;
 12  
 13  /

PL/SQL procedure successfully completed.

SQL> select * from example;

TEXT
--------------------------------------------------------------------------------
first
second

If I raise that exception on line 7 and don't handle it, it rolls back to the outer save point. If I handle it, it doesn't roll back anything. It would be sort of nice if Oracle could do a sort of scoped rollback with an implicit savepoint before each block, but anyways I only mention this because sometimes it seems like Oracle is rolling back to the nearest BEGIN but it's really rolling back the entire transaction.

# ? Apr 28, 2008 18:09

ImJasonH: Apr 2, 2004; RAMALAMADINGDONG!

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

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

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

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

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

ImJasonH: Apr 2, 2004; RAMALAMADINGDONG!

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

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

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

fansipans: Nov 20, 2005; Internets. Serious Business.

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:

 mysqldump foo | mysql bar

# ? Apr 30, 2008 19:18

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

fansipans posted:

Since mysqldump just outputs SQL you can do this on the command line:
code:
 mysqldump foo | mysql bar

Hah! Good stuff, thanks. Is there a way for it to hesitate to let me put in passwords when I do something like:

code:

mysqldump -u root -p foo | mysql -u root -p bar

It gives me:

Enter password: Enter password:

# ? Apr 30, 2008 19:32

MrSaturn: Sep 8, 2004; Go ahead, laugh. They all laugh at first...

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

npe: Oct 15, 2004

What platform?

# ? May 1, 2008 02:58

MrSaturn: Sep 8, 2004; Go ahead, laugh. They all laugh at first...

mySQL

# ? May 1, 2008 03:22

npe: Oct 15, 2004

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

MrSaturn: Sep 8, 2004; Go ahead, laugh. They all laugh at first...

I am using PHP. i'll look it up! Thanks!

# ? May 1, 2008 04:54

Code Jockey: Jan 24, 2006; 69420 basic bytes free

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

chocojosh: Jun 9, 2007; D00D.

Code Jockey posted:

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.

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

Code Jockey: Jan 24, 2006; 69420 basic bytes free

chocojosh posted:

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).

Oh man

that is probably the easiest possible way. Thanks!

# ? May 2, 2008 18:01

Adbot: ADBOT LOVES YOU

# ? May 13, 2024 23:53

npe: Oct 15, 2004

Code Jockey posted:

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.

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

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »