Database Access via IDC/HTX


HTX Codes


IF Statements


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 <%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. With standard SQL (including MS Access tables), use one of these forms to name the result so that the htx file can access it. (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.


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
URL: http:// cpcug.org / user / clemenzi / technical / HTML_Examples / HTX_Notes.htm