Code Practice - General Question 
Author Message
 Code Practice - General Question

I'm relooking at a code practice and looking for input.

Currently if I read or write to a DB table I first check to see if the table
exists and then (if of importance) see if the table is empty.

I'm thinking that a better practice may be to check if the table exists just
once (maybe at startup) with the assumption made that if it exists it will
continue to exist throughout the program.

Any input appreciated.



Tue, 03 Jan 2012 00:01:06 GMT  
 Code Practice - General Question

Quote:
> I'm relooking at a code practice and looking for input.

> Currently if I read or write to a DB table I first check to see if the
> table exists and then (if of importance) see if the table is empty.

> I'm thinking that a better practice may be to check if the table exists
> just once (maybe at startup) with the assumption made that if it exists it
> will continue to exist throughout the program.

> Any input appreciated.

What's your backend database system? I can't remember the last thing I wrote
that didn't have a "permanent" database behind it, meaning that I didn't
have to worry for one moment whether or not the table existed; I just
assumed it did.


Tue, 03 Jan 2012 01:40:45 GMT  
 Code Practice - General Question


Quote:
> I'm relooking at a code practice and looking for input.

> Currently if I read or write to a DB table I first check to see if the
> table exists and then (if of importance) see if the table is empty.

> I'm thinking that a better practice may be to check if the table exists
> just once (maybe at startup) with the assumption made that if it exists it
> will continue to exist throughout the program.

> Any input appreciated.

I gotta go along with Jeff.  I can't remember ever having to check for the
existance of a table with one exception, and that's if I've got code to
create the table (in which case it only gets created if it doesn't exist). I
also can't think of a typical reason why you'd need to check if the table is
empty or not.

Sure, in either case there might be special situations where you really do
need to check these 2 things, but IMO those are exceptions not a standard
coding practice. For example, another reason why you might check for the
existance of a table is if your program provides additionally functionally
if the table exists.  Just to throw something out, maybe your app is an
employee database and you have Lite and Professional editions and so you
store additonal information about an employee for the Pro edition in a
separate table.  Probably better ways to handle that though.

--
Mike



Tue, 03 Jan 2012 05:06:12 GMT  
 Code Practice - General Question
Thanks guys.

I dynamically create and delete various tables, hence the check.
I guess if I don't error, then I can assume creation worked and table
exists.

Only other condition would be at startup.  Again if table never deleted from
DB it still should be there and available.

Without going into depth of my code, there are several reasons I check if
table is empty as different jumps may occur based on result.

Consider thread closed.


Quote:



>> I'm relooking at a code practice and looking for input.

>> Currently if I read or write to a DB table I first check to see if the
>> table exists and then (if of importance) see if the table is empty.

>> I'm thinking that a better practice may be to check if the table exists
>> just once (maybe at startup) with the assumption made that if it exists
>> it will continue to exist throughout the program.

>> Any input appreciated.

> I gotta go along with Jeff.  I can't remember ever having to check for the
> existance of a table with one exception, and that's if I've got code to
> create the table (in which case it only gets created if it doesn't exist).
> I also can't think of a typical reason why you'd need to check if the
> table is empty or not.

> Sure, in either case there might be special situations where you really do
> need to check these 2 things, but IMO those are exceptions not a standard
> coding practice. For example, another reason why you might check for the
> existance of a table is if your program provides additionally functionally
> if the table exists.  Just to throw something out, maybe your app is an
> employee database and you have Lite and Professional editions and so you
> store additonal information about an employee for the Pro edition in a
> separate table.  Probably better ways to handle that though.

> --
> Mike



Tue, 03 Jan 2012 05:59:58 GMT  
 Code Practice - General Question
Don't know what DB you are working with and probably you have code already
to test,
but I use this function to check a Firebird table and you may find it
useful:

Public Function FirebirdTableExists(strTable As String) As Long

  Dim strSQL As String
  Dim rs As ADODB.Recordset

  '-----------------------------------------------
  'will give 1 if table exists and has records
  'will give 0 if table exists, but has no records
  'will give -1 if table doesn't exist
  '-----------------------------------------------
  On Error GoTo ERROROUT

  'looks odd, but works fine and is a bit faster than selecting a field
  '--------------------------------------------------------------------
  strSQL = "SELECT FIRST 1 NULL FROM " & strTable

  Set rs = New ADODB.Recordset
  rs.Open strSQL, oADOConn, adOpenForwardOnly, adLockReadOnly, adCmdText

  If Not rs.EOF Then
    FirebirdTableExists = 1
  End If

  Exit Function
ERROROUT:

  FirebirdTableExists = -1

End Function

RBS


Quote:
> I'm relooking at a code practice and looking for input.

> Currently if I read or write to a DB table I first check to see if the
> table exists and then (if of importance) see if the table is empty.

> I'm thinking that a better practice may be to check if the table exists
> just once (maybe at startup) with the assumption made that if it exists it
> will continue to exist throughout the program.

> Any input appreciated.



Tue, 03 Jan 2012 06:08:22 GMT  
 Code Practice - General Question
The answer depends on the likelihood that the table will disappear between
the time you check it and the time you use it, and that in turn depends on
where the tables are coming from, how they are created and whether they can
be deleted while your process is running.  The general rule is as you have
been applying it - check immediately before use.  But typical practice is to
relaxe this rule (eg, checking once at startup) if the situation warrants
it.  For instance, if you are checking a back-end database then the chance
of the table disappearing is slight, and the chance of it disappearing
between the start of a procedure and the time you use it is so small as to
be not worth worrying about, so checking at startup is appropriate. OTOH, if
you are checking because of the possibility of losing the connection, then
the disconnect could occur at any time and checking immediately before use
may be warranted.

You should also consider the implications of not catching the error. If the
user would not be fazed by a system-produced error report and knows that
they can safely restart the application after checking what the problem
might be, then letting the system catch and report the error might be OK,
and would save a small amout of processing.  But if it is part of a process
that needs to be properly terminated and restarted then obviously proper
error processing is required and that in turn requires checking immediately
before using it.


Quote:
> I'm relooking at a code practice and looking for input.

> Currently if I read or write to a DB table I first check to see if the
> table exists and then (if of importance) see if the table is empty.

> I'm thinking that a better practice may be to check if the table exists
> just once (maybe at startup) with the assumption made that if it exists it
> will continue to exist throughout the program.

> Any input appreciated.



Tue, 03 Jan 2012 09:38:27 GMT  
 Code Practice - General Question


Quote:
> Thanks guys.

> I dynamically create and delete various tables, hence the check.
> I guess if I don't error, then I can assume creation worked and table
> exists.

> Only other condition would be at startup.  Again if table never deleted
> from
> DB it still should be there and available.

> Without going into depth of my code, there are several reasons I check if
> table is empty as different jumps may occur based on result.

> Consider thread closed.

You might consider it closed and that's fine, but for anyone else that may
be interested....this sounds like a very iffy design IMO.  It's one thing to
create a table ONE time via code, but constantly creating and deleting
tables?  That just doesn't seem right. Perhaps you need to read about temp
tables (depending on what backend database you're using).

--
Mike



Tue, 03 Jan 2012 11:03:52 GMT  
 Code Practice - General Question

released on Thu, 16 Jul 2009 13:40:45 -0400 bearing the
following fruit:

Quote:


>> I'm relooking at a code practice and looking for input.

>> Currently if I read or write to a DB table I first check to see if the
>> table exists and then (if of importance) see if the table is empty.

>> I'm thinking that a better practice may be to check if the table exists
>> just once (maybe at startup) with the assumption made that if it exists it
>> will continue to exist throughout the program.

>> Any input appreciated.

>What's your backend database system? I can't remember the last thing I wrote
>that didn't have a "permanent" database behind it, meaning that I didn't
>have to worry for one moment whether or not the table existed; I just
>assumed it did.

Me too. It would be crazy to be in a situation where that
isn't the case.

--
Jan Hyde



Tue, 03 Jan 2012 16:22:34 GMT  
 Code Practice - General Question

on Thu, 16 Jul 2009 23:03:52 -0400 bearing the following
fruit:

Quote:



>> Thanks guys.

>> I dynamically create and delete various tables, hence the check.
>> I guess if I don't error, then I can assume creation worked and table
>> exists.

>> Only other condition would be at startup.  Again if table never deleted
>> from
>> DB it still should be there and available.

>> Without going into depth of my code, there are several reasons I check if
>> table is empty as different jumps may occur based on result.

>> Consider thread closed.

>You might consider it closed and that's fine, but for anyone else that may
>be interested....this sounds like a very iffy design IMO.  It's one thing to
>create a table ONE time via code, but constantly creating and deleting
>tables?  That just doesn't seem right. Perhaps you need to read about temp
>tables (depending on what backend database you're using).

I agree with everything you just said. I can't imagine a
scenario where the OPs approach is the best one.

--
Jan Hyde



Tue, 03 Jan 2012 16:24:11 GMT  
 Code Practice - General Question
RB Smissaert

Your codes similiar to mine


Quote:
> Don't know what DB you are working with and probably you have code already
> to test,
> but I use this function to check a Firebird table and you may find it
> useful:

> Public Function FirebirdTableExists(strTable As String) As Long

>  Dim strSQL As String
>  Dim rs As ADODB.Recordset

>  '-----------------------------------------------
>  'will give 1 if table exists and has records
>  'will give 0 if table exists, but has no records
>  'will give -1 if table doesn't exist
>  '-----------------------------------------------
>  On Error GoTo ERROROUT

>  'looks odd, but works fine and is a bit faster than selecting a field
>  '--------------------------------------------------------------------
>  strSQL = "SELECT FIRST 1 NULL FROM " & strTable

>  Set rs = New ADODB.Recordset
>  rs.Open strSQL, oADOConn, adOpenForwardOnly, adLockReadOnly, adCmdText

>  If Not rs.EOF Then
>    FirebirdTableExists = 1
>  End If

>  Exit Function
> ERROROUT:

>  FirebirdTableExists = -1

> End Function

> RBS



>> I'm relooking at a code practice and looking for input.

>> Currently if I read or write to a DB table I first check to see if the
>> table exists and then (if of importance) see if the table is empty.

>> I'm thinking that a better practice may be to check if the table exists
>> just once (maybe at startup) with the assumption made that if it exists
>> it will continue to exist throughout the program.

>> Any input appreciated.



Wed, 04 Jan 2012 22:31:37 GMT  
 Code Practice - General Question
Mr. Hahn:

Thanks for excellent reply.  As in most cases depends on reason for check.
I know this -- just trying to see why others do what they do.

----------------------

Another good example I'm currently debating relates to a specific function
call to obtain a region area.

I have a module that has "1" public proc with a number of private procs.
The pubic proc contains a series of "IF's" which determine whether none,
some, or all of the private procs are called (executed).

Each private proc currently contains the call to obtain the region area.  If
more than one of the private procs is selected than that call (and all the
code within that proc) would be executed multiple times.

So the issue is:
1)  Leave the call to obtain the region in the local proc
     Advantage - encapsulation at the lowest level
     Disadvantage -  multiple calls to the same function

2) Obtain the region in the public procedure and pass it as a parameter to
each private proc, or
     Advantage -- only one call
     Disadvantage -- more (parameters) stack usage

3)  Create a module variable, obtain the region in the public procedure, and
use the module variable within each private proc.
      Advantage -- only one call
      Disadvantage -- module variable

I leaning toward the module variable

----------------------
Any comment appreciated

=========================


Quote:
> The answer depends on the likelihood that the table will disappear between
> the time you check it and the time you use it, and that in turn depends on
> where the tables are coming from, how they are created and whether they
> can be deleted while your process is running.  The general rule is as you
> have been applying it - check immediately before use.  But typical
> practice is to relaxe this rule (eg, checking once at startup) if the
> situation warrants it.  For instance, if you are checking a back-end
> database then the chance of the table disappearing is slight, and the
> chance of it disappearing between the start of a procedure and the time
> you use it is so small as to be not worth worrying about, so checking at
> startup is appropriate. OTOH, if you are checking because of the
> possibility of losing the connection, then the disconnect could occur at
> any time and checking immediately before use may be warranted.

> You should also consider the implications of not catching the error. If
> the user would not be fazed by a system-produced error report and knows
> that they can safely restart the application after checking what the
> problem might be, then letting the system catch and report the error might
> be OK, and would save a small amout of processing.  But if it is part of a
> process that needs to be properly terminated and restarted then obviously
> proper error processing is required and that in turn requires checking
> immediately before using it.



>> I'm relooking at a code practice and looking for input.

>> Currently if I read or write to a DB table I first check to see if the
>> table exists and then (if of importance) see if the table is empty.

>> I'm thinking that a better practice may be to check if the table exists
>> just once (maybe at startup) with the assumption made that if it exists
>> it will continue to exist throughout the program.

>> Any input appreciated.



Wed, 04 Jan 2012 22:55:58 GMT  
 Code Practice - General Question
My leaning would be towards obtaining the region in the public procedure and
passing it as a parameter. The advantage is only one call, as you state.  I
could argue that I am still encapsulating at the lowest level, because the
public proc is the lowest level where there _should_ be any variation in the
object being obtained (and if there were any possibility of this rule being
violated, then (1) is the only choice).  And the overhead of passing an
object reference on the stack is negligible.

I do not accept that module variables are always bad as some have suggested,
but I find that I am increasingly using them only where they really are
application-wide, and I am learning to live with the longer parameter lists.


Quote:
> Mr. Hahn:

> Thanks for excellent reply.  As in most cases depends on reason for check.
> I know this -- just trying to see why others do what they do.

> ----------------------

> Another good example I'm currently debating relates to a specific function
> call to obtain a region area.

> I have a module that has "1" public proc with a number of private procs.
> The pubic proc contains a series of "IF's" which determine whether none,
> some, or all of the private procs are called (executed).

> Each private proc currently contains the call to obtain the region area.
> If more than one of the private procs is selected than that call (and all
> the code within that proc) would be executed multiple times.

> So the issue is:
> 1)  Leave the call to obtain the region in the local proc
>     Advantage - encapsulation at the lowest level
>     Disadvantage -  multiple calls to the same function

> 2) Obtain the region in the public procedure and pass it as a parameter to
> each private proc, or
>     Advantage -- only one call
>     Disadvantage -- more (parameters) stack usage

> 3)  Create a module variable, obtain the region in the public procedure,
> and use the module variable within each private proc.
>      Advantage -- only one call
>      Disadvantage -- module variable

> I leaning toward the module variable

> ----------------------
> Any comment appreciated

> =========================



>> The answer depends on the likelihood that the table will disappear
>> between the time you check it and the time you use it, and that in turn
>> depends on where the tables are coming from, how they are created and
>> whether they can be deleted while your process is running.  The general
>> rule is as you have been applying it - check immediately before use.  But
>> typical practice is to relaxe this rule (eg, checking once at startup) if
>> the situation warrants it.  For instance, if you are checking a back-end
>> database then the chance of the table disappearing is slight, and the
>> chance of it disappearing between the start of a procedure and the time
>> you use it is so small as to be not worth worrying about, so checking at
>> startup is appropriate. OTOH, if you are checking because of the
>> possibility of losing the connection, then the disconnect could occur at
>> any time and checking immediately before use may be warranted.

>> You should also consider the implications of not catching the error. If
>> the user would not be fazed by a system-produced error report and knows
>> that they can safely restart the application after checking what the
>> problem might be, then letting the system catch and report the error
>> might be OK, and would save a small amout of processing.  But if it is
>> part of a process that needs to be properly terminated and restarted then
>> obviously proper error processing is required and that in turn requires
>> checking immediately before using it.



>>> I'm relooking at a code practice and looking for input.

>>> Currently if I read or write to a DB table I first check to see if the
>>> table exists and then (if of importance) see if the table is empty.

>>> I'm thinking that a better practice may be to check if the table exists
>>> just once (maybe at startup) with the assumption made that if it exists
>>> it will continue to exist throughout the program.

>>> Any input appreciated.



Thu, 05 Jan 2012 12:06:39 GMT  
 Code Practice - General Question
Again.  Thanks for your time and input.

Will take your comments much consideration.

David


Quote:
> My leaning would be towards obtaining the region in the public procedure
> and passing it as a parameter. The advantage is only one call, as you
> state.  I could argue that I am still encapsulating at the lowest level,
> because the public proc is the lowest level where there _should_ be any
> variation in the object being obtained (and if there were any possibility
> of this rule being violated, then (1) is the only choice).  And the
> overhead of passing an object reference on the stack is negligible.

> I do not accept that module variables are always bad as some have
> suggested, but I find that I am increasingly using them only where they
> really are application-wide, and I am learning to live with the longer
> parameter lists.



>> Mr. Hahn:

>> Thanks for excellent reply.  As in most cases depends on reason for
>> check. I know this -- just trying to see why others do what they do.

>> ----------------------

>> Another good example I'm currently debating relates to a specific
>> function call to obtain a region area.

>> I have a module that has "1" public proc with a number of private procs.
>> The pubic proc contains a series of "IF's" which determine whether none,
>> some, or all of the private procs are called (executed).

>> Each private proc currently contains the call to obtain the region area.
>> If more than one of the private procs is selected than that call (and all
>> the code within that proc) would be executed multiple times.

>> So the issue is:
>> 1)  Leave the call to obtain the region in the local proc
>>     Advantage - encapsulation at the lowest level
>>     Disadvantage -  multiple calls to the same function

>> 2) Obtain the region in the public procedure and pass it as a parameter
>> to each private proc, or
>>     Advantage -- only one call
>>     Disadvantage -- more (parameters) stack usage

>> 3)  Create a module variable, obtain the region in the public procedure,
>> and use the module variable within each private proc.
>>      Advantage -- only one call
>>      Disadvantage -- module variable

>> I leaning toward the module variable

>> ----------------------
>> Any comment appreciated

>> =========================



>>> The answer depends on the likelihood that the table will disappear
>>> between the time you check it and the time you use it, and that in turn
>>> depends on where the tables are coming from, how they are created and
>>> whether they can be deleted while your process is running.  The general
>>> rule is as you have been applying it - check immediately before use.
>>> But typical practice is to relaxe this rule (eg, checking once at
>>> startup) if the situation warrants it.  For instance, if you are
>>> checking a back-end database then the chance of the table disappearing
>>> is slight, and the chance of it disappearing between the start of a
>>> procedure and the time you use it is so small as to be not worth
>>> worrying about, so checking at startup is appropriate. OTOH, if you are
>>> checking because of the possibility of losing the connection, then the
>>> disconnect could occur at any time and checking immediately before use
>>> may be warranted.

>>> You should also consider the implications of not catching the error. If
>>> the user would not be fazed by a system-produced error report and knows
>>> that they can safely restart the application after checking what the
>>> problem might be, then letting the system catch and report the error
>>> might be OK, and would save a small amout of processing.  But if it is
>>> part of a process that needs to be properly terminated and restarted
>>> then obviously proper error processing is required and that in turn
>>> requires checking immediately before using it.



>>>> I'm relooking at a code practice and looking for input.

>>>> Currently if I read or write to a DB table I first check to see if the
>>>> table exists and then (if of importance) see if the table is empty.

>>>> I'm thinking that a better practice may be to check if the table exists
>>>> just once (maybe at startup) with the assumption made that if it exists
>>>> it will continue to exist throughout the program.

>>>> Any input appreciated.



Thu, 05 Jan 2012 23:43:40 GMT  
 
 [ 13 post ] 

 Relevant Pages 

1. General Question on Proper Coding...

2. General Question About Running VBA Code

3. UDT Coding Practices

4. DBGrid Control Questions and general Data Object Questions

5. SSTab question and general unrelated API question..........

6. Coding Best Practices, Guidelines, Standards

7. Good practice question

8. Threading Best Practices Question

9. Data Access Best Practice Question

10. VB6/SQL Server 2000 ADO Best Practices question

11. Question: Best practices with ADODB.Connection

12. Theory question (but need to use in practice)

 

 
Powered by phpBB® Forum Software