Built In Stored Procedures

Built-in Stored Procedures

The OpenBase SQL database comes with a number of built-in stored procedures to assist you with application development.

videoicon3.png Video: Built-In Stored Procedures

Remote Query

OpenBase SQL supports the ability to perform queries in other database servers using remoteQuery() and remoteJDBCQuery() procedure calls.

The remoteQuery() procedure takes login information for another OpenBase SQL Database, performs the query, and then returns the results to the calling application.

Here is the syntax:

call RemoteQuery(databaseName, ipaddress, login, password, sqlstring)

Here is an example of a call:
call RemoteQuery(“pubs”, “127.0.0.1”, “admin”, “mypw”, “select * from authors”)

The remoteJDBCQuery() procedure takes remote query a step farther by enabling remote queries to be performed in any JDBC-compliant data source, including Oracle and Sybase databases, all from your OpenBase SQL database.

Here is the syntax:

call RemoteJDBCQuery(jdbcDriver, jdbcURL, login, password, sqlstring)

Here is an example of a call:
call RemoteJDBCQuery("com.sybase.jdbc2.jdbc.SybDriver", "jdbc:sybase:Tds:192.168.0.2:11222/pubs", "sa", "", "select * from authors")

Insert Select

While OpenBase does support INSERT-SELECT (in OB v9.1), the InsertSelect() stored procedure provides some advantages. For one thing, InsertSelect() will operate on and transfer blobs from one table to another. The downside is that it will not work on user defined temporary tables.

Here is the syntax:

call InsertSelect(sourceSelectQuery, targetTable, targetColumnList)

There are a few important things to note about the parameters. If the sourceSelectQuery has any quotes in it, they must be escaped so that the query is seen as a string. The targetTable must be a previously created table, and the targetColumnList is a comma-separated list of column names for the target table. Be sure to remove all spaces.

Here is an example of how this stored procedure can be used:

call InsertSelect(“select au_fname, au_lname from authors where au_fname like ‘scott’ “, “myAddressTable”, “firstname,lastname”)

This will move the result of the select query into the myAddressTable table.

Lucene Search Engine

While OpenBase SQL offers word indexes for indexing blob content for use in LIKE searches, the Lucene search engine has proven to be extremely efficient in finding and ranking text data. OpenBase now uses a combination of SQL and Lucene stored procedure calls to perform knowledgebase searches of hundreds of megabytes of email text.

To take advantage of this capability, first call the Lucene stored procedures and then plug the result into an SQL statement that returns the records that satisfy the search.

Here’s how to get it setup:

First, create the Lucene index. Because the index is stored inside the OpenBase database, you don’t have to worry about the individual index files.. You will, however, need to update the index manually each time your database changes. This can be done as a trigger or once a day, using an automatic stored procedure.

call LuceneRebuildIndex(tableName, columns, qualifier)
call LuceneSearchIndex(tableName, columns, query, limit)

There are three parameters.  The tableName, columns and the qualifier.  If the qualifier is an empty string, all records will be indexed.  You can specify an sql qualifier (ex: index=1) to limit the records indexed.  Columns definitions are in the following format:  1stColName/shouldIndex/shouldTokenize/shouldStore:2ndColName/shouldIndex/shouldTokenize/shouldStore:etc

These are the three attributes that appear between the slashes as part of the column definition:
shouldIndex (y or n): Should this column be indexed?
shouldTokenize (y or n): most of the time this should be no (n)
shouldStore (y or n): If the column is stored, it can be retrieved in the search

Here is some additional information copied from the Lucene site (http://darksleep.com/lucene) on what each of these mean:

Indexed for searches - sometimes you'll want to have fields available in your Documents that don't really have anything to do with searching. Two examples I can think of off the top of my head are creation dates and file names, so you can compare when the Document was created against the file modification date, and decide if the document needs to be reindexed. Since these fields won't ever make sense to use in an actual search, you can decrease the amount of work Lucene does by marking them as not indexed for searches.

Tokenized prior to indexing - tokenizing refers to taking a piece of text and cleaning it up, and breaking it down into individual pieces (tokens) for the indexer. This is done by the Analyzer. Some fields you may not want to be tokenized, for example a serial number field.

Stored in the index - even if a field is entirely indexed, it doesn't necessarily mean that it'll be easy for Lucene to reconstruct it. Although Lucene is a search index, and not a database, if your fields are reasonably small, you can ask Lucene to store them in the index. With the fields stored in the index, instead of using the Document to locate the original file or data and load it, you can actually pull the data out of the Document. This works best with fairly small fields and documents that you'd need to parse for display anyway. Some fields contain bulk data and are so large that you don't really want to store them in the index.

Here is how to index a column named textdescription in a table called EmailDetail. The qualifier is blank to indicate all records. However, you could index just a subset of your table by specifying a qualifying clause.

call LuceneRebuildIndex('EmailDetail', 'textdescription/y/y/n', '')

The search can be executed like this:

call LuceneSearchIndex(tableName, columns, query, limit)

Here is an example of seaching:

call LuceneSearchIndex('EmailDetail', '_rowid', 'textdescription:(foo)', '200')

This search will return the first 200 occurances of “foo” in the textdescription index. The result will be a list of _rowid values for the matching records.

Most of the time you will want to integrate this search into a query. Here is an example of how you would do that using the example above:

SELECT * FROM mytable WHERE _rowid IN (call LuceneSearchIndex('EmailDetail', '_rowid', 'textdescription:(foo)', '200') )

If you have a large volume of data, this method of searching text is extremely fast.

Credit Card Processing

OpenBase SQL supports integrated credit card transaction processing. This makes it easier for you to develop web sites that deliver secure, real-time credit card payment processing through built-in stored procedures in the OpenBase database server. This mechanism supports all major credit cards including VISA, MasterCard, and American Express. It works by calling a Java stored procedure, which contacts PayPal Pay Flow Pro over SSL, to either approve or refuse the transaction.

There are two ways to process credit cards. The easiest way is to use the Sale method which completes the transaction in a single call. There is also a Authorize/Capture method which completes the transaction in two calls.

Here is an example of how to do the sale in a single call. The difference between the two routines are whether you are specifying the CVV2 code.

call PayflowSale(creditCardNumber, expDate, amount, street, zip, currencyCode, invoiceNumber, pathToPreferenceFile)

call PayflowSaleCSC(creditCardNumber, expDate, amount, street, zip, currencyCode, invoiceNumber, pathToPreferenceFile, CVV2)

creditCardNumber - credit card number with no spaces or punctuation.
expDate - date value in "mmdd" format
amount - number and decimal place with no commas or $
street - the billing street address
zip - the billing zip code
currencyCode - empty indicates the US currency. Refer to Pay Flow Pro documentation for other currencies.
invoiceNumber - optional invoice number to connect the transaction with your records
pathToPreferenceFile - path to the configuration file
CVV2 - the security code on the card

Note: As of OpenBase 11.1 the credit card processing system has been updated for PayPals new API. The new API returns status="0" instead of "Approved" from the credit card gateway. The old APIs have been deprecated by Verisign so you will need to upgrade your applications.

Here is an example of how to call these routines:

call PayflowSaleCSC(“5400000000000005", "0104", "14.99",
    "123 ABC Street", "99001","", "INVOICE1234",   
    "/Library/OpenBase/Procedures/ProcessCreditCardPayment/payflow.txt",
    “1234”)

It is important to note that there are two new columns: currencyCode and invoiceNumber. If you are processing payments in US dollars then you can pass in blank for currencyCode to default to US currency. The invoice number is provided for your own reference and can be left blank or can be filled in.

first way is a two step process where you first authorize the card and then capture the payment.

The procedure call for this is:

call PayflowAuthorizeCSC(creditCardNumber, expDate, amount, street, zip, 
    currencyCode, invoiceNumber, pathToPreferenceFile, CVV2)

call PayflowAuthorize(creditCardNumber, expDate, amount, street, zip, 
    currencyCode, invoiceNumber, pathToPreferenceFile)

Here is how you capture the payment using the reference returned from the authorize:

call PayflowCapture('V79E1DF1868C', '/Library/OpenBase/Procedures/ProcessCreditCardPayment/payflow.txt');

pathToPreferenceFile represents a configuration file on the server that includes account password information. This is used to route the payment to your account through a clearing house, PayPal.

There is a sample preferences file for the PayPal interface in /Library/OpenBase/Procedures/ProcessCreditCardPayment called paypal.txt. The paypal.txt file needs to be copied and edited to reflect your PayPal account information. You will need to replace the values that are specific to your account, including the vendor name, user name and password. You will also need a PayPal hostname. The hostname is set to "pilot-payflowpro.paypal.com" when it is in test mode and "payflowpro.paypal.com" when you go live.

Credit card refunds are processed using these stored procedures:

call PayflowVoid('V79E1DF1868C', '/Library/OpenBase/Procedures/ProcessCreditCardPayment/payflow.txt')

The PayflowVoid() procedure takes a transaction reference and voids it.

E-mail Stored Procedure

The E-mail stored procedures makes it easy to send and fetch email using a simple stored procedure call. You can pass the mail body as a string or as a blob (for attachments and long messages). Here is a summary of options:

Fetching E-Mail

The ability to fetch email from a pop server and store it in the database is particularly useful for keeping an email archive of mail or implementing database-driven communications with your customers. We use these routines at OpenBase to communicate effectively with customers using standard email clients.

The FetchEmailFromPop stored procedure will transfer email into a table and set of columns that you specify. Here is an example:

call FetchEmailFromPop(pop_server, pop_login, pop_password,
    target_table, subject_col_name, to_col_name, from_col_name,
    full_from_col_name, date_col_name, cc_col_name, contentType_col_name,
    body_col_name, message_text_col_name, parentPK_col_name,
    set_flag_col_name, set_flag_value, email_fetch_limit )
Parameter Description
pop_server your mail server. Example: mail.apple.com
pop_login & pop_password name and password of your pop account
target_table name of the table which you want the email to be transferred to
subject_col_name column in the target table that will hold the subject of the message
to_col_name column containing who the mail is addressed to
from_col_name column containing the name of the person the email is from. This may only be the email address.
full_from_col_name column containing the name and email address of the person who sent the email
date_col_name column containing the date the message was sent
cc_col_name column containing the information about who else received the message
contentType_col_name the content type of the email message found in the email header
body_col_name object or text column which will include the entire body of the message and attachments
message_text_col_name object or text column which will have the converted text of the message with all attachments stripped out
parentPK_col_name this parameter is an optional identifier used to associate related messages. It is set to point to another, related message that has been inserted into the same table
set_flag_col_name name of a flag column which will be set to an initial value when email is inserted into the table. This feature is especially useful for detecting newly inserted messages that have not been processed
set_flag_value the value you want the flag column to be set to
email_fetch_limit the number of emails you want to process in one call. It is recommended that you do not allow too many emails to be processed in a single call because of memory constraints on the Java runtime. Alternatively, we recommend that you process them in chunks of 10 or 20.

Sending Email

The following examples show how to send a limited text string as an email body. Itis limited to a maximum parameter string size:

call SendEmailStringToAddresses(fromEmail, replyToAddress, evpFromAddress,
    subject, content-Type, smtp, sql_query, body)
call SendEmailString(fromEmail, replyToAddress, evpFromAddress,
    subject, content-Type, smtp, toAddress, ccAddress, body)

The smtp address should be specified as either a simple domain name (i.e. smtp.host.com) for servers that do not require authentication, or a domain followed by authentication information in cases where it is required. In cases where autherication is necessary, the smtp parameter should be specified as "smtp.host.com:user:password".

The following examples show how to send larger emails or redistribute emails that have been read from a pop server:

call SendEmailBlobToAddresses(fromEmail, replyToAddress, evpFromAddress,
    subject, content-Type, smtp, sql_query, tableName, columnName, primaryKey)
call SendEmailBlob(fromEmail, replyToAddress, evpFromAddress,
    subject, content-Type, smtp, toAddress, ccAddress,
    tableName, columnName, primaryKey)

Example:

call SendEmailStringToAddresses( 'support@openbase.com ', ' support@openbase.com ',
     'support@openbase.com ','Test Message Subject','text/plain',
    'smtp.somewhere.com:myuser:mypassword','select email_address from customers',
    'This is a test message')

This select statement retrieves the list of email addresses that you intend to mail to. Alternatively, the following stored procedure will send an email directly to a single person.

call SendEmailString(fromEmail, replyToAddress, evpFromAddress,
    subject, content-Type, smtp, toAddress, ccAddress, body)

Example:

call SendEmailString( 'support@openbase.com ', 'support@openbase.com ',
    ' support@openbase.com ','Test Message Subject','text/plain',
    'smtp.somewhere.com', 'info@openbase.com ', ' cc@openbase.com ',
    'This is a test message sent to info@openbase.com ')

Another example is when the body of the message is already stored in the database. This is most likely the case if the email was read from a pop server.

When you use a pointer (object or text) as shown in the following examples, the stored procedure will fetch the body of the message from the database and use that for sending email.

call SendEmailBlobToAddresses(fromEmail, replyToAddress, evpFromAddress,
    subject, content-Type, smtp, sql_query, tableName, columnName, primaryKey)
call SendEmailBlob(fromEmail, replyToAddress, evpFromAddress, subject,
    content-Type, smtp, toAddress, ccAddress, tableName, columnName, primaryKey)

Example:

call SendEmailBlob( 'support@openbase.com ', 'support@openbase.com ', 
    'support@openbase.com ', 'Test Message Subject', '',
    'smtp.somewhere.com', 'info@openbase.com ','text/ plain',
    'This is a test message sent to info@openbase.com ')

Stock Quotes

The StockQuote(StockCode) and StockQuotes(StockCodeArray) stored procedures provide real-time stock quotes from the internet.

Here is the syntax:

call StockQuote(StockCode)
call StockQuotes (StockCodeArray)

Here is an example of how they can be used:
call StockQuote('AAPL')
call StockQuotes('AAPL,IBM')

Currency Exchange

The CurrencyExchange() stored procedure provides the current exchange rate between two currencies. Here is the syntax:

call CurrencyExchange(OriginalCountry, ExchangedCountry)

Here is an example of how it is called:
call CurrencyExchange("US","Canada")

Synchronizing Databases

Database synchronization or replication can also be performed using stored procedures.

Synchronization is a process by which two tables in different databases are compared and brought up to date with one another. Once it is set up in OpenBase Manager, you can use a stored procedure from your application to perform the synchronization. Here is an example:

call ReplicateDatabase()

Calling ReplicateDatabase() will perform the replication process on all tables defined in the OpenBase Manager for that database. Always perform the call on the remote database, since that is the database that always holds the information about the master.

Synchronize Cleanup

Synchronization or replication can sometimes leave backup tables in your database when conflicts are found and old records need to be backed up (a set-able option in the OpenBase Manager). This sometimes leaves backup tables in your database. While those tables are harmless, this stored procedure will remove them.

To drop the backup tables you can call RemoveReplicationBackup() from the machine where they appear:

call RemoveReplicationBackup()

Global Log

The Global Log stored procedure allows application designers to store error messages in a central log that is not specific to any one database. Storing error messages centrally makes it easier for application designers to better monitor what’s going on with their apps. Logging to a central file is accomplished using the GlobalLog() stored procedure.

Here is an example:

call GlobalLog(logName,logMessage)

Here is how it might be used:
call GlobalLog(“myGlobalLogFile”,”This is an error message”)

ImportFile Stored Procedure

ImportFile() is a stored procedure designed for users who need faster imports.

To use the ImportFile() procedure you must have an OpenBase PowerCenter or demo license installed, and you must be logged into the database either as the admin user or a user with admin privileges (group=admin). The file to be imported must also be compatible with the format of the import/export mechanism in openisql.

Example: Importing 10240 records into a table with a lot of indexes

First, create a file to import (using a large version of the contacts table in the company database):

openbase 1> export ascii /tmp/data
Enter Column Separator (\t=TAB,\n=CR): \
Enter Record Separator (\t=TAB,\n=CR): \n
Enter select query and type go to export data.
export openbase 1> select firstname, lastname from contacts
export openbase 2> go
contacts    10240 rows exported - 0.370 seconds
openbase 1>

Next, import the rows back into the Company database. Keep in mind that the contacts table has 93 columns and 8 indexes which need to be maintained, even though most of the columns imported are NULL.

openbase 1> call ImportFile("/tmp/data", "/tmp/outmessages","\ t","\n")
openbase 2> go
SQL executed - 10.984 seconds
openbase 1>

The ImportFile() procedure always triggers a database cleanup, which will take longer on a larger database than a smaller database. If you have a large database and a small import set, it may be quicker to import it using openisql. If, on the other hand, you have a very large data set (such as 1+ million rows to import) then the ImportFile() procedure will work best.

ASCII Backup Stored Procedure

You can create an ASCII Backup of your database by calling the AsciiBackup() stored procedure. This procedure will create a file named after your database with a .bck extension in the path you specify. The file can be loaded using the restore command in openisql.

call AsciiBackup ("/Library/OpenBase/Backup/")

BackupToPath Stored Procedure

If you do not want an ASCII dump of your data sitting around, a better solution might be to schedule a tar backup of your database. The procedure call for this is:

call BackupToPath ("/PATH")

/PATH is the path to a directory. A new time-stamped file will appear in this directory when you perform the backup. This backup method is extremely effective and secure, because even if someone gets access to the backup files, they cannot get in without having a valid password.

Cleanup Stored Procedure

The cleanup stored procedure performs a database cleanup in the background, returning control to the caller immediately. A database cleanup empties the internal log files and can improve startup times significantly. It is recommended that a cleanup be performed periodically on all production databases.

call CleanupDatabase()

SaveBlobToFile(blob_key, file_path)

The SaveBlobToFile stored procedure allows you to export a blob (image or text) to the file system. It takes two parameters: a blob key, which is an 8-character identifier for a blob; and a file path, which must be valid and greater than 5 characters in length.

Here is an example:

call SaveBlobToFile ("abcf0100", "/PATH/myimage.tif")

webfones_dial( passcode , dialnumber , message )

The webfones_dial() stored procedure provides phone dialing through the webFones phone service (www.webfones.com). This is only for use with this VoIP phone service.

call webfones_dial ("5sga667ssaSA", "16035551212","Calling Joe")

The webfones passcode is a unique extension identifier which rings with the message "Calling Joe". When the user picks up the handset it dials the number 16035551212.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License