Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic, Enterprise
Expertise: Intermediate
Feb 3, 1999

Establish a Data Dictionary

If your SQL looks like this, you need to ask yourself how much code you'd have to inspect and revise if you decided to change a database field or table name, as frequently happens during development:
 
SQLString = "SELECT [first name], [last name], " & _
	"[line preferences]" & _
	" FROM [imaging users] WHERE [user code] = " & _
	"'" & Trim(UCase(UserIDText.Text)) & "'"

ODBCstatus = SQLExecDirect(ODBChandle1, SQLString, _
	Len(SQLString))
What happens if SQL command conventions (field name delimiters) change? Because a compile doesn't reveal such name misspellings or convention flaws, code in obscure procedures can be in production before defects are detected.

Our group established a table and field dictionary in a module used for a recent large project. This helped us ensure that we correctly pasted table and field names into all SQL commands. It also provided a repository that simplified maintenance.

As database resource names changed or new name-delimiting conventions were required, we revised the dictionary before recompiling. We also used the dictionary to convey descriptive information about tables and fields to developers. Our dictionary looks like this:

 
'tables:
Public Const tblUsers As String = "[imaging users]"

'data fields:
Public Const fldFirstName As String = "[first name]"
	'16 characters
Public Const fldLastName As String = "[last name]"
	'16 characters
Public Const fldLinePreferences As String = _
	"[line preferences]"
	'20 characters
Public Const fldUserCode As String = "[user code]"
	'10 characters
Our SQL looks like this:
 
SQLString = "SELECT " & fldFirstName & _
	", " & fldLastName & _
	", " & fldLinePreferences & _
	" FROM " & tblUsers & _
	" WHERE " & fldUserCode & " = " & _
	"'" & Trim(UCase(UserIDText.Text)) & "'"

ODBCstatus = SQLExecDirect(ODBChandle1, SQLString, _
	Len(SQLString))
Programmers don't have to know the actual names of database components. They always use the constants that refer to the database components. A clean compile ensures you'll use correct names and name-delimiting conventions in your SQL statements.
Doug Hagy
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date