#!/local/bin/hs -gif
puts { "Content-type: text/html\n\n" } ;

list center = { 
	list h1 = "A SQL Demo" 
} ;
xdescribe center ;

list p = {
	"This page is being generated by HyperScript.",
	"To view the program that generated this page, press ",
	list a = {
		attr href="/support/docs/samples/sqldemo_cgi.txt",
		"here"
	},
	list br,list br,
	"It provides a demonstation of using SQL within HyperScript.",
	"For example, the following table:"
} ;
xdescribe p ;
handle h = sql_open ( "guest", "", "localhost", "test" ) ;
puts { "<pre>mySQL database handle is ",h} ;
if ( h == 0 ) {
  puts "Failed to open MYSQL database connection" ;
  exit ;
}

str text = { 
  "sql_stmt = ",
  "      { \"select LOTID,QTY,EQPID,EQPTYPE,PARTID,STAGE,LOCATION \",",
  "      \"from lot_state where EQPID != '' and QTY < 25 and LOCATION LIKE 'ETCH%'\"", 
  "      } ;",
  " ",
  "stat = sql_query ( sql_stmt, h, \"table\", \"tr\", \"td\" ) ;",
  "if ( stat<0 ) { puts \"Failed to query database\" ; exit ; }",
  " ",
  "attr border=\"3\";",
  "attr cellspacing=\"0\";",
  "attr cellpadding=\"0\";",
  "insert ( table, border ) ;",
  "insert ( table, cellspacing ) ;",
  "insert ( table, cellpadding ) ;",
  " ",
  "xdescribe table ;"
  } ;

*text ;

list p = { 
	"was generated by the following hyperscript commands:"
	} ;
xdescribe p ;

list b = { list pre = text } ;
xdescribe b ;
	 
list p = {
	"The last three arguments, 'table', 'tr', and 'td' gave us the html table ",
	"structure.  All that was needed was to add some attributes to the table ",
	"and display it with the xdescribe function"
} ;
xdescribe p ;

list p = {
	"Instead of displaying directly, you may want to process the SQL ",
	"results after the select, the sql_query function provides ",
	"a number of different structure formats. ",
	"For example, a collection/sequence format ",
	"would be done with the following sql_query: "
} ;
xdescribe p ;

str text = {
	"stat = sql_query ( sql_stmt, h, \"collection\", \"sequence\" ) ;",
	"if ( stat<0 ) { puts \"Failed to query database\" ; exit ; }"
} ;

list b = { list pre = text } ;
xdescribe b ;

*text ;

list p = {
	"...and you get the following structure that you can program against with ease:" 
} ;
xdescribe p ;

list i = { list pre = sdescribe collection } ;
xdescribe i ;

list p = {
	"Another useful structure would be to change the sql_query statement to:"
} ;
xdescribe p ;

str text = {
	"stat = sql_query ( sql_stmt, h, \"structure\" ) ;",
	"if ( stat<0 ) { puts \"Failed to query database\" ; exit ; }"
} ;

list b = { list pre = text } ;
xdescribe b ;

*text ;

list p = {
	"...and you get another useful structure to program against. ",
	"Let's display it in XML format for a different perspective:" 
} ;
xdescribe p ;

list i = { list pre = xsdescribe structure } ;
xdescribe i ;


list p = {
	"Lastly you can get each column out as individual variables."
} ;
xdescribe p ;

str text = {
	"stat = sql_query ( sql_stmt, h ) ;",
	"if ( stat<0 ) { puts \"Failed to query database\" ; exit ; }"
} ;

list b = { list pre = text } ;
xdescribe b ;

*text ;

list p = {
	"...and you get the following parallel array variables:" 
} ;
xdescribe p ;

list i = { list pre = {
		sdescribe LOTID,
		sdescribe QTY,
		sdescribe EQPID,
		sdescribe EQPTYPE,
		sdescribe PARTID,
		sdescribe STAGE,
		sdescribe LOCATION
         }
} ;
xdescribe i ;

exit; 



stat = sql_query ( sql_stmt, h, "structure" ) ;
if ( stat<0 ) {
  puts "Failed to query database" ;
  exit ;
}
list i = { list pre = sdescribe structure } ;
xdescribe p ;

stat = sql_query ( sql_stmt, h ) ;
if ( stat<0 ) {
  puts "Failed to query database" ;
  exit ;
}

sql_close ( h ) ;