Yukon WishList - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Yukon WishList

Author

Topic

KHeon
Posting Yak Master

135 Posts

Posted - 2002-10-15 : 15:01:32

Hello everyone!

The real purpose of this post is to see if anyone knows whether or not Yukon will support multiple statements per connection like Oracle does.

Just spent an incredible amount of time floundering around with an SQL/ADO error that ultimately was because you cannot have multiple connections during a transaction.

Additionally, what other features might you all be interested in seeing in Yukon? I can't wait to start programming sprocs in C#!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-15 : 15:13:00

quote:
The real purpose of this post is to see if anyone knows whether or not Yukon will support multiple statements per connection like Oracle does.

Uhhhhhhhh, do you mean:

SELECT * FROM myTable
SELECT * FROM myOtherTable
SELECT * FROM SomeCompletelyUnrelatedOtherTable
UPDATE myTable SET col1='Some Value'

Because SQL Server allows that to run on a single connection since...well, since before I started using it! Multiple statements per connection and multiple connections during a transaction are two totally separate things.

quote:
Additionally, what other features might you all be interested in seeing in Yukon? I can't wait to start programming sprocs in C#!

I think that for all of the great features that will become available, this capability will unfortunately reinforce non-database thinking and programming. Cursors will ALWAYS be used and people will just throw bigger CPU's and hard drives at the problem. Not saying you personally will do that

but a lot of people will refuse to learn how to program specifically for a database because C#/VB.Net "doesn't work the way SQL does" or "SQL isn't a .Net language."

More feelings about it are here:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12598

KHeon
Posting Yak Master

135 Posts

Posted - 2002-10-15 : 15:31:12

Rob -

Actually what I am referring to is being able to do two seperate actions against SQL Server AT THE SAME TIME. I can't think of a time where it would have to happen but I have seen it in a previous job, where a second connection to SQL Server (v6.5) had to be created in order to execute both statements simulatenously. Also, I've run into issues using the same connection for both select and insert/update/delete when looping through a recordset.

I was, in reference to the transaction issue doing something like this...

oConn.BeginTrans

Insert Record into Table A

- If error oConn.RollbackTrans

Query Table B for data

Loop through Table B recordset

Insert Record into Table C
- If error oConn.RollbackTrans

Loop until Recordset.EOF

Update Table D

- If error oConn.RollbackTrans

oConn.CommitTrans

The failure was on the query nested inside the transaction. Solved it by simply moving it outside the transaction (thankfully I was able to do that).

I've used cursors on occassion, but mostly for administration purposes, hardly ever in a production system (although there are those times where they are needed -- albeit very limited).

Now for another question, and this may sound stupid but I can't seem to get around it. I have a transaction that adds a "parent" record. I then add child records to two seperate tables which have foreign keys on the parent table. I am unable to have the three inside a transaction because the foreign key appears to be invalid until the parent record is committed, which doesn't happen until everything is complete, but the seperate tables rely on the parent.

Does that make sense? The key that joins the three is a UNIQUEIDENTIFIER, that wouldn't be the problem would it? It's odd.

A slightly better definition is this:

Add User
- Add Address to User
- Add Telephone to User

Thoughts?

Thanks!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-15 : 15:40:48

The problem is using ADO to handle your transactions. This is always less than ideal. Can you rewrite it so that a single stored procedure accepts all the data you need to manage, and have the PROCEDURE handle the transaction? Something like this is very easy:

CREATE PROCEDURE AddParentsAndKids @parentName varchar(50),
@kidName1 varchar(50),
@kidName2 varchar(50)=Null AS
DECLARE @parentID int

BEGIN TRANSACTION
INSERT INTO Parent(Name) VALUES (@parentName)

IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION
RETURN
END

SELECT @parentID=@@IDENTITY
INSERT INTO Kids(Parent, Name) VALUES (@parentID, @kidName1)

IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION
RETURN
END

IF @kidName2 IS NOT NULL
INSERT INTO Kids(Parent, Name) VALUES (@parentID, @KidName2)

IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION
RETURN
END

COMMIT TRANSACTION

Doing it like this makes your transactions bulletproof and completely managed on the server side, where they belong. Additionally this cuts down on network traffic tremendously and eliminates a packet drop or other network problem as a cause for a failed transaction...something that ADO transactions can never fully guarantee against. It also greatly simplifies your ADO coding because you only need one connection and one command or recordset object, and you never have to change it even if the stored procedure changes radically. You can also customize your error handling and return a special error message that indicates EXACTLY where the error occurred, again without touching the client code.

BTW, the majority of the code above is error handling, and only for the INSERT operations. If the data supplied does not violate any CHECK constraints then you don't need any of it, and this whole procedure comes down to 5-6 lines of code.

Edited by - robvolk on 10/15/2002 15:45:45

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-10-15 : 19:31:35

I think...I want build it versioning. Been meaning to learn how to use Source Safe (or find out what it is!). Sure would be nice to have a model for how to do this.

Voted best SQL forum nickname...."Tutorial-D"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-15 : 22:35:14

1. I agree with sitka integration with source control would be nice.
Although you can take your sql scripts and add them manually.

2. Java/C# Style Error Handling

3. Built-In Encryption

4. Better Remote HTTP/HTTPS Support

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-10-15 : 23:02:54

NO NO NO NO NO!

quote:

Yukon will support multiple statements per connection like Oracle does.

Use a stored proc as Rob said. That is what they are for.

quote:

I want build it versioning. Been meaning to learn how to use Source Safe (or find out what it is!).

Find out what it is and learn to use it.
You don't have it built into a web server so why should it be built into your DB server.
Source control should be a part of your development platform / IDE / methodology. You shouldn't be using EM to write code.

I am sick of MS trying to integrate everything. When that happens, you HAVE to work the way they want you to work. That doesn't always suit, and when it doesn't you are screwed if you are relying on MS tools.

quote:

Built-In Encryption

So we can answer more questions here when people who don't know how to use it manage to lock away some data they really needed ? No thanks.

quote:

Better Remote HTTP/HTTPS Support

Ewwwwwwww! Keep your data in your database and your HTTP in your HTTP server. If you need to build some sort of RPC bridge, build one....securely. See above point about integration.

I am not looking forward to the next round of security holes when people start opening up their database over http.

Think about this, Code Red + blank SA paswords......now tell me you are not scared.

Damian

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-10-16 : 02:07:51

I'd have to agree with Rob and Merkin. I would never like to program my procs in C# or any other "normal" language. SQL is by far the most effective when dealing with relational data. That's my opinion.

And yes, keep as much of the transaction handling as possible within the SQL server. I have had consultants here who built applications with ADO and in the middle of transactions waited for user input. Not nice!

Built-in encryption would be nice though... That would be very useful indeed.

/Andraax

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-16 : 03:03:43

Just curious, but has anyone heard anything about the release *after* Yukon? From my understaning, they are going to have a version integrated with the OS and use it as a file system.

I'm really not sure how I feel about that. On one hand, I can picture a few neat things that that could do, but on the other hand, Errrr, I'd prefer not to think of the downsides at the moment.

I don't have a link, or else I'd provide it.

Oh yeah, on to another topic. In the new Visual Studio.NET, they have better integration for SQL script files and the like that make it pretty easy to use Source Safe for versioning. (Oh yeah, I love VS.NET so far. And I haven't even scratched the surface on what it can do.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

KHeon
Posting Yak Master

135 Posts

Posted - 2002-10-16 : 07:07:09

Thanks for the suggestion on wrapping it all in a stored procedure. Unfortunately I can't do that, at least not easily with the current project, although I can definetly keep that in mind next phase and rework the code to pass it all to the database. I have everything working the way I want, with rollback working where I need (for the most part).

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-16 : 07:34:34

That looks like a problem with allowing the application to own the database. Only applicable if you have a single application and even then not ideal. Common when application developers are allowed to run projects without realising that databases have different requirements - and as Rob says will get even worse if people start to develop in C#. Someone said to me recently that the concept of a relational database is dead - if this comes along he may be right as without a set based language to manipulate it you will need something more geared to the application language and have to maintain data integrity by other means.

For using SourceSafe see
www.nigelrivett.com
Administering SQL Server Release Control

I am in the process of rewriting this and expanding it to encompass large projects but don't know when it will be complete.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-10-16 : 08:01:51

RDBM's are ok but I remember reading about OODBM's ... and that intrigued me ... as far as yukon ... I would like to see better XML support ( i have recently discovered the wonders of being able to send large batch updates via xml to a stored procedure for processing (ALL IN ONE TRANSACTION IN THE STORED PROCEDURE) :) rather than calling the same procedure over and over and over to insert tid bits of data ... ) as far as .NET goes ... I would like OO-procedures ... i think that would be one of the neatest things they could offer ... or at least namespaces ... then you could organize your functions/procedures without using odd prefixs...

I thought yukon was the new file system format ... and that's what they are building Longhorn on ...

As far as using C# ... I would like to see the try..catch..finally structure added for error handling (or any type of better error handling) ... I would like to see database constants ... where you could declare your own constants such as application constants and then reference them in SQL queries ... rather than having to load those constants every time from a table i would just like to see the db do it for me :) ... or even enumerations would be cool ... that's one thing I think SQL Server is missing ... enumerated types (sort of like MySQL) ... (well i guess you could emulate this with a check statement)

I wish they would push us more info about yukon some time soon ... whatever they do offer i'm sure it will make us shutter to hear that someone is still using SQL Server 2000 ... :-p

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-16 : 11:24:52

quote:

I am not looking forward to the next round of security holes when people start opening up their database over http.

I agree with Merkin - the database should remain just a database. Ok, I dont even like windows authentication integration - hell, they might even decide to scrap SQL authentication tommorrow !
After working with oracle for a while, i have been pleasently surprised by the simplicity of so many things in sql server. for e.g. installation procedure, very good integrated bcp tools (not so in oracle ), good enterprise manager (we had a 3rd party one for oracle..)

-ashok
http://www.unganisha.org

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-10-16 : 13:23:26

thanks for the link nr

quote:
I am sick of MS trying to integrate everything.

good point, default install = DISABLED

maybe cool automated xml documentation

///<quirkyupwithpeoplehomoambiguouspoorlytranslatedpopsong>
///It's so easy, happy go lucky....
///</quirkyupwithpeoplehomoambiguouspoorlytranslatedpopsong>

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources