Execute code only once?

[thomas008]

Hi

I would like to create stored procedures in vb.net which works just fine.
The only problem is i need to make sure that the exist.
So i need to run the code the create them only once and then never again
What is the best way to do this? I could check if alle the procedures i need exist and if not create them. Or is there some sort of code that only executes once and never again. Not even if you restart your program?

 

[picoflop]

I'd bind this function to the database itself, because it's a functionality of the db.
Create a config file that contains the Create Code for all needed procs plus a unique identifier (name of the storedproc would be an idea). Create a table in your database that has two colums (at least): StoredProcHash and StoredProcIdentifier.
On startup, read your config file and for each create command build a hash (MD5 or whatever you like). Now check for each proc if the value of StoredProcHash is equal to the value of the hash for the config file. Three possible results:
a) No record found for StoredProcIdentifier: Stored proc is new -> create it (and write hash and identifier to db)
b) Record found, but hash mismatch -> Stored proc has changed. Drop proc and create new (update hash in db)
c) record found and hash match -> everything ok, nothing to do

Has the advantage that you don't have to care about old versioned databases when your storedprocs need to be changed, because you add or change functionality of your app.

ym2c

 

[InertiaM]

Maybe I'm missing the point, but why cant you query the system tables to check whether the stored procedures exist?

 

[thomas008]

Maybe I'm missing the point, but why cant you query the system tables to check whether the stored procedures exist?

I wanted to do that but when i execute the command to create the procedures they do not show up in my access database. So i don't know where to look for them. I know they are there because i can call the select query(no parameters) ny other query(with parameters) gives me a syntax error.

 

[InertiaM]

SELECT * FROM MSysObjects

Type 5 are queries

SELECT * from MSysQueries

gives you the query definitions.

 

[cjard]

Hi

I would like to create stored procedures in vb.net which works just fine.
The only problem is i need to make sure that the exist.
So i need to run the code the create them only once and then never again
What is the best way to do this? I could check if alle the procedures i need exist and if not create them. Or is there some sort of code that only executes once and never again. Not even if you restart your program?

If your access db is only used by a local user, why not drop and recreate them all as a matter of course, when you start the app?

Come to think of it, why are you using them at all in an Access db?