Browse DevX
Sign up for e-mail newsletters from DevX

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



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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, _
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:

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, _
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.



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