I like the SQL scripting option…

October 19, 2004

EDIT 10/19/2004: This is annoying. I was using SauceReader, my RSS aggregator, to comment on a post Trey made. Instead, it posted it to MY blog instead of as a comment on Trey’s page. While it doesn’t make much sense in places, I’ll leave it here. Mind you, I think I’m going to finally leave SauceReader as well – it is starting to drive me nuts.

ORIGINAL MESSAGE 10/18/2004: I recently refactored an existing application to make installation easier; part of that refactoring was to use OleDb against either SQL Server or Oracle to create the database structure from within the application rather than have the user run one of two different sets of scripts.  The two sets of scripts left room for error in maintaining the structure plus it was a strain for an implementation person who was used to one or the other databases but not both.

Basically, I created a subdirectory inside the project and each table got its own script.  Each relationship got its own script.  Then, inside the application I test for the primary table – if it wasn’t there I prompted the user to create the structure (the user can’t log in without a valid database login).  If the user says ‘yes’, I run the table scripts one after another and then run the relationship scripts.

Now, this may not be the optimal way to create indices and such, but the application isn’t a DBA; if the client wants to fine tune the db then a DBA is warranted.  However, this application is rather lightweight and while there are lots of reads, very little writes.

I only mention this because you were wondering scalibility – the .Net framework does provide a OleDbProvider for jet databases so it would work over Oracle, SQL Server and Jet.  The other nice bit about SQL scripts is that if the DBA wants to tune the scripts before creating the database or wants to create the database manually the resource is there.

Just my thoughts… if I don’t make sense or I’m rambling, well, sorry.  It’s late.

Advertisements

2 Responses to “I like the SQL scripting option…”

  1. Nathan said

    Sorry to hear you had trouble with the comment posting. If you have more details on the problem, please drop by our support forum and leave us a note so we can investigate. Any info on the stuff that’s “driving you nuts” would also be appreciated šŸ™‚

    http://www.synop.com/Support/

    Thanks, Nathan

  2. I will drop by – I found one bug but I just installed the new version and hopefully it is fixed. If not, I’ll report it. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: