Runtime Code

  • Comments posted to this topic are about the item Runtime Code

  • 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!!!

  • I agree this is an interesting discussion topic.

  • 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

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

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply