Using SQL in your pages

Availability

XS version 26 or later

Last update

6 January 2016

Related articles

Adding Custom CSS

Adding Javascript to your pages 

Infradox XS widgets

Infradox XS tags and functions

Introduction

This article describes how you can use SQL in your pages. 

dbhtml()

You can embed SQL by putting the tag

<?xs dbhtml('datasourcename','sql statement') { 

on a line of its own. You must then close the call on a separate line with } ?>

Anything between the line where you have put the <?xs dbhtml tag and the line where you have closed the tag will be parsed.

The below example lists the 10 newest photographers:

<ul>

<?xs dbhtml('photoglist','select top 10 * from photographer where own_profile=1 order by own_id desc') {

<!-- this html is parsed for every row in the resulting dataset -->

<li>
<a href='/contributor/<?xs dbcolumn('photoglist','id',{}) ?>/<?xs dbcolumn('photoglist','displayname',{urlmode:1}) ?>.html'>
<?xs dbcolumn('photoglist','displayname',{}) ?>
</a>
</li>

} ?>

</ul>

dbcolumn()

The dbhtml() tag opens a dataset and parses the html for every row in the dataset. To output data from columns, you can use the dbcolumn() tag. Its syntax is in this format <?xs dbcolumn('datasourcename','fieldname',{parameters}) ?>

datasourcename is the name that you have specified in the dbhtml() tag

fieldname is the name of the database column that you want to output

{parameters} are the parameters that you can use to format the output, parameters are comma separated and in the format name=value. For example {case:upper,urlmode:1}

SQL 

The dbhtml() tag supports SQL statements and Stored procedure calls. We will not publish names of the available stored procedures or names of the database tables for reasons of security. If you want to use SQL in your pages then post a ticket explaining what you want to achieve, and we'll send you the information. The dbhtml() function only supports read-only data. 

Using parameters

You can use parameters in your SQL statement for which values were supplied in the url.

Parameters must start with sql_ and must be between square brackets.

<?xs dbhtml('photographer','select * from photographer where own_id=[sql_photographerid]') { ... } ?>

Fixed value parameters

You can use fixed value (session based parameters). 

Example:

<?xs dbhtml('catlist1','sp_gallerytitles 1359,languageid,subdomainid,userid,userac,userdc,1,80') { ... } ?>

In the above example, the stored procedure SP_GALLERYTITLES is used to retrieve data and this stored procedure requires a number of parameters, i.e. 

  • languageid
    The id of the active language/locale 
  • subdomainid
    The id of the active subdomain (or 0 for www)
  • userid
    The id of the logged in user or 0 if guest
  • userac
    The active access codes
  • userdc
    The active deny codes

 Here's an example that adds a pulldown menu to the main menu and populates the menu items with groups (from the gallery manager) that are created for toplevel 1359.

<div class="menuwrapper fixcenterwidth">
     <div id="mainMenuContainer" xsmenu="1">
          <ul id="left" class="menuitems" style="margin-left:<?xs rproperty('mnu_left','0') ?>px">
<?xs html('xs-leftsidemenu.inc') ?> <!-- inserts the leftside menu items as configured in backoffice -->

<!-- Here we add a menu item for the category groups that exists for top level 1359 -->

          <li id="lmenu_80" order="20"><a id="menu_80" class="arrow pulldownitem menucatlist1" href="/categories/1359/3/1/categories.html" pdid="menucatlist1">News & Editorial</a></li>

          </ul>
     </div> 
</div>

<!--Here's the html to add the groups as menu items to the pulldown menu -->

<div id="menucatlist1" class="dropdownmenu lmenu_80">
     <ul class="counter0">
<?xs dbhtml('catlist1','sp_gallerytitles 1359,languageid,subdomainid,userid,userac,userdc,1,80') {
     <li><a href="<?xs dbcolumn('catlist1','url',{}) ?>"><?xs dbcolumn('catlist1','glg_title',{}) ?></a></li>
} ?>
     </ul>
</div>

The stored procedure SP_GALLERYTITLES takes the following parameters:

  • toplevelid
    The id of the top level for which you want to retrieve groups or galleries
  • languageid
    The current language id, automatically replaced
  • subdomainid
    The current domain id, automatically replaced
  • userid
    The user id, automatically replaced
  • userac
    The access codes, automatically replaced
  • userdc
    The deny codes, automatically replaced
  • groupsgalleries
    Requires a value of 0,1 or 2. 0 to get all child items, 1 to get groups only, 2 to get galleries only
  • menuid
    The id of the menu item that is to be highlighted after opening a URL
Have more questions? Submit a request

0 Comments

Article is closed for comments.
Powered by Zendesk