OpenX Home

OpenX - a shortcut to rapid database development and connectivity.
What is OpenX?
OpenX component is a set of high-performance software interfaces for data access that provides universal data integration over an enterprise's network.

If you're building database driven desktop application or web site, you can use its power to programmatically access and manipulate data by writing only few strings of code.

Its application-level database-programming interface allows enterprise programmers to write applications from any OLE-compliant language, including C#, C/C++, JScript, Java, Visual Basic, and VBScript.

OpenX is the fastest way to interact with RDBMS, because it makes direct calls to vendor's native database APIs, thus eliminating need in all possible intermediate layers such as ODBC/ADO. It's designed and optimized to achieve maximum performance under heavy load.

Thinking of upgrading your RDBMS?
Moving to another RDBMS vendor?
OpenX protects your investment by supporting all major database vendors on the most popular operating systems.
OpenX ASP Edition

OpenX ASP Edition will save you hours on creating and processing HTML Forms, mapping form elements to database fields, performing HTTP file uploads, validating input (credit cards and email addesses), and saving HTML Form data into your database.
What is OpenX.DBMail?

OpenX.DBMail is a full-featured e-mail integration component capable to collect email messages from POP3 server, send messages via SMTP server, extract and parse delimited email data, use RDBMS (MSSQL, Sybase, Oracle, MySQL) to store messages. More info
OpenX explained

  • OpenX acts as DB provider using Native Database API.
  • OpenX provides small but multifunctional command set compatible with most of programming languages.
  • At present, OpenX is an ATL COM/COM+ component.
    Other versions, a EJBTM version and a CORBATM version, will be made available in the near future.
  • OpenX is fully compatible with Active Server Pages, and Microsoft's new development environment - .NET
  • Its low-level interface offers data access optimizations and access to database-specific features.
  • Easy and powerful HTML Form processing and HTTP File uploading by OpenX ASP Edition. See how!

    • Supported database vendors:
  • Microsoft SQL Servers 6.5 / 7.0 / 2000
  • Oracle 7 / 8i / [9i *]
  • Sybase ASE 11.0 and up, or ASA 6.0 and up
  • IBM DB2 *
  • Informix *
  • InterBase *
  • Centura SQLBase *
  • MySQL
  • ODBC compliant databases *
    • * beta version

    Sybase Adaptive Server, Sybase Open Client, Sybase SQL Server are registered trademarks of Sybase Inc.;
Microsoft Windows, SQL Server, COM+ are registered trademarks of Microsoft Corp.;
Oracle 7 / 8i / 9i are registered trademarks of Oracle Corp.;
IBM DB2 is registered trademark of IBM Corp.;
Informix is registered trademark of Informix Corporation;
InterBase is registered trademark of Borland Software Corporation;
SQLBase is registered trademark of Centura Software Corporation


Purchase OpenX

Pricing & Licensing
OpenX has designed tiered database vendor licensing components to allow complete scalability of your connectivity solution, and offers several flexible, cost-effective options for licensing. The information below on this page will help you find the OpenX pricing and licensing strategy that is most cost-effective for your organization.
Buy OpenX COM/COM+ Professional [MS SQL Server or Sybase ASE/ASA or Oracle] now,
and upgrade to OpenX COM/COM+ Professional [All-In-One] for only $100 when it becomes available!
Product
Price (USD)
Professional Licence
OpenX [Sybase ASE/ASA] COM/COM+
 $299
OpenX [MS SQL Server] COM/COM+
 $299
OpenX [Oracle] COM/COM+
 $299
OpenX [MySQL] COM/COM+
 $15
OpenX [All-In-One] COM/COM+
$499
OpenX ASP Edition 4-in-1 [MSSQL, Sybase, Oracle, MySQL] COM/COM+
 $349
OpenX ASP Edition [MySQL] COM/COM+
 $29
OpenX.DBMail COM/COM+
 $99
OpenX.DBMail and OpenX ASP Edition 4-in-1
$399
Enterprise Licence
OpenX [Sybase ASE/ASA] COM/COM+
 $1,499
OpenX [MS SQL Server] COM/COM+
 $1,499
OpenX [Oracle] COM/COM+
 $1,499
OpenX [MySQL] COM/COM+
 $70
OpenX [All-In-One] COM/COM+
$2,499
OpenX ASP Edition 4-in-1 [MS SQL, Sybase, Oracle, MySQL] COM/COM+
 $1,549
OpenX ASP Edition [MySQL] COM/COM+
 $129
OpenX.DBMail COM/COM+
 $299
OpenX.DBMail and OpenX ASP Edition 4-in-1
$1,700

-- beta version available

-- Time limited offer.
OpenX.DBMail and OpenX ASP Edition supports

  • Microsoft SQL Server v6.5 / v7.0 / v2000
  • Oracle v7 / v8i
  • Sybase ASE v11.0 / v11.5 / v11.9.2 / v12.0 / v12.5
  • MySQL v3.23 or higher

    • OpenX Professional nonexclusive Development or Server Deployment license allows you to install one single copy of OpenX software on one signle Development Workstation or on one single Server only.

    OpenX Enterprise nonexclusive Development or Server Deployment license allows you to install OpenX software on unlimited number of Development Workstations and Servers within one organization.
    OpenX Enterprise also includes redistributable licence.

    Note: all options include free updates (within major version) and free web-based support. For info about upgrade options and custom versions, contact our Sales Department

    Please contact our Sales Department for any further questions regarding purchasing.
    General Inquiries: info@openx.ca


EULA

End-User License Agreement for OpenX Software

Copyright (c) 1999-2001 OpenX Technologies Inc.
All Rights Reserved.

AGREEMENT. After reading this agreement carefully, if you do not agree to all of the terms of this agreement, you may not use this software.

This software is owned by OpenX Technologies Inc. and is protected by national copyright laws and international copyright treaties.

  • DEFINITIONS.
    "Application" means a software application that you develop and that incorporates one or more OpenX objects. "Software" means the following objects: OpenX, OpenX ASP Edition, OpenX.DBMail. When used in this Agreement, the words "you" and "your" mean the party purchasing a license to use the Software.
  • GRANT OF LICENSE AND PROHIBITIONS.
    This software is licensed to you. You are not obtaining title to the software or any copyrights. You may not sublicense, rent, lease, convey, modify, translate, convert to another programming language, decompile, or disassemble the software for any purpose. The license may be transferred to another if you keep no copies of the software and notify OpenX on such transfer. Permission must be obtained before mirroring or redistributing the evaluation or beta copies of the software.
  • USE AND EVALUATION PERIOD.
    You may use one copy of this software on one client computer. A copy of this software is considered in use when loaded into temporary memory (i.e., RAM) and/or installed on a permanent storage device (i.e., hard disk, CD-ROM, etc. You may also use a copy of the software on a home or portable computer, provided only one copy of the software is in use at a time. You may use an evaluation copy of the software for only twenty (20) days in order to determine whether to purchase the software. You may use a beta copy of the software for only ninety (90) days in order to test its features.
  • MULTI-COMPUTER/NETWORK/ENTERPRISE LICENSES.
    If this is a multi-computer, network, or enterprise license, you may make, install, and use additional copies of the software up to the number of copies authorized in your registration documentation.
    If this is an enterprise license, you are granted a nonexclusive, royalty-free license to integrate the OpenX Software into Applications and to distribute such Software in connection with such Applications, provided that such Applications do not in any way compete with the Software or expose the functionality of the Software through a programmable interface. Each such Application must include the following comment in its source code within close proximity to each instantiation of an OpenX software:
    This application utilizes a licensed copy of OpenX software. OpenX software is Copyright (c)2001 OpenX Technologies Inc. All rights reserved. Use of any objects outside of the context of this application is a violation of Canadian and international copyright laws and other applicable laws.
  • INTELLECTUAL PROPERTY RIGHTS.
    You acknowledge that the Software contains copyrighted material, trade secrets, trademarks and other proprietary material of OpenX Technologies Inc. ("Confidential Information"), and is protected under Canadian and international copyright law and other applicable laws. You may not engage in any unauthorized use or disclosure of any Confidential Information. You agree that the source code of the Software is confidential and proprietary to OpenX. You must reproduce and maintain all copyright notices that are contained in the Software on any copy thereof that you make or use.
  • LIMITED WARRANTY AND LIABILITY.
    THE SOFTWARE IS PROVIDED AS IS AND OPENX TECHNOLOGIES DISCLAIMS ALL WARRANTIES RELATING TO THIS SOFTWARE, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO ANY IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. NEITHER OPENX TECHNOLOGIES NOR ANYONE INVOLVED IN THE CREATION, PRODUCTION, OR DELIVERY OF THIS SOFTWARE SHALL BE LIABLE FOR ANY INDIRECT, CONSEQUENTIAL, OR INCIDENTAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE SUCH SOFTWARE EVEN IF OPENX TECHNOLOGIES HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES OR CLAIMS. IN NO EVENT SHALL OPENX TECHNOLOGIES' LIABILITY FOR ANY DAMAGES EXCEED THE PRICE PAID FOR THE LICENSE TO USE THE SOFTWARE, REGARDLESS OF THE FORM OF CLAIM. THE PERSON USING THE SOFTWARE BEARS ALL RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE.
  • CANADIAN GOVERNMENT RESTRICTED RIGHTS.
    The Programs are Commercial Computer Software. Use, duplication and disclosure of the Programs and Documentation by the Canadian Government is subject to restrictions set forth in this license. OpenX reserves all unpublished rights under Canada copyright laws.
  • TERMINATION.
    This agreement terminates, you lose all rights licensed to you, and you must stop use of the software if you a) violate the terms of this agreement or b) you do not pay the license fee before the end of the evaluation period.
  • EXPORT.
    You agree to fully comply with all laws and regulations of the Canada and other countries ("Export Laws") to assure that neither the Programs or any direct products thereof are (1) exported, directly or indirectly, in violation of Export Laws, or (2) are used for any purpose prohibited by Export Laws, including, without limitation, nuclear, chemical or biological weapons production.
  • GOVERNING LAW.
    The agreement shall be governed by the laws of the Canada. Any action or proceeding brought by either party against the other arising out of or related to this agreement shall be brought only in a state court of competent jurisdiction located in Toronto, Ontario. The parties hereby consent to the personal jurisdiction of such courts.
I A G R E E


OpenX Syntax conventions

Syntax conventions
The following conventions are used for syntax diagrams in this manual:
Keywords | Symbol | Syntax Convention
Keywords | SELECT | SQL keywords are shown in uppercase.
Placeholders | name | Items that the user must replace with appropriate identifiers or expressions are shown in italics.
Options | | | Options are separated by vertical bars. Any one of the items is allowed.
Continuation | ... | Lines beginning with ... are a continuation of the statements from the previous line.
Lists | ",..." | Lists are shown with a list element followed by ",...". This means that one or more list elements are allowed and if more than one is specified, they must be separated by commas.
Optional portions | [ ] | Optional portions of a statement are enclosed by square brackets. For example, [ ASC | DESC ] indicates that ASC or DESC are optional. The square bracket should not be typed.
Alternatives | { } | When one of the options must be chosen, the alternatives are enclosed in curly braces. For example [ QUOTES { ON | OFF } ] indicates that if the QUOTES option is chosen, one of ON or OFF must be provided. The braces should not be typed.


OpenX Architecture.
  • The Connection Object

    • The Connection object represents a connection to the SQL Server.
  • The Command Object

    • The Command object represents a command (also known as a query or statement) that can be processed by the SQL Server. Commands can return rows or not, and can also handle parameters.
    Commands can be simple SQL statements, TSQL statements or calls to stored procedures in the database.
    Command object supports all SQL Server datatypes including text and image.

    Compatibility
    Native API Support
  • Oracle OCI7, OCI8
  • Sybase Open Client v10.0 or higher
  • Microsoft DB-Lib v6.0 or higher
  • Microsoft OLEDB
  • IBM DB2 CLI v6.x or higher
  • Informix CLI v2.x or higher
  • InterBase Client API v5.x or higher
  • SQLBase CAPI v6.x or higher
  • MySQL C API version 3.23.x or higher
  • ODBC v3.0 ot higher
            •
    Operating System
  • Windows NT 4.0
  • Windows 2000
  • Windows XP
    Component Type
  • COM+ Object/ActiveX DLL/In-Process Server
  • Supports Supports Free Threading
    and Apartment Model Multi-Threading
  • Supports JIT, Events and Statistics
    Year 2000 Compliance
  • Yes
            •


OpenX Features
  • Cursor emulation and memory conservation
    • While emulating cursors, OpenX doesn't saves result sets in it's memory buffer. It fetches row by row from SQL server's memory buffers instead. This technique is highly optimized, and aimed to scroll read-only records in a forward-only manner. Such method saves memory on machine where OpenX's instance was created.

  • Connection behavior
    • One of important OpenX features is protected connection. OpenX won't crash or raise ASP error if network or SQL server problem occurs. Also, it may happen if somebody kills OpenX process on SQL server. ErrorCode will return "-9999999" in that case. Executing Disconnect() and after that Connect(), will return OpenX to normal state.

  • isEmpty property behavior
    • Calling isEmpty after Execute() has the side effect of moving the cursor to the first row of the result set (if any).
    We recommend using "do ... while ( MoveNext() )" in that case.

  • Processing HTML Forms and Uploading Files (OpenX ASP Edition only)
    • This feature of OpenX greatly simplifies HTML Form processing and File uploading procedures. Only few strings of code needed to validate posted HTML Form and save its data into the database. If this form includes one or more <INPUT TYPE=FILE> tags, the files will be received by OpenX and stored onto server's hard drive or saved right into the database. Only one submit button is required for a form containing any combination of HTML form elements plus the upload file elements.
    OpenX provides all the functionalty of standard File uploading components: it will show the type of file, its extention and size, aggregate amount of bytes transferred during given upload session, and will allow you controlling the 'overwrite' switch and the size of uploads.
    OpenX will validate posted HTML forms, ensure that all mandatory fields are filled properly, and even check the validity of email addresses and credit card numbers.


OpenX Installation

Software installation

Installation procedure for Windows 2000

To install a OpenX COM+ server application with the Component Services administrative tool using *.MSI file.
  1. In the console tree of the Component Services administrative tool, double-click the computer to which you want to install the application.
  2. Right-click the COM+ Applications folder for that computer, point to New, and then click Application.
  3. In the initial dialog box for the COM Application Install Wizard, click Next.
  4. In the Install or Create a New Application dialog box, click Install pre-built application(s).
  5. In the Install from application file dialog box, browse to select the openx.msi file, and then click Open.
  6. In the Set Application Identity dialog box, specify the application identity and then click Next.
    The default selection is Interactive User. The interactive user is the user who is logged on to the Windows 2000 account for the computer that runs an application. You can select a different user by clicking This user and entering details for a specific Windows 2000 user or group.
  7. In the Application Installation Options dialog box, specify the directory in which to install the COM+ application.
  8. The application's component files are copied from the .msi file to the installation directory. The default installation directory is:
    %Program Files%\COMPlus Applications\[appid]
  9. You can accept the default directory, or click Specific directory and Browse to search for another location.
  10. To perform this procedure, the application identity specified in step 9 must have at least read access to the installation directory; and, the person running the Component Services administrative tool during this process must have write access to the installation directory.
  11. Click Finish.
The hierarchy in the Explorer console tree now shows the new application in the detail pane.

To install a OpenX COM+ server application with the Component Services administrative tool using *.DLL file.
  1. In the console tree of the Component Services administrative tool, double-click the computer to which you want to install the application.
  2. Right-click the COM+ Applications folder for that computer, point to New, and then click Application.
  3. In the initial dialog box for the COM Application Install Wizard, click Next.
  4. In the Install or Create a New Application dialog box, click Create an empty application.
  5. Name your Application, choose Activation Type, and then click Next.
  6. In the Account dialog box, specify the application identity and then click Next.
    The default selection is Interactive User. The interactive user is the user who is logged on to the Windows 2000 account for the computer that runs an application. You can select a different user by clicking This user and entering details for a specific Windows 2000 user or group. (Recommended to ensure proper access to needed resources)
  7. Click Finish. The hierarchy in the Explorer console tree now shows the new application in the detail pane.
  8. Expand the tree under your newly created application and select Components folder.
  9. Choose the following from MMC menu: Action -> New -> Component
  10. Click Next on COM Component Install Wizard
  11. In the Import or Install a Component dialog box, click Install New Component(s).
  12. In the Select Files to Install dialog box, browse to select the OpenX(ASP).dll file, and then click Open. Click Next. Click Finish.

To install a OpenX COM+ server application from the Windows Explorer
  1. On the computer to which you want to install the OpenX COM+ application, locate the server application file (openx.msi) using the Windows Explorer. The file can reside locally or on a network share.
  2. Double-click the server application file. You can also right-click the file name and then click Install. The Microsoft installer automatically installs the OpenX server application on the target computer.

Installation procedure for Windows NT 4.0

To install a pre-built OpenX package using MTS Explorer:
  1. Start MTS Explorer
  2. In the left pane of the Explorer, select the computer on which you want to create a package.
  3. Open the Packages Installed folder for that computer.
  4. On the Action menu, click New. You can also select the Create new object button on the MTS toolbar, or right-click on the Packages Installed folder and select New and then Package.
  5. Click the Install pre-built packages button.
  6. In the Select Package Files dialog box, click Add to browse the hard drive for package files. Select a package file (openx.pak), click Open, and then click Next. The component files that are included in the package must be located in the same directory as the package file.
  7. Specify the package identity in the Set Package Identity dialog box, and then click Next. The default selection is Interactive User. The interactive user is the user that logged on to the Windows NT account for the computer that runs a package. You can select a different user by selecting the This user option and entering details for a specific Windows NT user.
  8. In the Installation Options dialog box, specify the installation directory. The component files are copied from the package file directory to the installation directory. You can accept the default directory, or click Browse to search for another location.
  9. Click Finish. The Explorer hierarchy now shows the new package in the right pane.

To install a OpenX DLL using MTS Explorer:
  1. Start MTS Explorer
  2. In the left pane of MTS Explorer, select the computer for which you want to create a package.
  3. Open the Packages Installed folder for that computer.
  4. On the Action menu, click New. You can also select the Package Installed folder and either right-click and select New and then Package from the right-click menu, or select the Create a new object button on the MTS toolbar.
  5. Use the Package wizard to create an empty OpenX package.
  6. Click the Create an empty package button.
  7. Type a name for the new package (OpenX), and click Next.
  8. Specify the package identity in the Set Package Identity dialog box, and then click the Finish button.

    9. The default selection for package identity is Interactive User. The interactive user is the user that logged on to the server computer on which the package is running. You can select a different user by selecting the This user option and entering a specific Windows NT user.

  9. In the left pane of MTS Explorer, select the computer on which you want to install the component.
  10. Open the Packages Installed folder, and select the package in which you want to install the component.
  11. Open the Components folder.
  12. On the Action menu, click New. You can also select the Components folder, right-click, and select New and then Component from the right-click menu, or select the Create a new object button on the MTS toolbar.
  13. Click the Install new component(s) button.
  14. In the dialog box that appears, click Add Files to select the files you want to install. Select OX2.dll. Make sure that in your Windows NT Explorer, the Hidden files option is set to Show all files. If this option is set to hide files with the .dll file name extension, you will not see the OX2.dll in the Select files to install dialog box. You will have to restart the MTS Explorer if you change this setting.
  15. Click the Finish button to install the component.

To install OpenX COM+ using regsvr32.exe utility

  1. Unzip the file into a directory of your choice.
  2. Make sure anonymous login account (IUSR_<machine>) has appropriate permissions on this directory.
  3. Register the component by executing the command "Regsvr32.exe %Your_Directory%\OX2.dll" from the Command or Start/Run prompt.
  4. For more information on installing components, visit Microsoft Support Site

Native Database Client Software

Please, note that Native Database Client Software must be installed and properly configured prior to using OpenX.
Connection to RDBMS should be verified.
For example, Sybase dsedit utility has "Ping Server" function, Oracle Net8 Assistant has "Test Net Service" command, etc.
Note: Native Database Client Software must be installed on the same machine as OpenX.
Please, make sure you have latest version of your RDBMS client software.
Download and apply Service Packs, patches or EBFs if necessary.

OpenX ASP Edition

Please, make sure the current user (such as IUSR_xxx) whose credentials in use by OpenX, have Read, Write and Modify permissions over the upload directory to be able to create files in it. Read permissions over directory where Form Definition files resided, must be granted to the same user as well. You can instantly fix this problem by giving Everyone Full Control over the upload directory, then tighten security as appropriate.
To prevent timeouts when uploading large files set the Server.ScriptTimeout property to some large number such as 200 (in minutes) before running your upload script.

System Requirements

  • Pentium Processor based system (Pentium III 500MHz or higher recommended)
  • 128Mb RAM (256 or more recommended) [dllhost.exe running OpenX2 component consumes about 2-7Mb of RAM while idle. One thousand concurrent idle OpenX instances will consume about 14Mb of RAM. These values may vary when OpenX is not idle.]
  • Windows NT 4.0 SP6 or Windows 2000 SP1/SP2.
  • Microsoft IIS 4 or 5 for OpenX ASP Edition


OpenX Software configuration

Software configuration

OpenX v2.0 use Windows Registry to store its configuration data.
OpenX Configuration Utility enables you to create and maintain database connection profiles.

Before you start this utility, you should make sure you have administrative privileges on this machine.
Registry Path to configuration data: HKLM\SOFTWARE\Koldoon\OpenX2

Choose database vendor, then fill all needed parameters.
In some cases you might want to check vendor's documentation to find out how to fill "DBString" parameter.
Name your profile, and press "Apply" button.
Note, that you can create multiple profiles per vendor.
Please, keep in mind, that profile names must be unique, and parameters might be case sensitive.


OpenX Connection Object

The Connection object

Properties
autoCommit | isConnected
isolationLevel | VendorInfo
ErrorCode | ErrorCodeEx
ErrorInfo | Option

autoCommit property
Syntax | connection.autoCommit
Description | If true, each SQL statement executed using the connection is automatically committed immediately after it is executed
Type | Boolean
Access | Read / Write
isConnected property
Syntax | connection.isConnected
Description | Indicates whether a connection has been made to the database. (read-only)
Type | Boolean
Access | Read Only
isolationLevel property
Syntax | connection.isolationLevel
Description The isolation level for the database that you are connected to. You must be connected in order to set the isolation level. This value may be set with an integer or a string. It is always retrieved as a string.
The possible values are:
  • tilUnknown [-1] - "The isolation level is undetermined or cannot be determined."
  • tilReadUncommitted [0] - "ReadUncommitted"
  • tilReadCommitted [1] - "ReadCommitted"
  • tilRepeatableRead [2] - "RepeatableRead"
  • tilSerializable [3] - "Serializable"
If the isolation level is set to a value that the database does not recognize, the old isolation value will remain.
Type | Number (short)
Access | Read / Write
VendorInfo property
Syntax | connection.VendorInfo
Description | Shows database vendor information (read-only)
Type | String
Access | Read Only
ErrorCode property
Syntax | connection.ErrorCode
Description | Returns the last error code of the command object.
Notes OpenX error handler has two main error categories. System error (component level) and SQL Server error (SQL Server level).
System Error values could be found in OpenX Appendixes
In case of SQL Server error, ErrorCode returns native error code from SQL Server. Please check SQL Server documentation for error codes.
Type | Number [Integer]
Access | Read Only
ErrorCodeEx property
Syntax | connection.ErrorCodeEx
Description | Returns the row number pointing to error in the command text.
Notes | Covers SQL Server errors only. Its value is 0 in case of System Error.
Type | Number [Integer]
Access | Read Only
ErrorInfo property
Syntax | connection.ErrorInfo
Description | Retrieves the last error text of the command object.
Notes System Error values could be found in OpenX Appendixes
In case of SQL Server error, ErrorInfo returns native error text from SQL Server.
Type | String
Access | Read Only
Option property
Syntax | connection.Option
Description | Set/Retrieve server specific connection options.
Notes Sybase:
  • CS_PACKETSIZE
  • CS_APPNAME
  • CS_HOSTNAME

    • Property available in version 2.0.2 or higher
    Type | String
    Access | Read/Write

    Methods
CreateCommand | Connect | ConnectEx
Disconnect | Commit | Rollback

    CreateCommand method
    Syntax | connection.CreateCommand(String SQLText, [[Boolean bExecute = False], Long lCommandType = cmdTypeUnknown])
    Description Creates a new Command object and executes an SQL statement if bExecute is True. The CreateCommand() method can be used in the following ways:
    • connection.CreateCommand("select * from product", true) creates and executes a Command.
    • connection.CreateCommand("select * from product") creates a Command object, sets the SQL statement, but does not execute it. The Execute method could then be called at a later time.
    Notes | "text" and "image" fields must be at the end of SELECT statement. Command.FieldValue() will return longchar or binary data for these columns.
    Return | Command Object
    Connect method
    Syntax | connection.Connect(string ProfileName)
    Description | Connects to a database using the profile specified as parameter. Returns boolean indicating connection success or failure.
    Return | Boolean
    ConnectEx method
    Syntax | connection.ConnectEx(Long DBMS, string UserName, string Password, string ConnectionString_or_ServerName, [string DBname], [Long Protocol])
    Description | Connects to a database using the connection string specified as parameter. Returns boolean indicating connection success or failure.
    Notes DBMS values:
  • 1 - ODBC
  • 2 - Oracle
  • 3 - MS SQL
  • 4 - Interbase
  • 5 - SQLBase
  • 6 - IBM DB2
  • 7 - Informix
  • 8 - Sybase
  • 9 - MySQL

    • Protocol values (InterBase only)
  • 0-TCPIP
  • 1-NetBEUI
  • 2-IPX/SPX

    • Method available in version 2.0.2 or higher
    Return | Boolean
    Disconnect method
    Syntax | connection.Disconnect()
    Description | Disconnects a connection object from a database.
    Commit method
    Syntax | connection.Commit()
    Description | Commits the transaction.
    Return | None
    Rollback method
    Syntax | connection.Rollback()
    Description | Performs a rollback on the transaction.
    Return | None


OpenX Command Object

The Command object

Properties
CommandText | CommandType | Connection
ErrorCode | ErrorCodeEx | ErrorInfo
FieldCount | FieldName | FieldPos
FieldSize | FieldType | FieldValue
FieldValueAsLong | FieldValueAsShort | FieldValueAsDouble
FieldValueAsString | FieldValueAsDate | FieldValueAsBinary
isEmpty | isFieldNull | isFieldRequired
isOpened | ParamDirection | ParamValue
ParamValueAsLong | ParamValueAsShort | ParamValueAsDouble
ParamValueAsString | ParamValueAsDate | ParamValueAsBinary
ParamCount | RowsCount | XMLDocument
XMLString |  |

CommandText property
Syntax | command.CommandText
Description | Retrieves the SQL statement from the current command object.
Type | String
Access | Read / Write
CommandType property
Syntax | command.CommandType
Description Retrieves the type of command text.
  1. cmdTypeUnknown [0] - Undefined
  2. cmdTypeSQLStmt [1] - SQL Statement
  3. cmdTypeStoredProc[2] - Stored Procedure
Type | Number [Short]
Access | Read Only
Connection property
Syntax | command.Connection
Description | Retrieves connection object of the current Command object
Access | Read / Write
ErrorCode property
Syntax | command.ErrorCode
Description | Retrieves the last error code of the command object.
Notes OpenX error handler has two main error categories. System error (component level) and SQL Server error (SQL Server level).
System Error values could be found in OpenX Appendixes
In case of SQL Server error, ErrorCode returns native error code from SQL Server. Please check SQL Server documentation for error codes.
Type | Long
Access | Read Only
ErrorCodeEx property
Syntax | command.ErrorCodeEx
Description | Retrieves the row number pointing to error in the command text. (read-only)
Notes | Covers SQL Server errors only. Its value is 0 in case of System Error.
Type | Long
Access | Read Only
ErrorInfo property
Syntax | command.ErrorInfo
Description | Returns the last error text of the command object. (read-only)
Notes System Error values could be found in OpenX Appendixes
In case of SQL Server error, ErrorInfo has native error text from SQL Server.
Type | String
Access | Read Only
FieldCount property
Syntax | command.FieldCount
Description | Retrieves the number of columns in the result set. (read-only)
Type | Long
Access | Read Only
FieldName property
Syntax | command.FieldName(Long fieldNum)
Description | Returns the field name or field alias (as specified in the SQL statement) for the fieldNum column. Fields are numbered starting with 1. (read-only)
Notes | if fieldNum less then zero or greater then value returned by FieldCount, FieldName will raise an exception.
Type | String
Access | Read Only
FieldPos property
Syntax | command.FieldPos(String fieldName)
Description | Retrieves a position of the field in a result set. Fields are numbered starting with 1.
Type | Long
Access | Read Only
FieldSize property
Syntax | command.FieldSize(Variant fieldIdx)
Description | Retrieves declared size of the current data field.
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long)
Type | Long
Access | Read Only
FieldType property
Syntax | command.FieldType(Variant fieldIdx)
Description Retrieves field data type. Possible values are:
  1. dtUnknown [0]
  2. dtShort [1] - smallint
  3. dtLong [2] - integer
  4. dtDouble [3] - money, numeric, float
  5. dtDateTime [4] - datetime, date, time
  6. dtString [5] - char, varchar
  7. dtBytes [6]
  8. dtLongBinary [7] - image
  9. dtLongChar [8] - text
  10. dtBLob [9] - image
  11. dtCLob [10] - text
  12. dtCursor [11] (for Oracle only - it is not supported in this release)
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldType will raise an exception if fieldIdx points to unexisted field.
Type | String
Access | Read Only
FieldValue property
Syntax | command.FieldValue(Variant fieldIdx)
Description | Retrieves the value of the field of the current row of the result set.
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValue will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type | Variant
Access | Read Only
FieldValueAsLong property
Syntax | command.FieldValueAsLong(Variant fieldIdx)
Description | Retrieves the value of the field of the current row of the result set as Long Integer. FieldValueAsLong will raise an exception if the field type is not convertible to Long
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValue will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type | Long
Access | Read Only
FieldValueAsShort property
Syntax | command.FieldValueAsShort(Variant fieldIdx)
Description | Retrieves the value of the field of the current row of the result set as Short Integer. FieldValueAsShort will raise an exception if the field type is not convertible to Short
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsShort will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type | Short
Access | Read Only
FieldValueAsDouble property
Syntax | command.FieldValueAsDouble(Variant fieldIdx)
Description | Retrieves the value of the field of the current row of the result set as Double. FieldValueAsDouble will raise an exception if the field type is not convertible to Double
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsDouble will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type | Double
Access | Read Only
FieldValueAsString property
Syntax | command.FieldValueAsString(Variant fieldIdx)
Description | Retrieves the value of the field of the current row of the result set as String. FieldValueAsString will raise an exception if the field type is not convertible to String
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsString will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type | String
Access | Read Only
FieldValueAsDate property
Syntax | command.FieldValueAsDate(Variant fieldIdx)
Description | Retrieves the value of the field of the current row of the result set as Date. FieldValueAsDate will raise an exception if the field type is not convertible to Date
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsDate will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type | Date
Access | Read Only
FieldValueAsBinary property
Syntax | command.FieldValueAsBinary(Variant fieldIdx)
Description Retrieves the value of the field of the current row of the result set.
The result will be of type VT_ARRAY | VT_UI1, which is a variant array of unsigned one-byte characters.
Notes | fieldIdx must be VT_BSTR, VT_I2 or VT_I4 (String, Short or Long). FieldValueAsBinary will raise an exception if fieldIdx points to unexisted field. Type of the property depends on FieldType.
Type Variant
JScript - VBArray
VBScript - Array
C/C++ - Variant [VT_ARRAY | VT_UI1]
Access | Read Only
isEmpty property
Syntax | command.isEmpty
Description | Indicates whether the result set is empty or never returns result set.
Notes | if no command.MoveNext() method has yet been called on the command object, command.isEmpty has the side effect of moving the cursor to the first (next) row of the result set. In other words, isEmpty is equal to Not command.MoveNext().
Type | Boolean
Access | Read Only
isFieldNull property
Syntax | command.isFieldNull(Variant fieldIdx)
Description | Indicates whether the field's value is NULL.
Type | Boolean
Access | Read Only
isFieldRequired property
Syntax | command.isFieldRequired(Variant fieldIdx)
Description | Indicates whether the field value could be NULL.
Type | Boolean
Access | Read Only
isOpened property
Syntax | command.isOpened
Description | Returns a Boolean indicating whether a command object is open. The command object is opened either by calling the Execute() method on the command and some logical command are still not executed or using the connection.CreateCommand() method.
Type | Boolean
Access | Read Only
ParamDirection property
Syntax | command.ParamDirection
Description Sets or returns a Long indicating a parameter direction.
  1. ptParamInput = 0
  2. ptParamInputOutput = 1
  3. ptParamOutput = 2
  4. ptParamReturn = 3
Type | Long
Access | Read / Write
ParamValue property
Syntax | command.ParamValue(paramNum | paramName )
Description | Sets or returns the command parameter specified by its position or name.
Type | Variant
Access | Read / Write
ParamValueAsLong property
Syntax | command.ParamValueAsLong(paramNum | paramName )
Description | Sets or returns the command parameter specified by its position or name.
Type | Long
Access | Read / Write
ParamValueAsShort property
Syntax | command.ParamValueAsShort(paramNum | paramName )
Description | Sets or returns the command parameter specified by its position or name.
Type | Short
Access | Read / Write
ParamValueAsDouble property
Syntax | command.ParamValueAsDouble(paramNum | paramName )
Description | Sets or returns the command parameter specified by its position or name.
Type | Double
Access | Read / Write
ParamValueAsString property
Syntax | command.ParamValueAsString(paramNum | paramName )
Description | Sets or returns the command parameter specified by its position or name.
Type | String
Access | Read / Write
ParamValueAsDate property
Syntax | command.ParamValueAsDate(paramNum | paramName )
Description | Sets or returns the command parameter specified by its position or name.
Type | Date
Access | Read / Write
ParamValueAsBinary property
Syntax | command.ParamValueAsBinary(paramNum | paramName )
Description | Sets or returns the command parameter specified by its position or name.
Type | Variant [VT_ARRAY | VT_UI1]
Access | Read / Write
ParamCount property
Syntax | command.ParamCount
Description | Returns the number of parameters for SQL statement or stored procedure specified in the command object.
Type | Long
Access | Read Only
RowsCount property
Syntax | command.RowsCount
Description | The number of rows in the result set of the current command before fetch.
Notes | command.RowsCount must be called right after connection.CreateCommand(SQL, true) or after command.Execute(). To get a result set after calculating the number of rows, the Command must be executed again by calling command.Requery() method.
Type | Long
Access | Read Only
XMLDocument property
Syntax | command.XMLDocument
Description | Returns result set as a XML document.
Notes You should not call command.Execute method before using command.XMLDocument.
Data type codes used in XML tag "type":
  • 1 - boolean
  • 2 - int(Short)
  • 3 - int(Long)
  • 4 - double
  • 5 - double(Numeric)
  • 6 - datetime
  • 7 - string
  • 8 - binary
  • 9 - image(LongBinary)
  • 10 - text(LongChar)
  • 11 - image(BLob)
  • 12 - text(CLob)
            •
    Type | DOMDocument
    Access | Read Only
    XMLString property
    Syntax | command.XMLString
    Description | Returns result set as a XML string.
    Notes | You should not call command.Execute method before using command.XMLString .
    Type | String
    Access | Read Only

    Methods
Cancel | Close
Execute | FetchOutputParams
MoveNext | Prepare
Requery | SetCommandText

    Cancel method
    Syntax | command.Cancel()
    Description | Attempts to cancel the pending result set, or current statement execution. The exact definition is vendor-dependent.
    Notes | Sybase and Interbase servers doesn't support cancellation of a statement execution.
    Return | None
    Close method
    Syntax | command.Close()
    Description | Closes the active Command. The only way to re-establish the Command is to execute it again.
    Return | None
    Execute method
    Syntax | command.Execute()
    Description | Executes Command object's SQL statement.
    Return | None
    FetchOutputParams method
    Syntax | command.FetchOutputParams()
    Description Fetches the next row of output parameters (after execution of a stored procedure).
    Returns True if the next row was fetched, otherwise returns False
    Notes | Vendor dependent property.
    Return | Boolean
    MoveNext method
    Syntax | command.MoveNext()
    Description Changes the current row to the next row of a Command's result set.
    Returns True if the next row was fetched, otherwise returns False. (False also means that loop has reached the end of the result set)
    Return | Boolean
    Prepare method
    Syntax | command.Prepare()
    Description | Validates and prepares the current command for execution.
    Notes | Prepare() method compiles the command, but does not execute it. The method detects syntax errors in command text and verifies the existance of database objects.
    Return | None
    Requery method
    Syntax | command.Requery()
    Description | Re-executes the current Command and obtains a new result set. Identical to call a sequence of Close() and Execute() methods. The cursor is moved to the first row of the result set.
    Return | None
    SetCommandText method
    Syntax | command.SetCommandText(string sqlStatement, Long SQLType = cmdTypeUnknown)
    Description Sets the SQL statement in the current Command object to sqlStatement [string]. If a Command object was opened SetCommandText() method closes the command and removes all result sets.
    Possible SQLType values:
    1. cmdTypeUnknown [0] - Undefined
    2. cmdTypeSQLStmt [1] - SQL Statement
    3. cmdTypeStoredProc[2] - Stored Procedure
    Notes | "text" and "image" fields must be at the end of SELECT statement. command.FieldValue will retrieve longchar or binary data for these columns.
    Return | None


OpenX Language Support

Language Support

In many languages, such as English or French, there are few enough characters that make up the language that each letter (A, B, C) and number (1, 2, 3) can be represented by a single byte character set. In such a character set, each character is represented by an 8 bit byte. At most, 256 characters can be represented in a single-byte set.

The limit of 256 characters is not sufficient for many languages such as Japanese or Taiwanese. In cases such as these, a double byte character set is necessary to represent the large number of characters that make up the language. In a few languages even double-byte character sets are not sufficient in which case a multi-byte character set is used.

OpenX supports multi-byte character sets in SQL queries and comparisons and SQL variables.


OpenX Appendixes

Appendixes

Error Codes
Error Number | Error Location | Error Number | Error Location
1 | Connect() | 1A | FieldValueAsShort
2 | Disconnect() | 1B | FieldValueAsDate
3 | Commit() | 1C | FieldValueAsString
4 | Disconnect() | 1D | FieldValueAsDouble
5 | autoCommit | 1E | FieldValueAsBinary
6 | isolationLevel | 1F | IsFieldRequired
7 | SQLText | 20 | FieldPos
8 | Execute() | 21 | ParamValueAsLong (r)
9 | FieldCount | 22 | ParamValueAsLong (s)
A | Fetch | 23 | ParamValueAsShort (r)
B | FetchParamsNext | 24 | ParamValueAsShort (s)
C | Field | 25 | ParamValueAsDate (r)
D | FieldType | 26 | ParamValueAsDate (s)
E | FieldName | 27 | ParamValueAsString (r)
F | ParamCount | 28 | ParamValueAsString (s)
10 | ParamValue (r) | 29 | ParamValueAsDouble (r)
11 | ParamValue (s) | 2A | ParamValueAsDouble (s)
12 | Requery | 2B | ParamValueAsBinary (r)
13 | IsEmpty | 2C | ParamValueAsBinary (s)
14 | Close | 2D | ParamType (r)
15 | FetchedCount | 2E | ParamType (s)
16 | RowsCount | 2F | ParamDirection (r)
17 | Prepare | 30 | ParamDirection (s)
18 | Cancel | 31 | isFieldNull
19 | FieldValueAsLong |  |
r -- on retrieving. s -- on setting

Profile Errors
Code | Subcode | ErrMsg | Description
1 | 1 | Couldn't open the profile record | Windows Registry error or Profile doesn't exist
1 | 2 | Couldn't open the profile record | Client API info is not set in profile
1 | 3 | Couldn't open the profile record | DB Connection string is not set in profile
1 | 4 | Couldn't open the profile record | Login is not set in profile
1 | 5 | Couldn't open the profile record | Password is not set in profile


Samples - ASP JScript

Code Samples -- JScript

Example #1 - Retrieve Data

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = 0;
var sResult = "";
var bError = false;
var sSQL = "SELECT * FROM authors";

try {
  var i;
  oConn.Connect("ms_ox1");
  oCommand = oConn.CreateCommand( sSQL, true );
  sResult += "<table border=1><tr>";
  for(i = 1; i <= oCommand.FieldCount; i++)  {
    sResult += "<td><b>" + oCommand.FieldName(i) + "</b></td>";
  }
  sResult += "</tr>";
  while(oCommand.MoveNext())  {
    sResult += "<tr>";
    for(i = 1; i <= oCommand.FieldCount; i++)  {
      sResult += "<td>" + oCommand.FieldValueAsString(i) + "</td>";
    }
    sResult += "</tr>";
  }
  sResult += "</table>";
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}
%>
<html>
  <head><title>OpenX2 Test #1 - 'Select * from table'</title></head>
  <body>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP JScript

Code Samples -- JScript

Example #2 - Sorting

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var sOrderBy = "au_lname";
if(Request.Form("newOrder").Count > 0)  {
  sOrderBy = Request.Form("newOrder");
}
var sSQL = "SELECT au_fname + ' ' + au_lname, phone, address, city, state, zip FROM authors order by " + sOrderBy;

try {
  var i;
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oCommand.CommandText = sSQL;
  oCommand.Execute();
  while(oCommand.MoveNext())  {
    sResult += "<tr>";
    for(i = 1; i <= oCommand.FieldCount; i++)  {
      sResult += "<td>" + oCommand.FieldValueAsString(i) + "</td>";
    }
    sResult += "</tr>";
  }
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}

%>

<html>
  <head><title>OpenX2 Test #2 - ORDER BY</title>
<script type="text/javascript" language="JavaScript">
<!--
var sCurOrder = "<%= sOrderBy %>";
function ChangeOrder(sNewOrder)
{
  if(sCurOrder != sNewOrder)  {
    reorder.newOrder.value = sNewOrder;
    reorder.submit();
  }
}

-->
</script>

</head>
  <body>
<%
if(bError)  {
%>
    <div><%=sResult%></div>
<%
}
else  {
%>
    Click on column caption to sort by it
    <form action="OX2test2.asp" method="post" name="reorder" id="reorder">
    <input type="hidden" name="newOrder" id="newOrder" value="au_lname">
    <table border="1">
      <tr>
          <td style="cursor: hand;" onClick="ChangeOrder('au_lname')"><b>Name</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('phone')"><b>Phone</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('address')"><b>Address</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('city')"><b>City</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('state')"><b>State</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('zip')"><b>Zip</b></td>
      </tr>
      <%=sResult%>
    </table>
    <form>
<%
}
%>
  </body>
</html>


Samples - ASP JScript

Code Samples -- JScript

Example #3 - Simple Query

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var sParam = "";
var sSQL = "SELECT * FROM authors";

if(Request.Form("param").Count > 0)  {
  if(Request.Form("param") != "")  {
    sParam = Request.Form("param");
    sSQL = "SELECT authors.* FROM authors WHERE au_lname LIKE :1";
  }
}

try {
  var i;
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oCommand.CommandText = sSQL;
  if(sParam != "")
    oCommand.ParamValueAsString(1) = sParam;
  oCommand.Execute();
  sResult += "<table border=1><tr>";
  for(i = 1; i <= oCommand.FieldCount; i++)  {
    sResult += "<td><b>" + oCommand.FieldName(i) + "</b></td>";
  }
  sResult += "</tr>";
  while(oCommand.MoveNext())  {
    sResult += "<tr>";
    for(i = 1; i <= oCommand.FieldCount; i++)  {
      sResult += "<td>" + oCommand.FieldValueAsString(i) + "</td>";
    }
    sResult += "</tr>";
  }
  sResult += "</table>";
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}

%>
<html>
  <head><title>OpenX2 Test #4 - Parameter in SELECT</title></head>
  <body>
    <%= sSQL %>
    <form action="OX2test4.asp" method="post">
    <input type="text" name="param"><input type="submit" value="Requery">
    <div><%=sResult%></div>
    </form>
  </body>
</html>


Samples - ASP JScript

Code Samples -- JScript

Example #4 - Update

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var sResult1 = "";
var bError = false;
var bMakeUpdate = Request.Form("id").Count > 0;
var sRetrieveSQL = "SELECT * FROM authors";
var sUpdateSQL = "UPDATE authors SET au_lname = :1, au_fname = :2, phone = :3, address = :4, city = :5, state = :6, zip = :7, contract = :8 WHERE authors.au_id = :id";

try {
  var i;
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oConn.autoCommit = true;
  if(bMakeUpdate)  {
    oCommand.SetCommandText(sUpdateSQL, 1);
    for(i = 1; i <= 8; i++)
      oCommand.ParamValueAsString(i) = new String(Request.Form("fld" + i));
    oCommand.ParamValueAsString("id") = new String(Request.Form("id"));
    oCommand.Execute();
  }
  oCommand.CommandText = sRetrieveSQL;
  oCommand.Execute();
  sResult += "<table border=1><tr>";
  for(i = 1; i <= oCommand.FieldCount; i++)  {
    sResult += "<td><b>" + oCommand.FieldName(i) + "</b></td>";
  }
  sResult += "</tr>";

  while(oCommand.MoveNext())  {
    sResult += "<form action=\"OX2test5.asp\" method=\"post\">";
    sResult += "<tr>";
    sResult += "<td><input type=\"submit\" value=\"Update Row\"><input type=\"hidden\" name=\"id\" value=\"" + oCommand.FieldValueAsString(1) + "\"></td>";
    for(i = 2; i <= oCommand.FieldCount; i++)  {
      sResult += "<td><input type=\"text\" name=\"fld" + (i - 1) + "\" value=\"" + oCommand.FieldValueAsString(i) + "\" size=\"" + (oCommand.FieldSize(i) > 20 ? 20 : oCommand.FieldSize(i)) + "\"></td>";
    }
    sResult += "</tr></form>";
  }
  sResult += "</table>";
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}

%>
<html>
  <head><title>OpenX2 Test #5 - UPDATE Sample</title></head>
  <body>
    <%= sResult1 %>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP JScript

Code Samples -- JScript

Example #5 - Delete

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var sResult1 = "";
var bError = false;
var bDelete = Request.Form("id").Count > 0;
var sRetrieveSQL = "SELECT * FROM authors";
var sUpdateSQL = "UPDATE authors SET au_lname = :1, au_fname = :2, phone = :3, address = :4, city = :5, state = :6, zip = :7, contract = :8 WHERE authors.au_id = :id";

try {
  var i;
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oConn.autoCommit = true;
  if(bDelete)  {
    oCommand.SetCommandText(sUpdateSQL, 1);
    for(i = 1; i <= 8; i++)
      oCommand.ParamValueAsString(i) = new String(Request.Form("fld" + i));
    oCommand.ParamValueAsString("id") = new String(Request.Form("id"));
    oCommand.Execute();
  }
  oCommand.CommandText = sRetrieveSQL;
  oCommand.Execute();
  sResult += "<table border=1><tr>";
  for(i = 1; i <= oCommand.FieldCount; i++)  {
    sResult += "<td><b>" + oCommand.FieldName(i) + "</b></td>";
  }
  sResult += "</tr>";

  while(oCommand.MoveNext())  {
    sResult += "<form action=\"OX2test5.asp\" method=\"post\">";
    sResult += "<tr>";
    sResult += "<td><input type=\"submit\" value=\"Update Row\"><input type=\"hidden\" name=\"id\" value=\"" + oCommand.FieldValueAsString(1) + "\"></td>";
    for(i = 2; i <= oCommand.FieldCount; i++)  {
      sResult += "<td><input type=\"text\" name=\"fld" + (i - 1) + "\" value=\"" + oCommand.FieldValueAsString(i) + "\" size=\"" + (oCommand.FieldSize(i) > 20 ? 20 : oCommand.FieldSize(i)) + "\"></td>";
    }
    sResult += "</tr></form>";
  }
  sResult += "</table>";
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}

%>
<html>
  <head><title>OpenX2 Test #5 - UPDATE Sample</title></head>
  <body>
    <%= sResult1 %>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP JScript

Code Samples -- JScript

Example #6 - Insert

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var bInsert = (Request.Form("id").Count > 0) && (Request.Form("id") == "Add New");
var sRetrieveSQL = "SELECT * FROM authors";
var sInsertSQL = "INSERT INTO authors ( au_id, au_lname, au_fname, phone, address, city, state, zip, contract ) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9 )";

try {
  var i;
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oConn.autoCommit = true;
  if(bInsert)  {
    var rnd = new String(Math.round(Math.random() * 1000000000));
    oCommand.CommandText = sInsertSQL;
    oCommand.ParamValueAsString(1) = rnd.substr(0,3) + "-" + rnd.substr(3,2) + "-" + rnd.substr(5,4);
    for(i = 2; i < 10; i++)
      oCommand.ParamValueAsString(i) = new String(Request.Form("fld" + i));
    oCommand.Execute();
  }
  oCommand.CommandText = sRetrieveSQL;
  oCommand.Execute();
  sResult += "<form action=\"OX2test7.asp\" method=\"post\">";
  sResult += "<table border=1><tr>";
  for(i = 1; i <= oCommand.FieldCount; i++)  {
    sResult += "<td><b>" + oCommand.FieldName(i) + "</b></td>";
  }
  sResult += "</tr><tr><td><input type=\"submit\" name=\"id\" value=\"Add New\"></td>";
  for(i = 2; i <= oCommand.FieldCount; i++)  {
    sResult += "<td><input type=\"text\" name=\"fld" + i + "\" value=\"\" size=\"" + (oCommand.FieldSize(i) > 20 ? 20 : oCommand.FieldSize(i)) + "\"></td>";
  }
  sResult += "</tr>";

  while(oCommand.MoveNext())  {
    sResult += "<tr>";
    for(i = 1; i <= oCommand.FieldCount; i++)  {
      sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(i) + " </td>";
    }
    sResult += "</tr>";
  }
  sResult += "</table></form>";
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}
%>

<html>
  <head><title>OpenX2 Test #7 - INSERT Sample</title></head>
  <body>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP JScript

Code Samples -- JScript

Example #7 - Stored Procedure

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var sParam = "";
var sSQL = "SELECT * FROM authors";

if(Request.Form("param").Count > 0)  {
  if(Request.Form("param") != "")  {
    sParam = Request.Form("param");
    sSQL = "SELECT :1 as param, authors.* FROM authors WHERE au_lname LIKE :1";
  }
}

try {
  var i;
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oCommand.CommandText = sSQL;
  if(sParam != "")
    oCommand.ParamValueAsString(1) = sParam;
  oCommand.Execute();
  sResult += "<table border=1><tr>";
  for(i = 1; i <= oCommand.FieldCount; i++)  {
    sResult += "<td><b>" + oCommand.FieldName(i) + "</b></td>";
  }
  sResult += "</tr>";
  while(oCommand.MoveNext())  {
    sResult += "<tr>";
    for(i = 1; i <= oCommand.FieldCount; i++)  {
      sResult += "<td>" + oCommand.FieldValueAsString(i) + "</td>";
    }
    sResult += "</tr>";
  }
  sResult += "</table>";
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}

%>
<html>
  <head><title>OpenX2 Test #4 - Parameter in SELECT</title></head>
  <body>
    <%= sSQL %>
    <form action="OX2test4.asp" method="post">
    <input type="text" name="param"><input type="submit" value="Requery">
    <div><%=sResult%></div>
    </form>
  </body>
</html>


Samples - ASP JScript - XML Processing

Code Samples -- JScript

Example #8 - XML Processing

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection.1")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var sResultStr = "";
var sResultDoc = "";
var bError = false;
var sSQL = "SELECT * FROM titles";

try {
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oCommand.CommandText = sSQL;
  sResultStr = oCommand.XMLString;

  // Set stylesheet locations
  oCommand.Close();
  oCommand.CommandText = sSQL;

  var xmlProc;
  var xmlTmpl = Server.CreateObject("Msxml2.XSLTemplate");
  var xslStyle = Server.CreateObject("Msxml2.FreeThreadedDOMDocument");
  var styleFile = Server.MapPath("OpenX2.xsl");
  xslStyle.async = false;
  xslStyle.load(styleFile);
  xmlTmpl.stylesheet = xslStyle;
  xmlProc = xmlTmpl.createProcessor();
  // Get the XMLDocument from OpenX2
  xmlProc.input = oCommand.XMLDocument;
  if(xmlProc.transform())
      sResultDoc = xmlProc.output;

}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}

%>

<html>
  <head><title>OpenX2 Test #9 - XML Output</title></head>
<%
if(!bError) {
%>
  <XML id="source"><%=sResultStr%></XML>
  <XML id="style" src="OpenX2.xsl"></XML>
  <SCRIPT event="onload" for="window">
    showResult.innerHTML = source.transformNode(style.XMLDocument);
  </SCRIPT>
<%
}
%>
  <body>
<%
if(!bError) {
%>
    <h1>Server-Side XMLDocument Processing</h1>
    <div><%=sResultDoc%></div>
    <h1>Client-Side XML-Island (String) Processing</h1>
    <div id="showResult"></div>
<%
} else {
%>
  <div><%=sResultDoc%></div>
<%
}
%>
  </body>
</html>


Samples - ASP JScript - TEXT (CLOb/LongChar) Fields

Code Samples -- JScript

Example #9 - TEXT (CLOb/LongChar) Fields Reading

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection.1")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var sSetTextSize = "SET TEXTSIZE ";
var sRetrieveSQL = "SELECT publishers.pub_name, publishers.city, publishers.state, publishers.country, pub_info.pub_id, pub_info.pr_info FROM pub_info, publishers WHERE ( pub_info.pub_id = publishers.pub_id ) ORDER BY publishers.pub_name";

try {
  var i = 0;
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oConn.autoCommit = true;
  oCommand.CommandText = sSetTextSize + "12000";
  oCommand.Execute();
  oCommand.CommandText = sRetrieveSQL;
  oCommand.Execute();
  while(oCommand.MoveNext())  {
    sResult += "<tr>";
    sResult += "<td style=\"font-size: 10pt; cursor: hand;\" onClick=\"ShowDesc(row" + oCommand.FieldValueAsString(5) + ")\"><b>Show/Hide Info</b></td>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(1) + " </td>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(2) + " </td>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(3) + " </td>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(4) + " </td>";
    sResult += "<td style=\"font-size: 10pt;\"><img src=\"OX2ImageJS.asp?pub_id=" + oCommand.FieldValue(5) + "\" border=\"0\"></td>";
    sResult += "</tr>";
    sResult += "<tr id=\"row" + oCommand.FieldValueAsString(5) + "\" class=\"hiddentr\">";
    sResult += "<td colspan=\"6\">" + oCommand.FieldValueAsString(6) + " </td>";
    sResult += "</tr>";
    i++;
  }
  oCommand.CommandText = sSetTextSize + "0";
  oCommand.Execute();
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";
  throw e;
  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}
%>

<html>
  <head><title>OpenX2 Test #10 - TEXT (CLOb/LongChar) Fields Reading </title>
  <STYLE TYPE="text/css">
    tr.visibletr { font-size: 8pt; visibility: visible; position: static; }
    tr.hiddentr { font-size: 8pt; visibility: hidden; position: absolute; }
  </STYLE>
<script type="text/javascript" language="JavaScript">
<!--

function ShowDesc(theTR)
{
  if(theTR)  {
    if(theTR.className == "visibletr")
      theTR.className = "hiddentr";
    else
      theTR.className = "visibletr";
  }
}

-->
</script>

</head>
  <body>
    <table border="1">
      <tr>
          <td><b>Info</b></td>
          <td><b>Name</b></td>
          <td><b>City</b></td>
          <td><b>State</b></td>
          <td><b>Country</b></td>
          <td><b>Logo</b></td>
      </tr>
      <%=sResult%>
    </table>
  </body>
</html>
ASP file to retrieve image from the database (OX2ImageJS.asp)

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection.1")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var pub_id;
pub_id = Request("pub_id");
var sSetTextSize = "SET TEXTSIZE ";
var sRetrieveSQL = "SELECT pub_info.logo FROM pub_info WHERE pub_info.pub_id = '" + pub_id + "'";

try {
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oConn.autoCommit = true;
  oCommand.CommandText = sSetTextSize + "32768";
  oCommand.Execute();
  oCommand.CommandText = sRetrieveSQL;
  oCommand.Execute();
  if(!oCommand.isEmpty)  {
    Response.Clear();
    Response.ContentType = "image/gif";
    Response.BinaryWrite(oCommand.FieldValueAsBinary(1));
  }
  oCommand.CommandText = sSetTextSize + "0";
  oCommand.Execute();
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";
  throw e;
  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}
%>
Example #10 - TEXT (CLOb/LongChar) Fields Writing

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection.1")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var bMakeUpdate = ( Request.Form("id").Count > 0 ); // && ( Request.Form("textField").Count = 1);
var sRetrieveSQL = "SELECT publishers.pub_name, publishers.city, publishers.state, publishers.country, pub_info.pub_id, pub_info.pr_info FROM pub_info, publishers WHERE ( pub_info.pub_id = publishers.pub_id ) ORDER BY publishers.pub_name";
var sUpdateSQL = "UPDATE pub_info SET pr_info = :pr_info WHERE pub_id = :id";

try {
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oConn.autoCommit = true;
  if(bMakeUpdate)  {
    oCommand.CommandText = sUpdateSQL;
    oCommand.ParamValueAsString("pr_info") = new String(Request.Form("textField"));
    oCommand.ParamValueAsString("id") = new String(Request.Form("id"));
    oCommand.Execute();
  }
  oCommand.CommandText = sRetrieveSQL;
  oCommand.Execute();
  while(oCommand.MoveNext())  {
    sResult += "<form action=\"OX2test11.asp\" method=\"post\">";
    sResult += "<tr>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(1) + " </td>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(2) + " </td>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(3) + " </td>";
    sResult += "<td style=\"font-size: 10pt;\">" + oCommand.FieldValueAsString(4) + " </td>";
    sResult += "</tr>";
    sResult += "<tr>";
    sResult += "<td colspan=\"4\"><textarea cols=\"77\" rows=\"7\" name=\"textField\">" + oCommand.FieldValueAsString(6) + "</textarea></td>";
    sResult += "</tr>";
    sResult += "<tr>";
    sResult += "<td align=\"center\"colspan=\"4\"><input type=\"submit\" value=\"Update Info\"><input type=\"hidden\" name=\"id\" value=\"" + oCommand.FieldValueAsString(5) + "\"><hr></td>";
    sResult += "</tr>";
    sResult += "</form>";
  }
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";
  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}
%>

<html>
  <head><title>OpenX2 Test #11 - TEXT (CLOb/LongChar) Fields Writing </title></head>
  <body>
    <table border="1">
      <tr>
          <td><b>Name</b></td>
          <td><b>City</b></td>
          <td><b>State</b></td>
          <td><b>Country</b></td>
      </tr>
      <%=sResult%>
    </table>

  </body>
</html>
© 2001 www.openx.ca


Samples - ASP VBScript - Retrieve Data

Code Samples -- VBScript

Example #1 - Retrieve Data

<%@ Language=VBScript %>
<%
Dim oConn, oCommand
Dim sResult, sSQL
Dim bError
Dim i

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery

  Set oConn = Server.CreateObject("OpenX2.Connection")
  Set oCommand = Nothing

  sResult = ""
  bError = false
  sSQL = "SELECT * FROM authors"
  oConn.Connect("ms_ox1")
  Set oCommand = oConn.CreateCommand( sSQL, true )
  sResult = sResult & "<table border=1><tr>"
  For i = 1 To oCommand.FieldCount
    sResult = sResult & "<td><b>" & oCommand.FieldName(i) & "</b></td>"
  Next
  sResult = sResult & "</tr>"

  While oCommand.MoveNext
    sResult = sResult & "<tr>"
    For i = 1 To oCommand.FieldCount
      sResult = sResult & "<td>" & oCommand.FieldValueAsString(i) & "</td>"
    Next
    sResult = sResult & "</tr>"
  Wend
  sResult = sResult & "</table>"

End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #1 - 'Select * from table'</title></head>
  <body>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP VBScript - Sorting

Code Samples -- VBScript

Example #2 - Sorting

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sSQL, sOrderBy
Dim bError
sOrderBy = "au_lname"
if Request.Form("newOrder").Count > 0 Then
  sOrderBy = Request.Form("newOrder")
End If
sSQL = "SELECT au_fname + ' ' + au_lname, phone, address, city, state, zip FROM authors ORDER BY " & sOrderBy

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  Dim i
  sResult = ""
  bError = false
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oCommand.CommandText = sSQL
  oCommand.Execute
  While oCommand.MoveNext()
    sResult = sResult & "<tr>"
    For i = 1 To oCommand.FieldCount
      sResult = sResult & "<td>" & oCommand.FieldValueAsString(i) & "</td>"
    Next
    sResult = sResult  & "</tr>"
  Wend
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>

<html>
  <head><title>OpenX2 Test #2 - ORDER BY</title>
<script type="text/javascript" language="JavaScript">
<!--
var sCurOrder = "<%= sOrderBy %>";
function ChangeOrder(sNewOrder)
{
  if(sCurOrder != sNewOrder)  {
    reorder.newOrder.value = sNewOrder;
    reorder.submit();
  }
}

-->
</script>

</head>
  <body>
<%
If bError Then
%>
    <div><%=sResult%></div>
<%
Else
%>
    Click on column caption to sort by it
    <form action="OX2test2.asp" method="post" name="reorder" id="reorder">
    <input type="hidden" name="newOrder" id="newOrder" value="au_lname">
    <table border="1">
      <tr>
          <td style="cursor: hand;" onClick="ChangeOrder('au_lname')"><b>Name</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('phone')"><b>Phone</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('address')"><b>Address</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('city')"><b>City</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('state')"><b>State</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('zip')"><b>Zip</b></td>
      </tr>
      <%=sResult%>
    </table>
    <form>
<%
End If
%>
  </body>
</html>


Samples - ASP VBScript - Simple Query

Code Samples -- VBScript

Example #3 - Simple Query

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sParam, sSQL
Dim bError
sSQL = "SELECT * FROM authors"

If Request.Form("param").Count > 0 Then
  If Not Request.Form("param") = "" Then
    sParam = Request.Form("param")
    sSQL = "SELECT :1 as param, authors.* FROM authors WHERE au_lname LIKE :1"
  End If
End If

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  Dim i
  sResult = ""
  bError = false
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oCommand.CommandText = sSQL
  If Not sParam = "" Then
    oCommand.ParamValueAsString(1) = sParam
  End If
  oCommand.Execute
  sResult = sResult & "<table border=1><tr>"
  For i = 1 To oCommand.FieldCount
    sResult = sResult & "<td><b>" & oCommand.FieldName(i) & "</b></td>"
  Next
  sResult = sResult & "</tr>"
  While oCommand.MoveNext
    sResult = sResult & "<tr>"
    For i = 1 To oCommand.FieldCount
      sResult = sResult & "<td>" & oCommand.FieldValueAsString(i) & "</td>"
    Next
    sResult = sResult & "</tr>"
  Wend
  sResult = sResult & "</table>"
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #3 - Parameter in SELECT</title></head>
  <body>
    <%= sSQL %>
    <form action="OX2testVB4.asp" method="post">
    <input type="text" name="param"><input type="submit" value="Requery">
    <div><%=sResult%></div>
    </form>
  </body>
</html>


Samples - ASP VBScript - Update

Code Samples -- VBScript

Example #4 - Update

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sResult1, sRetrieveSQL, sUpdateSQL
Dim bError, bMakeUpdate
bMakeUpdate = Request.Form("id").Count > 0
sRetrieveSQL = "SELECT * FROM authors"
sUpdateSQL = "UPDATE authors SET au_lname = :1, au_fname = :2, phone = :3, address = :4, city = :5, state = :6, zip = :7, contract = :8 WHERE authors.au_id = :id"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS processing the rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  Dim i
  bError = false
  sResult = ""
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  If bMakeUpdate Then
    oCommand.SetCommandText sUpdateSQL, 1
    For i = 1 To 8
      oCommand.ParamValueAsString(i) = Request.Form("fld" + CStr(i))
    Next
    oCommand.ParamValueAsString("id") = Request.Form("id")
    oCommand.Execute
  End If
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  sResult = sResult & "<table border=1><tr>"
  For i = 1 To oCommand.FieldCount
    sResult = sResult & "<td><b>" & oCommand.FieldName(i) & "</b></td>"
  Next
  sResult = sResult & "</tr>"

  Dim iFS
  While oCommand.MoveNext()
    sResult = sResult & "<form action='OX2testVB5.asp' method='post'>"
    sResult = sResult & "<tr>"
    sResult = sResult & "<td><input type='submit' value='Update Row'><input type='hidden' name='id' value='" & oCommand.FieldValueAsString(1) & "'></td>"
    For i = 2 To oCommand.FieldCount
      If oCommand.FieldSize(i) > 20 Then
        iFS = 20
      Else
        iFS = oCommand.FieldSize(i)
      End If
      sResult = sResult & "<td><input type='text' name='fld" & CStr(i - 1) & "' value='" & oCommand.FieldValueAsString(i) & "' size='" & CStr(iFS) & "'></td>"
    Next
    sResult = sResult & "</tr></form>"
  Wend
  sResult = sResult & "</table>"
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #5 - UPDATE Sample</title></head>
  <body>
    <%= sResult1 %>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP VBScript - Delete

Code Samples -- VBScript

Example #5 - Delete

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sResult1, sRetrieveSQL, sDeleteSQL
Dim bError, bDelete
bDelete = Request.Form("id").Count > 0
sRetrieveSQL = "SELECT * FROM authors"
sDeleteSQL = "DELETE FROM authors WHERE authors.au_id = :id"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS processing the rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  Dim i
  bError = false
  sResult = ""
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  If bDelete Then
    oCommand.SetCommandText sDeleteSQL, 1
    oCommand.ParamValueAsString("id") = Request.Form("id")
    oCommand.Execute
  End If
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  sResult = sResult & "<table border=1><tr>"
  For i = 1 To oCommand.FieldCount
    sResult = sResult & "<td><b>" & oCommand.FieldName(i) & "</b></td>"
  Next
  sResult = sResult & "</tr>"

  While oCommand.MoveNext()
    sResult = sResult & "<form action='OX2testVB6.asp' method='post'>"
    sResult = sResult & "<tr>"
    sResult = sResult & "<td><input type='submit' value='Delete Row'><input type='hidden' name='id' value='" & oCommand.FieldValueAsString(1) & "'></td>"
    For i = 2 To oCommand.FieldCount
      sResult = sResult & "<td> " & oCommand.FieldValueAsString(i) & "</td>"
    Next
    sResult = sResult & "</tr></form>"
  Wend
  sResult = sResult & "</table>"
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #5 - UPDATE Sample</title></head>
  <body>
    <%= sResult1 %>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP VBScript - Insert

Code Samples -- VBScript

Example #6 - Insert

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sResult1, sRetrieveSQL, sInsertSQL
Dim bError, bDelete
bInsert = (Request.Form("id").Count > 0 And Request.Form("id") = "Add New")
sRetrieveSQL = "SELECT * FROM authors"
sInsertSQL = "INSERT INTO authors ( au_id, au_lname, au_fname, phone, address, city, state, zip, contract ) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9 )"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS processing the rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  Dim i
  bError = false
  sResult = ""
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  If bInsert Then
    Dim iRnd
    Randomize
    iRnd = CStr(Round(1000000000 * Rnd))
    oCommand.CommandText = sInsertSQL
    oCommand.ParamValueAsString(1) = Mid(iRnd, 1, 3) & "-" & Mid(iRnd, 4, 2) & "-" & Mid(iRnd, 5, 4)
    For i = 2 To 9
      oCommand.ParamValueAsString(i) = Request.Form("fld" + CStr(i))
    Next
    oCommand.Execute
  End If
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  sResult = sResult & "<form action='OX2testVB7.asp' method='post'>"
  sResult = sResult & "<table border=1><tr>"
  For i = 1 To oCommand.FieldCount
    sResult = sResult & "<td><b>" & oCommand.FieldName(i) & "</b></td>"
  Next
  sResult = sResult & "</tr><tr><td><input type='submit' name='id' value='Add New'></td>"
  For i = 2 To oCommand.FieldCount
    If oCommand.FieldSize(i) > 20 Then
      iFS = 20
    Else
      iFS = oCommand.FieldSize(i)
    End If
    sResult = sResult & "<td><input type='text' name='fld" & CStr(i) & "' value='' size='" & CStr(iFS) & "'></td>"
  Next
  sResult = sResult & "</tr>"

  While oCommand.MoveNext()
    sResult = sResult & "<tr>"
    For i = 1 To oCommand.FieldCount
      sResult = sResult & "<td> " & oCommand.FieldValueAsString(i) & "</td>"
    Next
    sResult = sResult & "</tr></form>"
  Wend
  sResult = sResult & "</table>"
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #5 - UPDATE Sample</title></head>
  <body>
    <%= sResult1 %>
    <div><%=sResult%></div>
  </body>
</html>


Samples - ASP VBScript - Example #7 - Field Type

Code Samples -- VBScript

Example #7 - Field Type

<%@ Language=VBScript %>
<!--#include file="OpenX2VB.inc"-->
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sSQL
Dim bError
Dim i

sRetrieveSQL = "SELECT * FROM titles"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  sResult = ""
  bError = false
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  sResult = sResult & "<table border=1><tr>"
  For i = 1 To oCommand.FieldCount
    sResult = sResult & "<td><b>" & oCommand.FieldName(i) & "</b></td>"
  Next
  sResult = sResult & "</tr>"

  Dim aNum
  Dim aDate
  While oCommand.MoveNext
    sResult = sResult & "<tr>"
    For i = 1 To oCommand.FieldCount
      Select Case oCommand.FieldType(i)
        Case dtShort
        Case dtLong
          sResult = sResult & "<td align='right'style='font-size: 10pt;'>" & oCommand.FieldValueAsString(i) & " </td>"
        Case dtDouble
          aNum = oCommand.FieldValueAsDouble(i)
          sResult = sResult & "<td align='right'style='font-size: 10pt;'>$" & FormatNumber(aNum, 2) & " </td>"
        Case dtDateTime
          aDate = oCommand.FieldValueAsDate(i)
          sResult = sResult & "<td align='center'style='font-size: 10pt;'>" + FormatDateTime(aDate, 2) + " </td>"
        Case Else
          sResult = sResult & "<td style='font-size: 10pt;'>" + oCommand.FieldValueAsString(i) + " </td>"
      End Select
    Next
    sResult = sResult & "</tr>"
  Wend
  sResult = sResult & "</table>"

End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #8 - 'Field Type'</title></head>
  <body>
    <div><%=sResult%></div>
  </body>
</html>

<% ' OpenX2VB.inc

' Transaction Isolation Level
Const tilUnknown = -1	' default
Const tilReadUncommitted = 0
Const tilReadCommitted = 1
Const tilRepeatableRead = 2
Const tilSerializable = 3

' AutoCommit
Const acAutoCommitUnknown = -1
Const acAutoCommitOff = 0
Const acAutoCommitOn = 1

' Data types
Const dtUnknown = 0
Const dtShort = 1
Const dtLong = 2
Const dtDouble = 3
Const dtDateTime = 4
Const dtString = 5
Const dtBytes = 6
Const dtLongBinary = 7
Const dtLongChar = 8
Const dtBLob = 9
Const dtCLob = 10
Const dtCursor = 11

' Command types
Const cmdTypeUnknown = 0  ' default
Const cmdTypeSQLStmt = 1
Const cmdTypeStoredProc = 2

' Parameter types
Const ptParamInput = 0
Const ptParamInputOutput = 1
Const ptParamOutput = 2
Const ptParamReturn = 3

%>


Samples - ASP VBScript - Example #8 - XML Processing

Code Samples -- VBScript

Example #8 - XML Processing

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sSQL, sResultStr, sResultDoc
Dim bError
Dim i
sSQL = "SELECT * FROM titles"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  bError = false
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oCommand.CommandText = sSQL
  sResultStr = oCommand.XMLString
  oCommand.Close()

  oCommand.CommandText = sSQL
  REM // Set stylesheet locations
  Dim styleFile
  Set xmlTmpl = Server.CreateObject("Msxml2.XSLTemplate")
  Set xslStyle = Server.CreateObject("Msxml2.FreeThreadedDOMDocument")
  styleFile = Server.MapPath("OpenX2.xsl")
  xslStyle.async = false
  xslStyle.load(styleFile)
  Set xmlTmpl.stylesheet = xslStyle
  Set xmlProc = xmlTmpl.createProcessor()
  REM // Get the XMLDocument from OpenX2
  xmlProc.input = oCommand.XMLDocument
  If xmlProc.transform() Then
      sResultDoc = xmlProc.output
  End If
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>

<html>
  <head><title>OpenX2 Test #9 - XML Output</title></head>
<%
If Not bError Then
%>
  <XML id="source"><%=sResultStr%></XML>
  <XML id="style" src="OpenX2.xsl"></XML>
  <script type="text/javascript" event="onload" for="window">
    showResult.innerHTML = source.transformNode(style.XMLDocument);
  </script>
<%
End If
%>
  <body>
<%
If Not bError Then
%>
    <h1>Server-Side XMLDocument Processing</h1>
    <div><%=sResultDoc%></div>
    <h1>Client-Side XML-Island (String) Processing</h1>
    <div id="showResult"></div>
<%
Else
%>
  <div><%=sResultDoc%></div>
<%
End If
%>
<%= sResult %>
  </body>
</html>


Samples - ASP VBScript - Example #9 - TEXT (CLOb/LongChar) Fields

Code Samples -- VBScript

Example #9 - TEXT (CLOb/LongChar) Fields Reading

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sRetrieveSQL, sSetTextSize
Dim bError
Dim i
sSetTextSize = "SET TEXTSIZE "
sRetrieveSQL = "SELECT publishers.pub_name, publishers.city, publishers.state, publishers.country, pub_info.pub_id, pub_info.pr_info FROM pub_info, publishers WHERE ( pub_info.pub_id = publishers.pub_id ) ORDER BY publishers.pub_name"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  i = 0
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  oCommand.CommandText = sSetTextSize & "12000"
  oCommand.Execute
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  Do While oCommand.MoveNext
    sResult = sResult & "<tr>"
    sResult = sResult & "<td style='font-size: 10pt; cursor: hand;' onClick='ShowDesc(row" & oCommand.FieldValueAsString(5) & ")'><b>Show/Hide Info</b></td>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(1) & " </td>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(2) & " </td>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(3) & " </td>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(4) & " </td>"
    sResult = sResult & "</tr>"
    sResult = sResult & "<tr id='row" & oCommand.FieldValueAsString(5) & "' class='hiddentr'>"
    sResult = sResult & "<td colspan='5'>" & oCommand.FieldValueAsString(6) & " </td>"
    sResult = sResult & "</tr>"
    i = i + 1
    If i = 3 Then
      Exit Do
    End If
  Loop
  oCommand.CommandText = sSetTextSize & "0"
  oCommand.Execute
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>

<html>
  <head><title>OpenX2 Test #10 - TEXT (CLOb/LongChar) Fields Reading </title>
  <STYLE TYPE="text/css">
    tr.visibletr { font-size: 8pt; visibility: visible; position: static; }
    tr.hiddentr { font-size: 8pt; visibility: hidden; position: absolute; }
  </STYLE>
<script type="text/javascript" language="JavaScript">
<!--

function ShowDesc(theTR)
{
  if(theTR)  {
    if(theTR.className == "visibletr")
      theTR.className = "hiddentr";
    else
      theTR.className = "visibletr";
  }
}

-->
</script>

</head>
  <body>
    <table border="1">
      <tr>
          <td><b>Info</b></td>
          <td><b>Name</b></td>
          <td><b>City</b></td>
          <td><b>State</b></td>
          <td><b>Country</b></td>
      </tr>
      <%=sResult%>
    </table>
  </body>
</html>
ASP file to retrieve image from the database (OX2ImageVB.asp)

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sRetrieveSQL, sSetTextSize
Dim bError
Dim pub_id
pub_id = Request("pub_id")
sSetTextSize = "SET TEXTSIZE "
sRetrieveSQL = "SELECT pub_info.logo FROM pub_info WHERE pub_info.pub_id = '" & pub_id & "'"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  oCommand.CommandText = sSetTextSize & "32768"
  oCommand.Execute
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  If Not oCommand.isEmpty Then
    Response.Clear
    Response.ContentType = "image/gif"
    Response.BinaryWrite(oCommand.FieldValueAsBinary(1))
  End If
  oCommand.CommandText = sSetTextSize & "0"
  oCommand.Execute
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr
%>
Example #9 - TEXT (CLOb/LongChar) Fields Writing

<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sRetrieveSQL, sUpdateSQL
Dim bError, bMakeUpdate
Dim i
sRetrieveSQL = "SELECT publishers.pub_name, publishers.city, publishers.state, publishers.country, pub_info.pub_id, pub_info.pr_info FROM pub_info, publishers WHERE ( pub_info.pub_id = publishers.pub_id ) ORDER BY publishers.pub_name"
sUpdateSQL = "UPDATE pub_info SET pr_info = :pr_info WHERE pub_id = :id"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  i = 0
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  If bMakeUpdate Then
    oCommand.CommandText = sUpdateSQL
    oCommand.ParamValueAsString("pr_info") = Request.Form("textField")
    oCommand.ParamValueAsString("id") = Request.Form("id")
    oCommand.Execute
  End If

  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  Do While oCommand.MoveNext
    sResult = sResult & "<form action='OX2test11.asp' method='post'>"
    sResult = sResult & "<tr>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(1) & " </td>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(2) & " </td>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(3) & " </td>"
    sResult = sResult & "<td style='font-size: 10pt;'>" & oCommand.FieldValueAsString(4) & " </td>"
    sResult = sResult & "</tr>"
    sResult = sResult & "<tr>"
    sResult = sResult & "<td colspan='4'><textarea cols='77' rows='7' name='textField'>" & oCommand.FieldValueAsString(6) & "</textarea></td>"
    sResult = sResult & "</tr>"
    sResult = sResult & "<tr>"
    sResult = sResult & "<td align='center'colspan='4'><input type='submit' value='Update Info'><input type='hidden' name='id' value='" & oCommand.FieldValueAsString(5) & "'><hr></td>"
    sResult = sResult & "</tr>"
    sResult = sResult & "</form>"
    i = i + 1
    If i = 3 Then
      Exit Do
    End If
  Loop
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>

<html>
  <head><title>OpenX2 Test #11 - TEXT (CLOb/LongChar) Fields Writing </title></head>
  <body>
    <table border="1">
      <tr>
          <td><b>Name</b></td>
          <td><b>City</b></td>
          <td><b>State</b></td>
          <td><b>Country</b></td>
      </tr>
      <%=sResult%>
    </table>

  </body>
</html>
© 2001 www.openx.ca


Samples - ASP JScript - Advanced Technique

Advanced Code Samples -- JScript

Example - Advanced Technique

<%@ Language=JScript %>
<%
var oConn = Server.CreateObject("OpenX2.Connection")
var oCommand = Server.CreateObject("OpenX2.Command")
var sResult = "";
var bError = false;
var sOrderBy = "au_lname";
var sFields = new Array(6);
sFields[0] = "au_lname";
sFields[1] = "phone";
sFields[2] = "address";
sFields[3] = "city";
sFields[4] = "state";
sFields[5] = "zip";
var sFieldValues = new Array(6);
var sFieldMatch = new Array(6);

if(Request.Form("newOrder").Count > 0)  {
  sOrderBy = Request.Form("newOrder");
}
var sMoreParams = "";

var i;
for (i = 0; i < 6; i++) {
  sFieldMatch[i] = "";
  sFieldValues[i] = "";
  if(Request.Form(sFields[i]).Count > 0)  {
    if(Request.Form(sFields[i]) != "")  {
      var sOperator;
      if(Request.Form("cb_" + sFields[i]).Count > 0)  {
        sOperator = "= lower('" + Request.Form(sFields[i]) + "') ) ";
        sFieldMatch[i] = " checked";
      }
      else  {
        sOperator = "LIKE lower('%" + Request.Form(sFields[i]) + "%') ) ";
      }

      if(sMoreParams != "")
        sMoreParams += "AND ";
      else
        sMoreParams += "WHERE ";
      sMoreParams += " ( lower(" + sFields[i] + ") " + sOperator;
      sFieldValues[i] = Request.Form(sFields[i]);
    }
  }
}
var sSQL = "SELECT au_fname + ' ' + au_lname, phone, address, city, state, zip FROM authors "  + sMoreParams + "ORDER BY " + sOrderBy;

try {
  oConn.Connect("ms_ox1");
  oCommand.Connection = oConn;
  oCommand.CommandText = sSQL;
  oCommand.Execute();
  while(oCommand.MoveNext())  {
    sResult += "<tr>";
    for(i = 1; i <= oCommand.FieldCount; i++)  {
      sResult += "<td>" + oCommand.FieldValueAsString(i) + "</td>";
    }
    sResult += "</tr>";
  }
}
catch(e) {
  bError = true;
  if(oCommand.ErrorCode != 0)
    sResult = "OpenX2 Command Error: " + oCommand.ErrorInfo + ". Error #" + oCommand.ErrorCode + " (" + oCommand.ErrorCodeEx + ")<br />";
  else
    if(oConn.ErrorCode != 0)
      sResult += "OpenX2 Connection Error: " + oConn.ErrorInfo + ". Error #" + oConn.ErrorCode + " (" + oConn.ErrorCodeEx + ")<br />";

  sResult += "ASP Error: #" + e.number + ". " + e.description + "<br />";
}

%>

<html>
  <head><title>OpenX2 Test #3 - ORDER BY, FILTER</title>
<script type="text/javascript" language="JavaScript">
<!--
var sCurOrder = "<%= sOrderBy %>";
function ChangeOrder(sNewOrder)
{
  if(sCurOrder != sNewOrder)  {
    reorder.newOrder.value = sNewOrder;
    reorder.submit();
  }
}

function ApplyFilter(sField, sValue)
{
  reorder.sField.value = sValue;
  reorder.submit();
}
-->
</script>

</head>
  <body>
  Generated Query - "<%= sSQL %>"<br>
<%
if(bError)  {
%>
    <div><%=sResult%></div>
<%
}
else  {
%>
    Click on column caption to sort by it<br>
    <form action="OX2test3.asp" method="post" name="reorder" id="reorder">
    <input type="hidden" name="newOrder" id="newOrder" value="au_lname">
    <table border="1">
      <tr><td colspan="6" align="center"><input type="submit" value="Refresh"></td></tr>
      <tr>
          <td style="cursor: hand;" onClick="ChangeOrder('au_lname')"><b>Name</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('phone')"><b>Phone</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('address')"><b>Address</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('city')"><b>City</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('state')"><b>State</b></td>
          <td style="cursor: hand;" onClick="ChangeOrder('zip')"><b>Zip</b></td>
      </tr>
      <tr>
          <td><input type="text" name="au_lname" id="au_lname" value="<%= sFieldValues[0] %>"></td>
          <td><input type="text" name="phone" id="phone" value="<%= sFieldValues[1] %>"></td>
          <td><input type="text" name="address" id="address" value="<%= sFieldValues[2] %>"></td>
          <td><input type="text" name="city" id="city" value="<%= sFieldValues[3] %>"></td>
          <td><input type="text" name="state" id="state" value="<%= sFieldValues[4] %>"></td>
          <td><input type="text" name="zip" id="zip" value="<%= sFieldValues[5] %>"></td>
      </tr>
      <tr>
          <td><label for="1">Exact matches</label> <input type="checkbox" name="cb_au_lname" id="1" value="1"<%= sFieldMatch[0] %>></td>
          <td><label for="2">Exact matches</label> <input type="checkbox" name="cb_phone" id="2" value="1"<%= sFieldMatch[1] %>></td>
          <td><label for="3">Exact matches</label> <input type="checkbox" name="cb_address" id="3" value="1"<%= sFieldMatch[2] %>></td>
          <td><label for="4">Exact matches</label> <input type="checkbox" name="cb_city" id="4" value="1"<%= sFieldMatch[3] %>></td>
          <td><label for="5">Exact matches</label> <input type="checkbox" name="cb_state" id="5" value="1"<%= sFieldMatch[4] %>></td>
          <td><label for="6">Exact matches</label> <input type="checkbox" name="cb_zip" id="6" value="1"<%= sFieldMatch[5] %>></td>
      </tr>
      <%=sResult%>
    </table>
    <form>
<%
}
%>
  </body>
</html>


Samples - C++ - Retrieve Data

Code Samples -- Visual C++

Example #1 - Retrieve Data

#include <tchar.h>
#import "OX2.dll"
#include <iostream.h>

#define CMDUNKNOWN  0 // detect command type automatically
#define CMDSQLSTMT  1 // SQL statement
#define CMDSTOREDPROC 2 // Stored procedure or a function

int main(int argc, char* argv[])
{
 CoInitialize(NULL);

 OX2Lib::IConnectionPtr conn;
 if(S_OK == conn.CreateInstance("OpenX2.Connection")) {
  if(VARIANT_TRUE == conn->Connect(_bstr_t("ms_ox1"))) {
   OX2Lib::ICommandOXPtr cmd = conn->CreateCommand(_bstr_t("SELECT * FROM authors"), VARIANT_TRUE, CMDSQLSTMT);
   if(cmd != NULL) {
    while(cmd->MoveNext() == VARIANT_TRUE) {
     for(int i = 1; i <= cmd->FieldCount; i++) {
      _variant_t vIndex((long)i);
      _bstr_t str = cmd->FieldValueAsString[vIndex];
      cout << (LPCTSTR)str << _T("\t");
     }
     cout << endl;
    }
    cmd.Release();
   }
  }
  conn.Release();
 }

 CoUninitialize();
 return 0;
}
© 2001 www.openx.ca


OpenX How-To.
We are sorry, this page is still under construction.

Please, visit this page later.


Frequently Asked Questions

Online Frequently Asked Questions
We are sorry, this page is still under construction.

Please, visit this page later.