PrismCore Interface Spec

Owned by Dawn Brunke (Unlicensed)
Last updated: Feb 29, 2024 by Sue Busker
4 min read

Introduction

  • Integration with the PrismCore database by third-party applications is handled through the WPServer application. 

  • WPServer supports multiple import and export types, and has the ability to import and export the data in a variety of formats. 

  • In the event that a third-party application requires data that is not contained within the standard PrismCore interfaces, the WPServer application can be modified to accommodate a custom interface.

  • Third-party applications should not alter the PrismCore databases (prism, prism_security).  Doing so voids all warranty and support agreements.  However, third-party applications are permitted to access and update the prism_trn database which is used to transfer interface information.  Either database may be linked using a direct ODBC connection.

  • This document describes the interface types, standard import and export formats, the standard transports, and the requirements for developing a custom interface.

  • For information on installation and configuration of the WPServer application, please refer to the document entitled: PrismCore Application Server Installation and Configuration.

 

Formats

The WPServer application can be configured to export files in a variety of formats.  Third-party applications can also import or export data using an ODBC connection to the prism_trn database.

ODBC

The tables and data in the prism_trn database on the PrismCore SQL Server can be accessed using the Microsoft SQL Server ODBC driver.  This allows a programmer using Visual Basic, Visual C++ or other programming languages to directly read and write data into the PrismCore transfer database.  In addition an ODBC connection can be used to link to the prism database to run queries. 

Note: The prism database should be limited to read only.  Writing directly to the prism database voids all warranty and support agreements. It is important to monitor resources when running queries on the live database.

Comma Delimited ASCII

The WPServer application can be configured to import and export files in comma delimited ASCII.  The following conventions should be used when creating or parsing the files:

  • Exported date Formats will be MM/DD/YYYY.

  • Exported currency values have four digits of precision (0.0000).

  • Empty fields can either have “” or simply a ,.

  • Double quotes (“) inside of double quotes should be prefixed with a backslash (\).

  • Carriage returns inside of quoted fields should be represented as \r.

  • Line feeds inside of quoted fields should be represented as \n.

  • Lines are terminated with a carriage return / line feed pair.

  • Numeric data does not require quotes.

  • Data cannot contain a comma as this creates an extra field.

Example:  “01/01/1970”, “Joe \”Bob\” Brown”, 120000, “line feed and carriage return\r\n”

Tab Delimited ASCII

Tab delimited files follow the same formatting conventions as the comma delimited files, however, the comma used to separate the fields is replaced by a tab character (hex 0x09).

XML

The WPServer application imports and exports simple XML documents.  Schemas and DTDs are not supported.  The required elements and attributes are defined for each interface in the Interface Types section.

PRISM Format

For backward compatibility, the WPServer application supports the standard import and export formats from the PRISM application.  The PRISM formats are fixed-length ASCII files.

No field de-limiters are used.  Each file can contain multiple records separated by a line feed (CHAR 10).  Records should contain only ASCII characters from 32 (space) through 126 (tilde), and line feeds.  All alpha should be UPPER-CASE. When a tilde is found in the first position of a field, the field is left unchanged. This should only be done with records that already exist and that contain valid values in the designated fields.

Fields identified as REQUIRED are usually for indexing key purposes.  Fields identified as OPTIONAL should be space filled if alpha/numeric and zero filled if numeric.  Numeric fields should be right justified and the decimal point should be suppressed.

Concerning the PRISM format, there are a few instances where certain fields may have a different maximum size as compared to their ASCII counterparts.  For example, in AR Receipt Export the Receipt Number (Transaction Number) in the PRISM format only allows up to 10 characters vs. 25 characters for the receipt_number field in the delimited ASCII format, as well as the 25 characters for the TranNumber field in the

Transaction_Header table, etc.  Just be aware of this and exercise due caution when using the various PRISM formats.

Fixed Length ASCII

The only fixed length ASCII format supported for standard interfaces is the prism format. Custom file formats utilizing Fixed Length ASCII can be produced.  Please see “Custom” below.

Custom

Any alteration from a standard format is considered a Custom file format. Customization can be as small as adding a field to a standard format or up to processing completely unique formats.  For information on developing a custom interface, contact Nebraska Book Support.

Transports

WPServer is capable of transporting using either FTP or Secure FTP.  Files may be transported outside of wpserver by a variety of methods.  Examples:

  • SQL jobs

  • User copies to Shared folders on their desktop

  • Add an ftp server to the PrismCore server that the outside source uses to upload or download.

  • Cron jobs that copy to a network resource such as a shared folder.

  • Third party sharing software

 

WPServer Email Alerts

WPServer can be configured to send an E-mail when an error occurs with an interface line. Information on setup can be found here: E-mail configuration for WPServer Notifications

 

Interface Types

The following sections describe the database tables in the prism_trn database that are used to import and export data.