Database Access - Raw Notes

Database Access via IDC/HTX

HTX Codes

IF Statements

<%if CurrentRecord EQ 0%>	
  No, <%userguess%> is not...
<%else%>
  Correct! <%Authorname%> is ...
<%endif%>

IF Example

<%begindetail%>
<%IF sele_order EQ "A"%>
  <table border=3 WIDTH=90% cellpadding=4>
  <tr><td><h2><B>Categories: <%sele_category%></B></h2>
<%ENDIF%>
<%IF sele_order EQ "B"%>
  <tr><td><A HREF="/scripts/sr_cat1.idc?super_cat_id1=<%category_id%>">
  <%disp_category%></A>
<%ENDIF%>
<%IF sele_order EQ "C"%>
  </table>
  <HR SIZE=5>
  <table border=1 WIDTH=90% cellpadding=4><tr><td colspan=3><h2>Links: <%sele_category%></h2>
  <tr><th><B>Site Name/Description</B><th>Status Date<th>Status
<%ENDIF%>
<%IF sele_order EQ "D"%>
  <tr><td><A HREF="<%url%>"><%sr_nm%></A><td><%last_updat%><td><%status%>
  <tr><td colspan=3><%sr_ds%>
<%ENDIF%>
<%enddetail%>

Testing if any records were returned

These parameters are available

CurrentRecord
MaxRecords

<%if CurrentRecord EQ 0%> <I><B> No records found to match query. Please try again.</B></I> <%endif%>

http Parameters

All the standard http parameters are available. These can be used to access information about the user and the remote system.

HTTP_USER_AGENT (their browser)
REMOTE_ADDR     (their IP address)

<INPUT TYPE="HIDDEN" NAME="REMOTE_ADDR" VALUE="<%REMOTE_ADDR%>">
<INPUT TYPE="HIDDEN" NAME="REMOTE_HOST" VALUE="<%REMOTE_HOST%>">
<INPUT TYPE="HIDDEN" NAME="REMOTE_USER" VALUE="<%REMOTE_USER%>">
<INPUT TYPE="HIDDEN" NAME="HTTP_USER_AGENT" VALUE="<%HTTP_USER_AGENT%>">

(the http_ may be extra and needs to be deleted)

IDC Codes

Most of these examples are specific to SQL Server and will not work with MS Access.

Limiting the Number of Records Returned

3 different methods to limit the number of records returned

  ODBCOptions: SQL_MAX_ROWS=10 

  MaxRecords: 50

  +set rowcount %mr%            This is database specific

Tracing ODBC Calls

Syntax of Trace command:

ODBCOPTIONS: SQL_OPT_TRACE=1 , SQL_OPT_TRACEFILE=File name

SQL_OPT_TRACE=1 -> Turn Trace On
SQL_OPT_TRACE=0 -> Turn Trace Off

When Tracing is On, each ODBC function call made by Httpodbc.dll is written to the Trace file. If the Trace file already exists, data is appended to it, otherwise the file is created. If tracing is on and no trace file has been specified, the trace is written to c:\SQL.LOG.

Datasource: gcn dsn
Username: sa
Template: temp.htx
ODBCOPTIONS: SQL_OPT_TRACE=1, SQL_OPT_TRACEFILE=\TRACE1.LOG
SQLStatement:
+SET NOCOUNT OFF
+SELECT site_name
+FROM table1
+WHERE upper(rtrim(site_name)) like upper(rtrim('%%%site_search%%%'))

Naming Calculated Values

Using functions in a select statement produces no usable results.

Select Upper(rtrim(last_name)) from name

With standard SQL (including MS Access tables), use one of these forms to name the result so that the htx file can access it.

Select Upper(rtrim(last_name)) as Last_Name from name 
    or
Select Upper(rtrim(last_name))    Last_Name from name

(I don't if Upper & rtrim work in MS Access, but for any function that returns a value, one of these formats is required.)

Some databases use this syntax to name the result.

Select last_name=Upper(rtrim(last_name)) from name

Formatting Dates

select convert(char,getdate(),1)

   Returns 06/07/96

select convert(char,getdate(),7)

   Returns Jun 07, 96  

BeginDateRequest=convert(char,VacRequest.BeginDateRequest,7)

The third parameter controls the format - database dependent code.

Comments

Comments /* ....*/

+/*
+ comments
+*/

Database dependent - will not work with MS Access

May be database specific

select status= case 
	         when status is null 
		      then ' ' 
	         else status 
	       end 
	from cal1

	+ SET NOCOUNT OFF
	+ SELECT id AS MyId FROM [Motor Works] WHERE Name = '%Motor%'
	+ SET NOCOUNT ON
	+ INSERT INTO Car (motor Works ID) VALUES (%MyID%);

This syntax is specific to SQL Server.
There is no "set nocount off" statement in MS Access.

Author: Robert Clemenzi - clemenzi@cpcug.org