HareScript's SQL Concepts

Sitemap |

Clients

HareScript's SQL Concepts

Home | Downloads | HareScript | Websites/CMS | Applications

	HareScript
	Databases


	Introduction

Non-SQL databases

So far we've seen HareScript offering a standardized SQL interface to a variety of databases and to record arrays created in HareScript itself. But HareScript can also be used as a SQL interface to data containers that don't even support SQL themselves.

As our first example, we'll show the LDAP driver. LDAP is the Lightweight Directory Access Protocol, which implements a hierarchical database. SQL is mainly suited to relational databases, and most LDAP servers do not offer an SQL interface. The HareScript LDAP driver abstracts the complexities of LDAP and allows you to treat it as a normal SQL database.

The following script would print the name and e-mail addresses of the first two students from the faculty of Computer Science at the University of Twente who have at least two initials in their name:

<?wh

LOADLIB "wh::dbase/ldap.whlib";

LOADLIB "wh::internet/tcpip.whlib"; // Needed for ResolveHostName

/* Connect to the server ldap.utwente.nl. Port 389 is the default

   LDAP port, and the currently empty parameters are used for

   username and password information. */

INTEGER ldaptrans := OpenLDAPConnection(

                    ResolveHostname("ldap.utwente.nl"), 389, "", "");

SetLDAPSearchScope(ldaptrans, 

     "ou=Informatica, ou=Studenten, o=Universiteit Twente, c=NL", 1);

/* Set up a table 'ldap_persons' to represent the contents of the

   LDAP server 'dn', 'cn' and 'mail' are attributes (cell names) on

   the LDAP server. */

TABLE <STRING dn, STRING cn, STRING mail> ldap_persons;

ldap_persons := BindTransactionToTable(ldaptrans,"ldap_persons");

// Select the information we were looking for

RECORD ARRAY persons := SELECT cn, mail

                          FROM ldap_persons

                         WHERE mail LIKE "?.?.*" // Two initials

                      ORDER BY cn          // Sort by common name

                         LIMIT 2;          // Show first two students

// Print the search results

FOREVERY(RECORD person FROM persons)

  Print(person.cn || ":" || person.mail || "\n");

The output of this script would look something like this:

X:\testfiles>runscript ldap.whscr

"Doe,J.A.(stud-INF)":j.a.doe@student.utwente.nl

"Smith,J.F.(stud-INF)":j.f.smith@student.utwente.nl

You can also use HareScript to manipulate XML files through SQL. WebHare offers a couple of helper functions to manipulate these documents, since XML documents are highly hierarchical, but a combination of XPATH and SQL makes manipulating XML documents simpler than ever before.

First, a very simply but typical XML document:

<books>

  <book id="4">

    <author>John Smith</author>

    <title>About a John</title>

  </book>

  <book id="8">

    <author>Jim Doe</author>

    <title>My first book</title>

  </book>

</books>

Manipulating the document by combining SQL and XPATH is then pretty simple:

<?wh

LOADLIB "wh::filetypes/xml.whlib";

BLOB xmldata   := ....; // The original XML document, contained in a

                        // file or database

INTEGER xmldoc := ParseXML(xmldata);

// Get all book ids in the database

RECORD ARRAY books := SELECT id 

                        FROM SelectXML(xmldoc, "/books/*");

// Get the title of the book written by John Smith

RECORD johnsbook := SELECT title 

                      FROM SelectAndSquashXML(xmldoc, "/books/*")

                     WHERE author="John Smith";

With HareScript, you can even use SQL to manipulate files on your hard disk. The following code, when executed as a stand-alone HareScript, will print the name and title of all Word documents in the current directory:

<?wh

LOADLIB "wh::files.whlib";

LOADLIB "wh::filetypes/detect.whlib";

/* Open the file, and use the standard HareScript file type detection

   libraries to extract the tile from a (Microsoft Word) document */

STRING FUNCTION GetDocTitle(STRING filename)

{

  BLOB filedata := MakeBlobFromDiskFile(filename);

  RECORD fileinfo := DetectFileType(filedata,filename);

  RETURN RecordExists(fileinfo) ? fileinfo.title : "";

}

// Get all documents in the current directory, and their title

RECORD ARRAY docfiles := SELECT name // Select the filename

                       // And the file's title

                              , title := GetDocTitle(name)

                      // Get a folder listing

                           FROM ReadDiskDirectory(".","*.*")

                      // Get all word docs

                          WHERE ToUppercase(name) LIKE "*.DOC"

                      // Sort by filename

                        ORDER BY name; 

// Remove all documents without a title

docfiles := SELECT * FROM docfiles WHERE title != "";

// Print the search results

FOREVERY(RECORD doc FROM docfiles)

  PRINT(doc.name || ' (' || doc.title || ')\n');

An example output of this script could be:

X:\testfiles>runscript dumptitles.whscr

agenda.doc (15-02-2004 meeting schedule)

company.doc (Company information)

test.doc (Test document containing PNG images)

In section 'Introduction'

HareScript and SQL

Using HareScript expressions inside a select

Using HareScript functions to build select results

Database abstraction

Reselections and multiple data sources

Null values

Non-SQL databases

Summary

Top