SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Runtime Code


Runtime Code

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)

Group: Administrators
Points: 248792 Visits: 19807
Comments posted to this topic are about the item Runtime Code

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Varga
Gary Varga
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38204 Visits: 6562
It is an extremely interesting thought.

I bet the level of testing is much lower especially when considering that this area of research is undoubtedly less mature and therefore we will have less input into professional development.

When I have done similar work (i.e. generated code) I have not been as rigorous as I otherwise would have been as I have been less confident in ways to provide the same level of coverage.

I, for one, am certainly interested in any experience (successful or less so) in this area. Also not necessarily limited to SQL related features (SSIS, ETL etc.) as often cross pollination of ideas works wonders.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
joe.eager
joe.eager
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 467
I agree this is an interesting discussion topic.
Peter Ryan-300561
Peter Ryan-300561
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 113
Hi,

I am doing this using stored procedures interacting with SSIS 2008 R2. My testing is to run against the entire data set.

The code I am generating is a select statement executed in a MySQL database which creates an export file for loading via a SSIS file import task. The original five different MySQL stored procedures gave five different files and had five separate, but very similar, SSIS packages to load them into the same data destination. (A Data Warehouse.)

By writing a stored procedure to write the required MySQL select statement I was able to have only one SSIS package to load the data. This could then be encapsulated by a "Looper" package, which would call the new SSIS package. It uses expressions extensively to shape sources and destinations for the package.

Outside of ensuring that the package fails safely, and can be run against the entire initial data set, the code generation is based on fixed tables, not the data itself, so there is a finite number of possibilities.

There are still cases I have not expected and I've learned a great deal about the SSIS task inconsistencies. (The Sort data flow task defaults to case sensitive, but the partial option of the Lookup Transformation tast seems to be case in-sensitive.)

I'm sure that I don't always use SSIS correctly, I tend to used stored procedures to transform large data sets, but I'm reading and learning to do better.

Thanks,
Peter
Toby Harman
Toby Harman
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1845 Visits: 671
Ultimately we will see more and more of this from the likes of LINQ and other ORM tools.

Admittedly these aren't particularly complex SQL most of the time, and we can mitigate some of this by enforcing data access throuhg stored procedures, but they're still generating SQL.
Dave Poole
Dave Poole
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26674 Visits: 3544
I had a lot of fun building software to auto-generate CRUD stored procedures for reference data. It takes a surprising amount of code to generate relatively simple code.

The problem was mainly in understanding the different use cases that apply when auto-generation comes into affect.

I got caught out by the length of the VARCHAR parameters/fields because a length of -1 means VARCHAR(MAX).

I also had some fun with the primary key fields and description fields because in the DB they follow the naming convention <tablename>ID and <tableName>Description where as in the stored procs they present as ID, Description. You have to remember to allow for a table where someone has already called a column ID or Description and also how you detect which column to call Description if you multiple VARCHAR fields.

Generally if you can autogenerate the code then you can autogenerate a fair number of the tests which will flush out these quite effectively.

Generally autogenerating code is taking tried and tested code and turning it into a template.

LinkedIn Profile
www.simple-talk.com
joe.eager
joe.eager
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 467
Toby Harman (11/13/2013)
Ultimately we will see more and more of this from the likes of LINQ and other ORM tools.

Admittedly these aren't particularly complex SQL most of the time, and we can mitigate some of this by enforcing data access throuhg stored procedures, but they're still generating SQL.


I prefer forcing them through stored procedures as some of the SQL I've seen generated by ORM's be pretty horrible.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search