Database Documentation by InfoDial, Inc.

USING SQL

As a part of DBSearch you can develop and use SQL queries. Typically an SQL query that is used to apply rule(s) to the search that you are setting up is generated within the Access Database itself. To use the query after you have saved, tested and named the query you would then use the name of the Query as the value in the DBSearch tbl field. Future searches then returns data that has been processed by that query.

If your SQL Query requires input from the user, you will need to build an SQL Datafile that will be brought into the DBSearch system at the time of search. You will be able to pass form parameter(s) into the SQL file to customize the SQL Query at search time.

You will need to have both a strong knowledge of SQL and Access Databases to use this feature. In addition to knowledge we also request that you use only Access 97 or better to develop DBSearch SQL Queries.

SQL DATAFILE PARAMETERS

INPUT	POST METHOD	GET METHOD
tbl	<input type="hidden" name="tbl" value="SQL-Embed">	tbl=SQL-Embed
SQLFILE	<input type="hidden" name="SQLFILE" value="\path\filename.txt">	SQLFILE=/filename.txt

SQL tbl Tag
You will not need to specify a specific table - rather you will need to use the value of SQL-Embed to use SQL with DBSearch.

SQLFILE
Defines the path to the SQL Data File.

How to make an SQL Datafile

An SQL Datafile is a plain text file (.txt) that contains pre-tested / working SQL. To make an SQL Datafile follow these steps:

Step 1
Build a Query within Access in the database you will be running the query on with DBSearch.

Step 2
After determining that the data and the query are working perfectly and that the data returned is acceptable then use the View\SQL View Command on the Access Toolbar.

Step 3
Now using the Copy command Highlight and copy all the SQL to the Clipboard and then paste it to the notepad and save the file with a .txt extension.

USING SQL PARAMETERS

INPUT	POST METHOD
Form Tag	<input type="text" name="SQLPARAM_(YourParameterName)">

SQLPARAM_
You can define any name to the parameters that you use with the SQLPARAM_ method just specify the parameter name and make sure that the parameter Tag is placed in the SQL Data file. An example of a parameter added to SQL is below. You can use more than one Parameter tag at a time (make sure to specify different names) you can also use parameters more than one time in the SQL.

Plain SQL (unaltered)

SELECT Table1.id, Table1.name
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2
WHERE (((Table1.id)>0));

SQL With Parameter Tag in Place

SELECT Table1.id, Table1.name
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2
WHERE (((Table1.id)>##param1##));