This document describes how to access the Primavera Software Development Kit (SDK). The Primavera SDK makes Project Management data available to external applications. In addition to data, the Primavera SDK provides application business rules and calculated values that facilitate integration with customer databases and applications.

The Primavera SDK makes Primavera data available via an Open Database Connectivity (ODBC) type interface and is also compatible with any ODBC client. ODBC clients include programming languages such as VB, PowerBuilder, C++, scripting tools, and so on. The SDK requires some expertise in extracting data from a database (typically, using Structured Query Language (SQL) queries or programming tools), along with some knowledge of the application schema (tables and fields). The SDK supports both read and write of Primavera data.

Physical Schema vs. Logical Schema (return to Table of Contents)

The physical schema is the schema at the database level. The physical data model specifies implementation details to match features of a particular product, as well as configuration information for that database instance. For example, when you run the database setup scripts, Oracle and SQL Server require different SQL scripts.

The logical data model of the database is independent of the eventual target database (Oracle/SQL Server.) The logical schema is exposed as a normalized, in-memory representation of data. The logical schema is similar to the physical schema, but does contain some differences. For example, calculated fields such as Earned Value may be included in the logical schema, but they are not stored directly in the physical schema. These fields are calculated in memory and are accessible via the Primavera SDK.

Interfaces Supported (return to Table of Contents)

ODBC technology provides a common interface for accessing heterogeneous SQL databases. ODBC is based on SQL as a standard for accessing data. This interface provides maximum interoperability: a single application can access different SQL Database Management Systems (DBMS) through a common set of code. This enables a developer to build and distribute a client/server application without targeting a specific DBMS. Database drivers are then added to link the application to the user's choice of DBMS.

Since ODBC is supported, other third-party bridges such as OLEDB and JDBC also provide access.

ODBC Open Database Connectivity (ODBC) technology provides a common interface for accessing heterogeneous SQL databases. ODBC is based on Structured Query Language (SQL) as a standard for accessing data. This interface provides maximum interoperability: a single application can access different SQL Database Management Systems (DBMS) through a common set of code. This enables a developer to build and distribute a client/server application without targeting a specific DBMS. Database drivers are then added to link the application to the user’s choice of DBMS.

OLEDB OLEDB is a set of interfaces that expose data from a variety of sources by using the Component Object Model (COM). OLEDB interfaces provide applications with uniform access to data stored in diverse information sources. These interfaces support the amount of DBMS functionality appropriate to the data store, enabling the data store to share its data. In addition we may choose to expose custom interfaces to enable clients to access functionality beyond data manipulation. It’s really up to us, we may choose to expose some/all functionality. These additional interfaces can co-exist with the interfaces required by OLEDB. This may be accomplished via COM aggregation or containment.

JDBC JDBC is intended to be a low-level API that supports basic SQL functionality. The authors of JDBC expect higher-level APIs to be defined and possibly implemented on top of JDBC.

Special Business Functions (return to Table of Contents)

In cases where special business functions need to be exposed via the ODBC interface, stored procedures are used to allow the client to invoke functions not directly related to data manipulation. For example, a client may use "EXEC SQL call create_project(wbs_id, 'APEX' );" to add a project to the database via the ODBC interface. The number of cases where these hooks are required is fairly small, and the stored procedure model accomplishes it well. All other business logic pertaining to data update is built into the ODBC interface, much like triggers are built into a traditional physical database.

Programmatic Access (return to Table of Contents)

The examples provided in this document use MS ActiveX Data Objects (ADO) for data access. ADO offers a simple object model that exposes connections upon which to execute and return record sets and field values. This method for accessing data can be used via Visual Basic, C++, JScript and many other popular programming languages. The SQL queries provided in this document are applicable to other ODBC tools as well.

Primavera SDK and Multi-user Access (return to Table of Contents)

Primavera SDK has a table called USESSION. This table typically shows only the row associated with the current session. Users are prevented from inserting or deleting data from this table, as it is only for reference. Note that the underlying physical database shows one row for each logged on user, but these are not shown via the Primavera SDK interface.

The Primavera SDK obeys all multi-user rules and can be used in a multi-user environment where many PM tools access the database at the same time. The Primavera SDK does not, however, support more than one connection to a Primavera SDK data source on any given client machine. Opening a second connection cancels the license slot granted to the first client, and both clients may become unstable.

Concurrency/Licensing Issues with Primavera SDK (return to Table of Contents)

A client program using the Primavera SDK driver participates in the same concurrency mechanism as other Project Management client programs. Other applications are notified of changes made via the Primavera SDK driver, and the Primavera SDK is able to detect when changes are made to the underlying data by other applications. The mechanism for updating the client's view of the new data via Primavera SDK is invoked via ODBC by a stored procedure called refresh().

The refresh() stored procedure can be invoked at any time during the life of an active Primavera SDK connection. If “stale” data does not exist, the refresh call does nothing. It is recommended that the Primavera SDK programmer invoke the refresh() procedure every few minutes while the client application is active. It does not matter how often data is refreshed. The decision to refresh the data is up to the individual client and programmer and depends on how often the application needs to see updated data. Note that for some operations, the Primavera SDK driver will invoke the refresh() on its own.

Additionally, Primavera SDK periodically updates its "last active" timestamp in the underlying database for licensing purposes. This process runs automatically in the background for an active Primavera SDK connection and does not require any special handling by the programmer.

ODBC DSN Setup (return to Table of Contents)

The Primavera SDK can be used as a stand-alone application without other Primavera client applications. The standard SDK setup installs the Borland Database Engine (BDE) if it does not already exist on the system. Like other ODBC drivers, the Primavera SDK requires an ODBC Data Source to be set up with the Primavera SDK driver before the data can be accessed.

The SDK installation adds an ODBC driver named Primavera SDK to the system. The setup process also prompts the user to create a database alias and creates a System DSN to access the SDK. The default values can be modified at any time.

The following parameters are required during the DSN setup for the SDK to function properly:

ODBC Name: The name the user sees in the ODBC System Data Sources.

Database Alias: The database alias used by BDE to connect to the database (defined by the user during SDK setup).

System and User Requirements:

Installing Primavera SDK on Windows NT and Windows 2000 requires administrative rights.
System requirements are the same for SDK as for other Primavera applications.

Working with the ODBC Data Source Administrator (return to Table of Contents)

To edit/add/remove a Primavera SDK data source, use the ODBC Data Source Administrator found in Control Panel.

In Control Panel you should have a ‘Data Sources (ODBC)’ icon.

Double-click on this icon to start the ODBC Data Source Administrator.

(Windows XP, 2000 users go to ‘Administrative Tools’ in Control Panel.)

In the ODBC Data Source Administrator, if you click on the ‘System DSN’ tab you will see the data source that

was added during the installation of the Primavera SDK. Use this dialog box to Add/Remove/Configure your

Primavera SDK data sources.

If you need to add an additional Primavera SDK data source, you simply click the ‘Add’ button in the

dialog box above and select the ‘Primavera Software Development Kit’ as the driver for your data source.

You will then be given the following dialog to complete, just as you are during the installation of the Primavera SDK.

Delayed Loading (return to Table of Contents)

Delayed Loading is a mechanism for loading global data only when it is requested. The installation of Primavera SDK installs PMSDK.ini file in the

\Primavera Common\PMSDK\Engines folder where delayedLoadMode can be turned ON or OFF. By default delayedLoadMode is turned ON. With

delayed loading ON, the time to establish a connection is reduced due to the fact that not all of the global data is loaded into memory. Also,

response/loading time is reduced when updating the scope_level for projects since only global data associated with that project is loaded.

Read only access to the Primavera SDK (return to Table of Contents)

Users that log into the Primavera SDK can be configured so that they are restricted from making edits and have read-only access to all global and project data.

This can be accomplished by creating a global profile under Admin, Security Profiles with the global privilege View All Global/Project Data via SDK set. A user that

logs in via the Primavera SDK with a global profile and this global privilege set will have read-only access.

For Example:

A user that logs into the Primavera SDK with read-only access is able to update the following fields:

Table:	Field:	Explanation:	Sample:
PROJECT	scope_level	Necessary to load project level data, but data will be read-only	UDPATE PROJECT SET scope_level=7 WHERE proj_id =314
PROJBASE	current_baseline_flag (primary) current_baseline_flag1 (secondary) current_baseline_flag2 (tertiary)	Sets the current SDK session user baseline for a given project.	UPDATE PROJBASE SET current_baseline_flag = ‘Y’ WHERE proj_id = 123
PROJBASE	project_is_project_baseline_flag	Sets the baseline as the project baseline for a given project.	UPDATE PROJBASE SET project_is_project_baseline_flag = ‘Y’ WHERE proj_id = 123
SYSOPTIONS	curr_id	Sets the view currency to be used for monetary field types	UPDATE SYSOPTIONS SET curr_id = 11
SYSOPTIONS	spread_interval	Sets the spread interval to be used by the *_SPREADS tables -- values are Year, Quarter, Month, Week, Day, and Hour. Default is ‘M’.	UPDATE SYSOPTIONS SET spread_interval = ‘W’

Global and Project Scope Level (return to Table of Contents)

By default, when a user opens a connection to the Primavera SDK (via ODBC) using the Project Management engine, the global data are loaded (for example Resource Pool and Reports), but the project-specific data are not loaded (for example WBS, Activities and Resource Assignments).

To load project data, the application programmer must indicate to the driver the name and location of the projects to load. This is accomplished by updating a logical field in the PROJECT table called scope_level. The higher the scope_level value, the more detail is loaded.

When the driver loads, all projects are at scope_level 1, which includes only information about the project itself, no details about its structure or contents. When clients want to see project level details, they must increment the scope_level for those projects from 1 to 7. For example:

UPDATE PROJECT SET scope_level = 7 WHERE proj_id IN (151, 209, 3005)

loads detail data for those three projects. If the database is small, the programmer may choose to load more, or even all project data. If the projects are numerous and large, the client may load only a few projects at a time. Updating the scope_level from 7 to 2 causes the driver to unload the data and frees up memory for other projects. This process is similar to loading (opening) projects in the Project Management application when you first log on to the Windows version.

The following scope levels are available:

Title	Scope Level	Description
SCOPE_LEVEL_PROJECT_ONLY	1	Only root WBS - User has security to view this project's root WBS detail information
SCOPE_LEVEL_MAX_WBS	2	WBS up to the max WBS level that is specified for the Summarizer
SCOPE_LEVEL_ALL	7	All activities, documents, and other information

Access Level (return to Table of Contents)

Along with setting the scope level, the access level must also be set when opening a project. Three types of access levels are available: Read Only, which is the default, Shared, and Exclusive. Read Only does not allow any data to be updated. To update data, the scope level must be set to Shared which opens a shared copy of the project, or Exclusive, which opens a master copy of the project, locking out all other users. The setting for each level is defined in the following table:

Access Level	Setting
Read Only	0
Shared	1
Exclusive	2

Tables (return to Table of Contents)

A listing of all of the tables exposed by the Primavera SDK is provided in the included GeneralTable&Fields.htm file. This file provides a list of tables with a general description of the data stored in that table. The table names provide a hotlink to detailed table information including field name, field type, maximum length, read-only, nullable, time distributed, and description.

Extended Tables (return to Table of Contents)

Extended tables are offered to allow for optimized joins. For example, the table X_TASK offers the TASK table fields (task_id to actv_code_contractor_code_id) and adds extended information including PROJWBS, PROJECT, RSRC, and CALENDAR, as well as extended activity code information. The extended tables are available for selecting data. Client programmers are strongly encouraged to use the extended columns in place of creating traditional table joins in SQL. This mechanism providea much better performance.

An example of traditional joins versus extended columns is as follows:

Query Using Traditional Joins:

SELECT t.task_name, r.rsrc_name

FROM task t, rsrc r, taskrsrc tr

WHERE t.task_id = tr.task_id

AND r.rsrc_id = tr.rsrcid

Query Using Extended Columns:

SELECT TASK__task_name, RSRC__rsrc_name FROM x_taskrsrc

Note the following features of using extended columns:

1) Extended column queries always have only one table in the FROM-clause -- it should be the primary relationship you are querying.

2) Extended column queries reference other joined tables via the syntax: <joined_table_name>__<column_name>. The system utilizes the

pre-indexed foreign keys internally, and does not require the programmer to specify the join relationships.

3) Avoid using ‘SELECT * FROM X_<table>’ when working with Extended tables. Extended tables can have a number of columns that exceed the limit that the client being used can support. To prevent exceeding client limitations, construct efficient SQL statements like:

SELECT TASK__task_name, RSRC__rsrc_name FROM x_taskrsrc

when using extended tables.

Meta Tables (return to Table of Contents)

Meta tables are logical tables that describe information about the structure and status of the (logical) database.

SYSTABLES

contents: lists one row per table for other logical tables

example columns: api_table_name, table_title, guid_field, import_options

special fields: last_assigned_key (integer), identifies the last generated primary key for this table

SYSCOLUMNS

contents: lists one row per field across all logical tables

example columns: api_table_name, field_name, data_type, field_length, read_only_flag

SYSOPTIONS

The SYSOPTIONS table is used primarily for reference by programmers and testers. It contains only one row, and has no reflection in the physical database. Only one field is updatable, and the rest are read-only (see the following table).

Column Name	Valid Values	Meaning
Id	1 (readonly)	primary key
engine_type	<string> (readonly)	internal object type and suite identifier for driver
engine_version	<integer> (readonly)	build number of driver engine (matches GUI build number)
physical_database	<string> (readonly)	identifies physical database (BDE alias name) of underlying database and its native driver type
client_filename	<string> (readonly)	full path/filename of active client program
driver_filename	<string> (readonly)	full path/filename of driver DLL
spread_interval	"Y", "Q", "M", "W", "D", "H"	selects which date bin type should be reported in the *_SPREADS tables -- values are Year, Quarter, Month, Week, Day, and Hour (where available). Default is "M"
curr_id	<integer>	selects the view currency to be used for monetary field types (a valid curr_id value from the CURRTYPE table)

Examples:

SELECT engine_version FROM SYSOPTIONS

(shows the release number of the driver)

UPDATE SYSOPTIONS SET spread_interval = 'W'

(changes your date bin type to Weekly, as seen in all the _SPREADS tables)

OA Tables (return to Table of Contents)

The OA tables are read-only system tables that describe the SDK schema. These tables can be used in conjunction with the meta tables to access useful information about available tables, fields, and stored procedures exposed through the Primavera SDK.

Table Name	Description
OA_TABLES	Description of all the tables in the database.
OA_COLUMNS	Descriptions of columns for all the Tables in the database.
OA_TYPES	Data types supported by OPENRDA DAM Databases (needed in ODBC 1).
OA_INFO	Data Source information (needed in ODBC level 1).
OA_STATISTICS	Index information of all tables in the database.
OA_FKEYS	Foreign Key information of all tables in the database.
OA_PROC	Description of all the procedure names in the database.
OA_PROCCOLUMNS	Descriptions of input and output parameters and columns that make up result set for specified procedures.

Stored Procedures (return to Table of Contents)

A number of stored procedures are available via the Primavera SDK interface. These stored procedures are not related to the physical database -- they are logical procedures that operate at the level of business rules and are implemented in code, in the driver. They are provided primarily as shortcuts -- most of their functionality can be implemented by executing various SQL queries against the logical schema.

Note: Stored procedures in the Primavera SDK do not return values, nor

do they return result sets. These may be supported in the future.

Stored Procedure	Description
create_project(wbs_id, proj_short_name )	Inserts a new row into PROJECT and PROJWBS tables. The project is added under the specified EPS node (the wbs_id). Check last_assigned_key from SYSTABLES to get the newly assigned proj_id and wbs_id (see above).
copy_project(proj_id, copy_options)	proj_id is an integer that is not null. Copy_options is a string upto 512 characters can be null.
delete_project(proj_id )	Cascades to all related information including baselines.
create_eps(parent_wbs_id, wbs_short_name)	Parent_wbs is an integer that is not null. wbs_short_name is a string up to 20 characters that is not null. EPS is a superset of the WBS values.
delete_eps(proj_id)	If the EPS node has other child EPS nodes or projects under it, then the delete_eps stored proc fails. Users first have to delete the projects and other child EPS nodes before deleting the EPS node.
commit_changes	Updates the database on demand.
refresh	Refreshes the in memory table list.

copy_project parameters:

proj_id (Integer, Not Null)

copy_options (string, 512 chars, Nullable)

copy_options above is a string that contains all the options of what should and should not be copied (the default is False, or "don't copy").

A developer may use the following constants to concatenate and define the copy options:

Value	Descriptions
copy_document	Copies project assigned documents.
copy_riskthrs	Copies project assigned Risks and Thresholds.
copy_projissu	Copies project assigned Issues.
copy_reports	Copies project assigned Reports.
copy_ext_dependencies	Copies the external dependencies.

For example...

procedure MyProc()

var

sCopyOptions: String;

begin

sCopyOptions := “copy_document=Y;copy_riskthrs=Y;copy_projissu=N”;

end;

sCopyOptions above copies the Documents and Risks/Thresholds but does not copy Project Issues, as specified.

Reports and External Dependencies are not copied via the default value.

Uppercase values must be used to copy project data. Anything other than an uppercase Y results

in data not being copied.

Special Notes for Updating Foreign Keys (return to Table of Contents)

Foreign keys for certain tables cannot be updated via the Primavera SDK interface. The most common example is the proj_id field for a row that has a foreign key to the PROJECT table. Since changing the proj_id on a row (for example, a row in the TASK table) removes the task from one project and adds it into a different project, this operation involves more complex logic that is not invokable via the Primavera SDK. Some of these operations (moving items from one project to another) are supported by the Windows applications, but not via the Primavera SDK, since more contextual information is available when using the Windows products.

For example, when moving an activity from one project to another, the Windows product can detect exactly where in the WBS tree (or other groupings) you are placing the task and assign the new attributes accordingly. In the Primavera SDK, however, that context is not present, and the operation would have to make many assumptions about the programmer's intent. Accordingly, if you want to update certain foreign keys (like proj_id) via the Primavera SDK, it is recommended that you INSERT (make a new row) and then DELETE (remove the old row) instead.

Primavera SDK Error Codes (return to Table of Contents)

This table outlines the error codes returned by the Primavera SDK ODBC driver. Note that other error codes may be returned

by ODBC/OLE/COM from the driver under some conditions. All error conditions are returned with a detailed description string.

Error	Description
S_OK	0x00000000 // success
E_NOTIMPL	0x80004001 // function not implemented
E_FAIL	0x80004005 // low level failure
E_UNEXPECTED	0x8000FFFF // unexpected result
PMSDK_E_INTERNALERR	0x80040003 // internal driver error
PMSDK_E_NOCONNNECTION	0x80040010 // driver not connected, operation not allowed
PMSDK_E_BAD_LOGON	0x80040011 // username/password or license invalid
PMSDK_E_BAD_TABLENAME	0x80040020 // invalid table name
PMSDK_E_BAD_FIELDNAME	0x80040021 // invalid field/column name
PMSDK_E_BAD_PROCNAME	0x80040022 // invalid stored procedure name
PMSDK_E_NOTNULL	0x80040030 // missing required value for field
PMSDK_E_READONLY	0x80040031 // insert/update/delete on read-only value/row
PMSDK_E_PROCFAIL	0x80040032 // stored procedure returned error
PMSDK_E_NONNEGATIVE	0x80040033 // negative value not allowed
PMSDK_E_VALUERANGE	0x80040034 // value outside of valid range
PMSDK_E_FORMAT	0x80040035 // value has invalid format
PMSDK_E_INVALIDVAL	0x80040037 // value for this field is not a valid in context
PMSDK_E_NOTUNIQUE	0x80040036 // value must be unique

SELECTs, INSERTs, UPDATEs and DELETEs (return to Table of Contents)

Selects of Data - Selects from a table:

The table TASK contains several fields starting with task_id:

SQL> SELECT task_id,task_name FROM TASK

task_id task_name

===============

31773 Perform system integration and testing

31774 Prepare for system integration and testing

31778 Perform QA testing of system

13783 Install the system

Selects of Data - Selects from an extended table:

The table X_TASK provides extended activity information. Use X_TASK table to quickly get a list of tasks and determine the location of the task in the WBS, along with the resource assigned to the task.

SQL>SELECT task_id,PROJWBS__wbs_short_name,RSRC__rsrc_short_name FROM X_TASK

task_id PROJWBS__wbs_short_name RSRC__rsrc_short_name

=====================================================

31773 IT BMCA

31774 IT BMCA

31778 QA BMCA

13783 SI JSAN

Inserts and Assignment of Primary Keys:

In a typical physical SQL database, the client is responsible for assigning primary keys to new rows (INSERT). With the Primavera SDK, the client is not allowed to assign primary keys, since primary keys must go through the concurrency layer and not conflict with any other users on the system. Therefore, all INSERT statements to tables having a single primary key (see the schema doc for more information) should provide a primary key field with a value of zero. The actual primary key is assigned by the driver, and the client can query for it immediately after the INSERT call.

To determine the value of a primary key on a new row, use the SYSTABLES table and look for the corresponding last_assigned_key field.

Example:

SQL> INSERT INTO USERS (user_id, prof_id, actual_name) VALUES (0,12,'Bob Smith')

1 row inserted.

SQL> SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = 'USERS'

last_assigned_key

=================

188

The following table lists the fields required when inserting a row into a table:

Table Name	Required Fields
ACTVCODE	actv_code_type_id
BUDGCHNG	proj_id, wbs_id
CALENDAR	clndr_type
DOCUMENT	proj_id
EXTAPP	login_supply_flag, app_name,app_exe_name
ISSUHIST	proj_id,issue_id
PCATVAL	proj_catg_type_id
PROJCOST	task_id, cost_per_qty
PROJFUND	fund_id, proj_id
PROJISSU	proj_id,add_by_name
PROJPCAT	proj_catg_type_id, proj_catg_id
PROJRISK	proj_id
PROJWBS	proj_id
RCATVAL	rsrc_catg_type_id
RISKCTRL	proj_id, risk_id
RSRCRATE	rsrc_id
RSRCRCAT	rsrc_catg_type_id, rsrc_catg_id,rsrc_id
RSRCROLE	role_id,rsrc_id
TASK	wbs_id
TASKACTV	actv_code_type_id, actv_code_id
TASKDOC	proj_id, task_id, doc_id, wbs_id
TASKMEMO	task_id, memo_type_id, proj_id
TASKPRED	task_id, pred_task_id, pred_type
TASKPROC	task_id
TASKRSRC	task_id, rsrc_id
TSDATES	start_date, end_date
USEROBS	obs_id,user_id,prof_id
USERS	prof_id, actual_name
WBSBUDG	proj_id or wbs_id
WBSMEMO	proj_id, memo_type_id, wbs_id
WBSSTEP	proj_id, wbs_id, step_name

Updating Data

To modify an existing record, the SQL command Update can be used as follows:

SQL> UPDATE TASK SET task_name='Document Infrastructure' WHERE task_code = 'A1000'

When updating data, use the primary key to avoid accidentally changing data. Activity IDs should be unique within a project, but the Primavera SDK allows multiple projects to be opened. The following command ensures that only one record is updated:

SQL> UPDATE TASK SET task_name='Document Infrastructure' WHERE task_id = 31773

Deleting Data

To remove records from a table, use DELETE. For items that require many business rules to fire, a stored procedure is available that handles deletion of children and associated records to ensure data integrity. Refer to the following example of deleting with a specified wildcard:

SQL> DELETE FROM USERS WHERE user_id = 7896

Field Type/Data Type Mappings

The generated HTML tables contain a column called field type. The

following table lists the data types that map to all fields in the system.

Field Type	Data Type
Primary Key	Integer
Foreign Key	Integer
Sequence	Integer
Text	String
Currency	Double
Yes/No	String
HTML	Memo
Start Date	Date Type
Integer	Integer
Static	String
GUID	String
Duration	Double
Floating	Double
2 Decimals	Double
Rsrc Cost/Qty	Double
Finish Date	Date Type
Quantity	Double
Percent	Double
Variant	Double
Date Range	String
Price/Qty	Double

Special Note for Setting End-Date Fields via the SDK:

There is a special consideration when assigning end-date values that fall on day boundaries via the SDK. When assigning an end date value that falls exactly on a day boundary, use the date of the following day, and omit the time value or use midnight. This only applies when assigning a value to an end date field, which includes activity end date (TASK table) as well as end dates in the TASKRSRC, PROJWBS, PROJECT, and other tables.

For example, if a task starts on Jan 10th, Monday, and ends on Jan 12th, Wednesday, the task lasts for exactly three days. The end date should be "1/13/2001 00:00". This will be displayed in the GUI as "1/12/2000", since midnight of the following day is interpreted as ending on the previous day's date. If you are specifying a mid-day timestamp, there is no need to increment the end-date's day.

Files installed by the Primavera Software Development Kit (SDK)

(return to Table of Contents)

The ‘\Primavera Common\PMSDK’ contains several sub-folders:

‘\Primavera Common\PMSDK\Cache’ - (not used)

‘\Primavera Common\PMSDK\Doc’ - contains SDK HTML documentation files

index.html - root HTML help file

GeneralTables&Fields.htm - detailed listing of tables/fields exposed via the SDK

Programmer's Guide.htm - detailed documentation on using the SDK

SpreadTables&Fields.htm - detailed listing of spread tables

StaticFieldsDefinitions.htm - detailed listing of static values used in the SDK

ExtendedTables&Fields.htm - summary listing of Extended tables

SYSTABLES.htm - meta table in html format

SYSCOLUMNS.htm - meta table in html format

SYSOPTIONS.htm - meta table in html format

‘\Primavera Common\PMSDK\Driver’ - contains the Open Access (C++) driver

datatype.dbf - configuration database used by Open Access

drivinfo.dbf - configuration database for Data Access Manager

(DAM) for Open Access; parameters are extracted/set

in the oainfo.ini file.

EngineProperties.dll - The DLL which contains the UI for the DSN setup dialog

oadrd.ini - contains connection properties as they are created

oainfo.ini - Data Access Manager (DAM) settings used by Open Access

oaisql.lic - driver license file

OpenRDA.ini - Open Access server configuration file

PrmDPAPI.dll - Primavera ODBC Data Provider for WIN32 (main driver file)

‘\Primavera Common\PMSDK\Engines’ - contains the main engine file(s) and the SDK configuration file

PMSDK.dll - main engine file (dll)

PMSDK.ini - used to set delayedLoadMode

Working with the Primavera SDK (Examples) (return to Table of Contents)

Generic Examples (SQL) (return to Table of Contents)

When a connection is established with the Primavera SDK Data Source, the scope_level in the project table is set to 1 by default. This permits access to global data. Subsequently, as the scope_level is increased from 1 to 7, detailed data are loaded in memory. In addition to setting the scope_level, project data must be accessed with the appropriate access permissions. The access_level should be set to either 1 (shared mode) or 2 ( exclusive mode) to modify project-related data.

Creating an Enterprise Project Structure (EPS) node (return to Table of Contents)

To view EPS data, default scope_level of 1 is sufficient. An EPS node is always created under another EPS node. To create a lower-level EPS node, the wbs_id of another EPS node as the parent_wbs_id. The PROJWBS table flags each entry as a PROJECT, EPS, or WBS node in the node_type field.

Since the root EPS node does not have a parent, it can be accessed from the PROJWBS table using the following query:

SELECT wbs_id from PROJWBS WHERE parent_wbs_id IS NULL

To create an EPS node with a wbs_short_name of ‘Maintenance Projects’ under the root EPS use the create_eps() stored procedure:

CALL create_eps (436, ‘Maintenance Projects’)

The wbs_id for the newly created entry is an autogenerated field and can be obtained from SYSTABLES by querying the last_assigned_key field:

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = ‘PROJWBS’

Note: If another EPS node exists with the same wbs_short_name, a numerical value is appended

and stored as ‘Maintenance Projects-1’.

Creating a Project (return to Table of Contents)

The sample code below creates a Project with proj_short_name as ‘System Integration’. The proj_short_name is stored as a unique value for each entry in the PROJECT table. Project level data can be modified with a default scope_level.

The create_project() stored procedure also requires a wbs_id of a valid EPS node. The new project is created under the specified EPS node.

In the following example, 23454 is a valid EPS node with wbs_id = 23454.

CALL create_project(23454, 'System Integration')

Note: If another project exists with the same proj_short_name, a numerical value is appended

and stored as ‘System Integeration-1’.

To modify the default values, select the project using the following query. The proj_id can also be determined using the last_assigned_key from SYSTABLES.

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = ‘PROJECT’

SELECT proj_id FROM PROJECT WHERE proj_short_name = 'System Integration'

UPDATE PROJECT set proj_short_name = 'Sample Project' WHERE proj_id = 3564;

Note: Every project also has a corresponding entry in the PROJWBS table.

Creating a WBS node (return to Table of Contents)

A WBS node can be inserted directly in the PROJWBS table. However, a WBS node must be associated with a project. The example below assumes that a project called ‘System Integration’ with proj_id = 3564 exists in the database. Note that the appropriate scope_level is required to insert a WBS node.

INSERT INTO PROJWBS (wbs_id, proj_id) VALUES (0, 3564)

A WBS node associated with ‘System Integration’ is created above. SYSTABLES can then be queried to determine the wbs_id for the new entry as follows:

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = ‘PROJWBS’

(assume return value of 34832)

UPDATE PROJWBS set wbs_name = ‘IT Systems’ WHERE wbs_id = 34832

The update statement above modifies the wbs_name to ‘IT Systems’. Multiple WBS nodes can be associated with the same project.

Creating a Task (return to Table of Contents)

To add tasks to the TASK table associate them with a WBS node. Select the appropriate WBS node from the PROJWBS table as described below.

UPDATE PROJECT SET scope_level=7 WHERE proj_id = 3564

SELECT wbs_id FROM PROJWBS WHERE proj_id = 3564 AND wbs_short_name = ‘IT Systems’

The new task is added to ‘System Integration’ under the WBS node called IT Systems with a wbs_id = 4893. Since the wbs_short_name is unique within each project, the above query returns a unique value. Once the location of the task has been established by either selecting an existing WBS node or creating a new WBS node, the new task can be inserted in the TASK table. To access the data stored in the TASK table, the appropriate scope level needs to be set for the project as described.

UPDATE PROJECT set scope_level = 7 WHERE proj_id = 3564

INSERT INTO TASK (task_id, wbs_id) VALUES (0,4893)

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = ‘TASK’

(assume return value of 33682)

UPDATE TASK SET task_name = ‘Install Upgrades’ WHERE task_id = 33682

Once the task has been created, the task_id can be obtained from the SYSTABLES using the last_assigned_key value as described in the examples above.

Creating a Resource (return to Table of Contents)

To create a resource, add a new record to the RSRC table as follows. No parameters are required to insert the record in the RSRC table.

INSERT INTO RSRC (rsrc_id) VALUES (0)

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = ‘RSRC’

(assume return value of 2348)

UPDATE RSRC SET rsrc_name = ‘Bob Smith’ WHERE rsrc_id = ‘2348’

Creating a Resource Assignment (return to Table of Contents)

To create a resource assignment, add a record to the TASKRSRC table. A resource must be associated with an existing task and the scope_level required to create resource assignments must be set to 7. The sample SQL query below inserts a record in the TASKRSRC table and associates a resource with rsrc_id = 8695 and an activity with task_id = 75447. The newly created taskrsrc_id can be obtained from SYSTABLES using the last_assigned_key.

INSERT INTO TASKRSRC (task_id, rsrc_id) VALUES (75447, 8695)

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = ‘TASKRSRC’

Creating Task Dependencies (return to Table of Contents)

The SQL statements below assign a dependency Finish-to-Start between two tasks with task_id =75447 and 76474. The task with task_id =75447 is the predecessor task. To assign a relationship between these tasks, add a record to the TASKPRED table. The fields required to insert a record in TASKPRED table are task_id, pred_type.

The pred_type should be one of the following values:

‘PR_FS’ - Finish to Start

‘PR_SF‘ - Start to Finish

‘PR_SS’ - Start to Start

‘PR_FF’ - Finish to Finish

INSERT INTO TASKPRED (task_id, pred_task_id, pred_type) VALUES (75447, 76474, 'PR_FS')

Creating a Calendar (return to Table of Contents)

Add a record to the CALENDAR table to create a new calendar.

Calendars can be one of the following three types:

‘CA_Base’ - Global Calendar

‘CA_Rsrc’ - Resource Calendar

‘CA_Project’ - Project Calendar

To add a Global Calendar, the calendar type and an associated project must be specified when a record is inserted. The following code creates a Global Calendar and then finds the id of the created calendar.

INSERT INTO CALENDAR (clndr_type) values('CA_Base')

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = 'CALENDAR'

(assume return value of 2345)

The calendar name can be updated to ‘Development Calendar’ for a clndr_id = 2345 that is obtained from SYSTABLES as follows:

UPDATE CALENDAR SET clndr_name = 'Development Calendar' WHERE clndr_id = 2345

Resource and project calendars can also be created using the specified clndr_type. Each Project and Resource Calendar can inherit the properties of the Global Calendar. Update the base_clndr_id field with the preferred base calendar to reflect any changes as shown below.

INSERT INTO CALENDAR (clndr_type, proj_id) VALUES ('CA_Project', 3564)

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = 'CALENDAR'

(assume a return value of 2578)

UPDATE CALENDAR SET base_clndr_id = 2345 WHERE clndr_id = 2578

Similarly, a Resource Calendar can be created as described below. Once the calendar is created, obtain the clndr_id from SYSTABLES and update the fields as required.

INSERT INTO CALENDAR (clndr_type) VALUES ('CA_Rsrc')

Creating an Activity Code (return to Table of Contents)

When dealing with activity codes, you need to consider Activity Code Types and Activity Code Values. Each Activity Code Type may have several values associated with it. For example, a code for Project Phase may have the values Requirements and Design.

The table ACTVTYPE contains the activity code.

SELECT actv_code_type_id, proj_id, actv_short_len, actv_code_type FROM ACTVTYPE

actv_code_type_id proj_id actv_short_len actv_code_type

75 7 Location Code

76 307 7 Sub Contractor

Codes can be either global or project specific. For example, the ‘Location’ code entry above is global and the ‘Sub Contractor’ code entry above is specific to proj_id 307. The following queries can be used to create a project-level activity code:

INSERT INTO ACTVTYPE (actv_code_type_id, proj_id) VALUES (0, 3564)

A global activity code is created if an association to proj_id is removed. The actv_type_id can then be obtained from SYSTABLES.

The table ACTVCODE contains the possible values for a given activity code.

SELECT actv_code_id, actv_code_type_id, short_name, actv_code_name FROM ACTVCODE WHERE actv_code_type_id = 75

actv_code_id actv_code_type_id short_name actv_code_name

1200 75 ATLC Atlanta corporate center

1201 75 SLCE Salt Lake City engineering

1202 75 RTC Roanoke test center

1203 75 BDC Birmingham development

The table above shows the following valid code values for Location Code are ‘Atlanta corporate center’, ‘Salt Lake City engineering’, ‘Roanoke test center’, and ‘Birmingham development’. Activity code values can be assigned to an activity code by adding records to the ACTVCODE table as shown below. The value for actv_code_type_id is assumed to be 234.

INSERT INTO ACTVCODE (actv_code_id, actv_code_type_id) values (0,234)

The table TASKACTV contains the assignments of activity code values to tasks, as shown in the following query.

SELECT task_id, actv_code_type_id, actv_code_id, proj_id FROM TASKACTV

task_id actv_code_type_id actv_code_id proj_id

32568 75 1202 307

32569 75 1202 307

32570 75 1202 307

32571 75 1200 307

32572 75 1200 307

An activity code value can be assigned to a task by inserting the value in the TASKACTV table. The task_id = 75447 and actv_code_id = 1200 and actv_code_type_id = 75.

INSERT INTO TASKACTV (task_id, actv_code_id, actv_code_type_id) VALUES (75447, 1200, 75)

Creating a User (return to Table of Contents)

When creating new users, you must consider account users and user rights. The USERS table contains a listing of all users in the system. These are global data, so they require scope level 1.

To display a list of users in the system:

SELECT user_id, user_name FROM USERS

To add a user:

INSERT INTO USERS(user_id,prof_id,actual_name) VALUES (0,12,’Bob Smith’)

SELECT last_assigned_key FROM SYSTABLES WHERE api_table_name = 'USERS'

(assume a return value of 25)

To edit an existing user:

UPDATE USERS SET user_name = 'Bob' WHERE user_id = 25

To delete a user:

DELETE FROM USERS WHERE user_name = 'Bob'

Each user has a security privilege identified by the prof_id, which links the USERS table to the PROFILE table.

When creating new users in using the Primavera SDK, you can update the security profile of each inserted user.

A user must have a security profile.

SELECT user_id, prof_id, user_name FROM USERS WHERE user_name = 'admin'

user_id prof_id user_name

25 12 admin

This above select returns a prof_id of 12.

SELECT prof_id, prof_name FROM PROFILE WHERE prof_id = 12

prof_id prof_name

12 <Admin Superuser>

Activating Baselines for Loaded Project (return to Table of Contents)

When an SDK session begins, all projects’ baseline fields reflect the current plan. The user’s preferences are not used to activate any project baselines as in the GUI – this is the programmer’s responsibility.

To activate a baseline, first locate the desired baseline row in the PROJBASE table. Then, issue an update statement to activate that baseline. Note that the associated project should be loaded with scope_level=7 already.

UPDATE PROJBASE SET current_baseline_flag = ‘Y’ WHERE proj_id = 123

MS Access Example (return to Table of Contents)

1) In MS Access, go to File, New Database… and create a Blank Database

2) Go to File, Get External Data >, Link Tables…

3) Under ‘Files of type’ set to ‘ODBC Databases()’

4) Click the ‘Machine Data Source’ tab and select your Primavera SDK data source

5) If prompted to Login, login as a super-user (i.e., Admin/Admin):

6) Select desired tables to link to:

Note: In MS Access, you can link to a table even if you do not set the scope level accordingly. This will result in tables not displaying accurate information until the scope level is set to a correct level. To set the scope_level in Access you need to create an Update Query. The following is an example of an Update Query in Access that will set the scope_level for all projects to ‘7’ (SCOPE_LEVEL_ALL):

Note: When linking to any of the spread tables (see SpreadTables&Fields.htm) in MS Access, you will be prompted to ensure data integrity

by choosing fields that uniquely identify each record. For each of the spread tables you will select the first four fields.

For example, when linking to the spread table TASKRSRC_SPREADS you will need to select the fields:

taskrsrc_id, spread_type, start_date and normal format to allow MS Access to uniquely identify each

record in the table.

Visual Basic/ADO Example (return to Table of Contents)

ActiveX Data Objects (ADO) (return to Table of Contents)

Open Database Connectivity (ODBC) is a widely accepted Application Programming Interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.

OLEDB is Microsoft's strategic low-level interface to data across the organization. OLEDB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data.

ADO is designed as an easy-to-use application level interface to Microsoft's newest and most powerful data access paradigm, OLEDB. OLEDB provides "high-performance" access to any data source, including relational and non-relational databases, e-mail and file systems, text and graphics, custom business objects, and more. ADO is implemented with a small footprint, minimal network traffic in key Internet scenarios, and a minimal number of layers between the front-end and data source, to provide a lightweight, high-performance interface. ADO is easy to use because it is called using a familiar metaphor, the OLE Automation interface, available from just about any tool and language on the market today. And since ADO was designed to combine the best features of, and eventually replace RDO and DAO, it uses similar conventions with simplified semantics to make it easy to learn for today's developers.

ADO is advantageous since it has a very simple programming model. Below is a diagram showing the objects and how they relate to one another:

Summary of ADO Model:

The Connection Object

The Connection object represents a connection to the data source and allows you to execute commands. To execute any kind of command, you use the Execute method of the Connection object. If the command returns rows, a default Recordset object is created and returned. To specify a more complex Recordset, you create a new Recordset object, associate it with the Connection, and open the cursor.

The Command Object

The Command object represents a command (also known as a query or statement) that can be processed by the data source. Commands can return rows or not, and if the provider is capable, can also handle parameters. The Command object is actually optional in the ADO model because some data providers cannot supply command execution, but the object is supported if the provider supports commands.

Commands can be simple SQL statements (or some other language the data provider recognizes) or calls to stored procedures in the database. Commands can then be executed using the Command's Execute method, or you can create a Recordset object and associate it with the Command object when opening the cursor.

The Command object includes a collection of Parameter objects, which is described below. If the provider can support commands with parameters, the Parameters collection will contain one parameter object for each parameter in the command. As opposed to past models, you can create Parameter objects and explicitly add them to the Parameters collection, thus allowing you to use well-known parameterized commands to avoid the sometimes very expensive operation of having the provider populate the Parameters collection automatically based on the system catalog.

The Parameter Object

The Parameter object represents a parameter of a Command. As noted in the Command description, you can explicitly create Parameter objects and add them to the Parameters collection to avoid the often unnecessary and expensive task of going to the system catalog to automatically populate the parameter binding information.

The Recordset Object

The Recordset object is by far the most complex (the others are simplified considerably). This is not surprising, however, because all the cursor functionality is represented in this object. The Recordset object looks much like it does in existing models today, but a number of improvements have been made, such as removing unnecessary elements, adding optional arguments that reduce the number of lines of code for common scenarios, and changing defaults that didn't make sense in today's technologies.

The Field Object

The Field interface represents a column in a Recordset that you can use to obtain values, modify values, and learn about column meta-data. This object is almost identical to past models, but includes some new functionality.

The Error Object

The Error object represents an error returned from a data source. This object is actually optional because it is only needed when data sources can return multiple errors for a single method call. If a provider does not return multiple errors for a single function call, the provider just raises the error through the normal COM mechanisms that all COM servers do when called from languages like Visual Basic.

Sample Visual Basic/ADO code:

In Visual Basic, start a new project. In order to use ADO, you need to add a reference to

‘Mocrosoft ActiveX Data Objects Library. In your VB project go to ‘Project, References…’

and select the ADO library.

Sub Main()

Dim SDK As ADODB.Connection

Dim RecSet As ADODB.RecordSet

Dim lNumRecords As Long

‘1) create ADO connection object

Set SDK = New ADODB.Connection

‘2) establish connection

Call SDK.Open(<System Data Source Name>, <username>, <password>)

‘3) obtain a recordset of the contents of the PROJECT table

‘ where lNumRecords will return the number of records found

‘ and adCmdUnknown is an ADO constant that specifies that you are not

‘ specifically identifying the command you are

‘ about to execute

Set RecSet = SDK.Execute(“SELECT * FROM PROJECT”, lNumRecords, adCmdUnknown)

‘4) display the contents proj_id field value of each record

RecSet.MoveFirst

While Not (RecSet.EOF)

MsgBox RecSet.Fields(“PROJ_ID”).Value

RecSet.MoveNext

Wend

‘5) close/destroy the recordset

RecSet.Close

Set RecSet = Nothing

‘6) close/destroy the connection

SDK.Close

Set SDK = Nothing

End Sub

Lotus Notes Example (return to Table of Contents)

Here is a quick Lotus Notes demo on how to establish a connection

to a System DSN that can be used with the Primavera SDK:

1) In Notes, if you create a new empty database, expand the Design section, and double-click on Database Script...

2) In the (Options) event add the following include statement:

3) Then, switch to the Postopen event and add the following code:

4) Save and close the database.

5) Reopen the database and the following dialog will appear:

7) Enter the name of your SDK System DSN and it will let you know if it was able

to successfully establish a connection.

8) Once you have a connection object established you are off and running, executing SQL queries,

creating result sets, etc.

Microsoft Windows Scripting Host (WSH)/ADO Example (return to Table of Contents)

Microsoft^® Windows^® Script Host (WSH) is a language-independent scripting host for ActiveX^® scripting engines. It brings simple, powerful, and

flexible scripting to the Windows 32-bit platform, allowing you to run scripts from both the Windows desktop and the command prompt.

Windows Script Host is ideal for non-interactive scripting needs such as logon scripting, administrative scripting, and machine automation.

Windows Script Host offers the following benefits:

· Two separate executable files, WScript.exe and CScript.exe, for running scripts either from Windows or from the command prompt. WScript.exe provides a Windows-based properties page for setting script properties, while CScript.exe provides command-line switches for setting script properties.

· Support for multiple files. You can call multiple scripting engines and perform multiple jobs from a single Windows Script (.wsf) file.

· Very low memory requirements.

· Mapping of script extensions to programmatic identifiers (ProgIDs). When you start a script from the Windows desktop or the command prompt, the script host reads and passes the specified script file contents to the registered script engine. Instead of using the HTML SCRIPT tag to identify the script, the host uses file extensions; for example, .vbs for Microsoft Visual Basic^® Scripting Edition (VBScript) files, and .js for Microsoft JScript^® files. The use of extensions means you no longer need to be familiar with the ProgID for a given script engine. Windows Script Host handles this for you by maintaining a mapping of script extensions to ProgIDs, launching the appropriate engine for a given script.

Windows Script Host is integrated into Windows 98, Windows 2000 Professional, and Windows 2000 Server. Version 2.0 includes the VBScript and JScript scripting engines. In the future, other software companies may provide ActiveX scripting engines for languages such as PerlScript, TCL, REXX, and Python.

Sample WSH JScript/ADO code:

The following sample JScript code displays the current number of projects found in a

Primavera SDK data source connection:

//create ADO connection object...

var cn = new ActiveXObject("ADODB.Connection");

//open connection...

cn.Open(<System Data Source Name>, <username>, <password>);

var NumRecAff;

var adCmdUnknown = 8; //ado constant

//execute SQL command…

var rs = cn.execute("SELECT COUNT(*) FROM PROJWBS WHERE NODE_TYPE='PROJECT'",NumRecAff,adCmdUnknown);

WScript.echo("Project Count: " + rs.Fields.Item(0).Value);

rs.Close();

rs = null;

cn.Close();

cn = null;

</Script>

</Job>

</package>

[ Save the code above into a file called ‘getProjectCount.wsf’ ]

Double-click on the ‘getProjectCount.wsf’ file to get the output:

OR from the command line, enter the following:

>cscript //job:getProjectCount getProjectCount.wsf

Microsoft (R) Windows Script Host Version 5.1 for Windows

Project Count: 26

OR from the command line, enter the following:

>wscript //job:getProjectCount getProjectCount.wsf

Java Example (using JDBC-ODBC) (return to Table of Contents)

import java.sql.*;

class sdk_jdbc_odbc

{

public static void main (String[] args)

{

String url = "jdbc:odbc:<Data Source>";

String query = "SELECT PROJ_ID FROM PROJECT";

try

{

//load the jdbc-odbc bridge driver…

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//establish connection…

Connection con = DriverManager.getConnection(url,"<userName>","<password>");

//create a statement object so we can submit SQL statements to the driver

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(query);

printResultSet(rs);

rs.close();

stmt.close();

con.close();

}

catch (SQLException ex)

{

while (ex != null)

{

System.out.println("SQLException: " + ex.getMessage());

ex = ex.getNextException();

}

catch (java.lang.Exception ex)

{

ex.printStackTrace();

}

private static void printResultSet(ResultSet rs) throws SQLException

{

int numCols = rs.getMetaData().getColumnCount();

while (rs.next())

{

for (int i =1; i<=numCols; i++)

{

if (i == numCols)

System.out.print(rs.getString(i));

else

System.out.print(rs.getString(i) + ",");

}

System.out.println();

}

[ Save code above to a file called ‘sdk_jdbc_odbc.java’. ]

From the command line, enter the following to compile the .java file: >javac sdk_jdbc_odbc.java

From the command line, enter the following to execute the .class file: >java sdk_jdbc_odbc

Primavera SDK Log File(s) (return to Table of Contents)

The Primavera SDK automatically generates a log file in the TEMP folder called ‘PrmSDK.log’.

To determine the TEMP folder type ‘set’ at a command prompt:

Example:

>set

…

TEMP=C:\TEMP (for WinNT)

TEMP=C:\WINDOWS\TEMP (for Win95/98)

…

The Primavera SDK logs actions/methods that are performed by the application (VDB) to service a request. The log also provides the success/failure of the actions/methods performed, as well as any errors that may have taken place in processing a request.

Note: You can control the level of detail that is written to the log file using the ‘Options for logging messages’ setting found in the

‘Primavera Software Development Kit Setup’ dialog. The setting can be set to terse, verbose, or none.

Sample terse (less detail) output (establishing a connection):

010313:18:41:56.361 - VDB.Connect: da=PMSDK_PE;Driver=PrimaveraPMSDK;engine=P3e;lf=C:\temp\PMSDK.LOG;lo=terse;pg=1;pp=pubuser;pu=pubuser; as "admin" successful

Sample verbose (more detail) output (establishing a connection):

010313:18:44:52.865 - VDB.Connect: "admin" using params=da=PMSDK_PE;Driver=PrimaveraPMSDK;engine=P3e;lf=C:\temp\PMSDK.LOG;lo=verbose;pg=1;pp=pubuser;pu=pubuser;

010313:18:44:54.737 - USESSION - GETNEWKEY

010313:18:44:54.808 - SELECT * from users WHERE user_id=25

010313:18:44:54.818 - SELECT * from profile WHERE prof_id=12

010313:18:44:54.828 -

010313:18:44:54.828 - REFRESHING - All Tables

010313:18:44:54.828 - REFRESHING - PREFER

010313:18:44:54.828 - SQL - SELECT * FROM prefer ORDER BY prefer_id

…

etc.

Sample verbose output (establishing a connection with an invalid password):

010313:18:47:49.278 - VDB.Connect: "admin" using params=da=PMSDK_PE;Driver=PrimaveraPMSDK;engine=P3e;lf=C:\temp\PMSDK.LOG;lo=verbose;pg=1;pp=pubuser;pu=pubuser;

010313:18:47:50.771 - Logon failed: invalid password

010313:18:47:50.771 - ERROR => Logon failed: invalid password

VDB.Connect; hResult=0x80040011 (PMSDK_E_BAD_LOGON: username/password or license invalid); EOleSysError=SDK failed to logon to driver

010313:18:47:50.791 - VDB.Disconnect

SQL (aggregate/scalar) functions supported by the Primavera SDK/OA

(return to Table of Contents)

Aggregate functions supported

<set-function-spec> ::= COUNT ( * )

| AVG ( [ALL | DISTINCT] < value-expression > )

| MIN ( [ALL | DISTINCT] < value-expression > )

| MAX ( [ALL | DISTINCT] < value-expression > )

| SUM ( [ALL | DISTINCT] < value-expression > )

| COUNT ( [ALL | DISTINCT] < value-expression > )

Examples:

SELECT AVG(cost_per_qty) FROM RSRC

SELECT MIN(cost_per_qty) FROM RSRC

SELECT MAX(cost_per_qty) FROM RSRC

SELECT SUM(cost_per_qty) FROM RSRC

SELECT COUNT(*) FROM RSRC

Scalar functions supported:

Summary:

Category:	Functions:
String	ascii, char, concat, lcase, lower, left, length, locate, ltrim, repeat, rtrim, ucase, upper
Date/time	curdate, curtime, dayname, dayofmonth, hour, minute, month, monthname, now, second, year
Numeric	mod
System	user, database

Detail:

Function/Syntax:	Description:	Sample SQL:	Sample Output:
String
ASCII(string_exp)	Returns the ASCII code value of the leftmost character of string_exp as an integer.	select rsrc_name from rsrc where ascii(rsrc_name)=67	rsrc_name Capital Projects Construction Department Customer Relations
CHAR(code)	Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255; otherwise, the return value is data source–dependent.	select char(65),rsrc_name from rsrc	Col1 rsrc_name A Capital Projects A Construction Department A Operating Engineer
CONCAT(string_exp1, string_exp2)	Returns a character string that is the result of concatenating string_exp2 to string_exp1. The resulting string is DBMS-dependent. For example, if the column represented by string_exp1 contained a NULL value, DB2 would return NULL but SQL Server would return the non-NULL string.	select concat(rsrc_id,rsrc_name) from rsrc	Col1 1Capital Projects 1159Construction Department 1160Operating Engineer
LCASE(string_exp) LOWER(string_exp)	Returns a string equal to that in string_exp, with all uppercase characters converted to lowercase.	select lcase(rsrc_name) from rsrc select lower(rsrc_name) from rsrc	Col1 capital projects construction department operating engineer
LEFT(string_exp, count)	Returns the leftmost count characters of string_exp.	select left(rsrc_name,2) from rsrc	Col1 Ca Co Op
LENGTH(string_exp)	Returns the number of characters in string_exp, excluding trailing blanks.	select length(rsrc_name) from rsrc	Col1 16 23 18
LOCATE(string_exp1, string_exp2[, start])	Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first character position in string_exp2 unless the optional argument, start, is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.	select locate('a',rsrc_name,1) from rsrc	Col1 2 17 5
LTRIM(string_exp)	Returns the characters of sring_exp, with leading blanks removed.
REPEAT(string_exp, count)	Returns a character string composed of string_exp repeated count times.	select repeat('x',10) from <table>	Col1 xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx
RTRIM(string_exp)	Returns the characters of string_exp with trailing blanks removed.
UCASE(string_exp) UPPER(string_exp)	Returns a string equal to that in string_exp, with all lowercase characters converted to uppercase.	select ucase(rsrc_name) from rsrc select upper(rsrc_name) from rsrc	Col1 CAPITAL PROJECTS CONSTRUCTION DEPARTMENT OPERATING ENGINEER

Function/Syntax:	Description:	Sample SQL:	Sample Output:
Date/Time
CURDATE( )	Returns the current date.	select concat(rsrc_name,curdate()) from rsrc	Col1 Capital Projects2000-12-12 Construction Department2000-12-12 Operating Engineer2000-12-12
CURTIME( )	Returns the current local time.	select concat(rsrc_name,curtime()) from rsrc	Col1 Capital Projects11:15:00 Construction Department11:15:00 Operating Engineer11:15:00
DAYNAME(date_exp)	Returns a character string containing the data source–specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English, or Sonntag through Samstag for a data source that uses German) for the day portion of date_exp.	select dayname(plan_start_date) from project	Col1 Thursday Friday Saturday Friday Monday
DAYOFMONTH(date_exp)	Returns the day of the month based on the month field in date_exp as an integer value in the range of 1–31.	select dayofmonth(plan_start_date) from project	Col1 19 2 7 4
HOUR(time_exp)	Returns the hour based on the hour field in time_exp as an integer value in the range of 0–23.	select hour(target_start_date) from task	Col1 8 8 11 10
MINUTE(time_exp)	Returns the minute based on the minute field in time_exp as an integer value in the range of 0–59.	select minute(target_start_date) from task	Col1 0 0 12
MONTH(date_exp)	Returns the month based on the month field in date_exp as an integer value in the range of 1–12.	select month(target_start_date) from task	Col1 7 9 9 12
MONTHNAME(date_exp)	Returns a character string containing the data source–specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English, or Januar through Dezember for a data source that uses German) for the month portion of date_exp.	select monthname(target_start_date) from task	Col1 July August August September
NOW( )	Returns current date and time as a timestamp value.	select concat(rsrc_name,now()) from rsrc	Col1 Capital Projects2000-12-12 11:45:12 Construction Department2000-12-12 11:45:12
SECOND(time_exp)	Returns the second based on the second field in time_exp as an integer value in the range of 0–59.	select second(target_start_date) from task	Col1 0 0 0
YEAR(date_exp)	Returns the year based on the year field in date_exp as an integer value. The range is data source–dependent.	select year(target_start_date) from task	Col1 1999 2000

Function/Syntax:

Description:

Sample SQL:

Sample Output:

Numeric

MOD(integer_exp1, integer_exp2)

Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.

select mod(task_id,10) from task

Col1

Function/Syntax:	Description:	Sample SQL:	Sample Output:
System
DATABASE( )	Returns the name of the database corresponding to the connection handle. (The name of the database is also available by calling SQLGetConnectOption with the SQL_CURRENT_QUALIFIER connection option.)	select database() from <table>	Col1 da=PMSDK;Driver= PrimaveraPMSDK; engine=P3e; lf=C:\temp\PMSDK.LOG; lo=terse;pg=1;pp=pubuser; pu=pubuser;
USER( )	Returns the user name in the DBMS. (The user name is also available by way of SQLGetInfo by specifying the information type: SQL_USER_NAME.) This can be different than the login name.	select user() from <table>	Col1 admin

FAQ (return to Table of Contents)

What is the SDK?

see Overview section.

What is the Universal Framework?

The Universal Framework exposes the appropriate interfaces and connects to the core VDB logic. Since there will be disparate languages and models between the VDB and UF, an encapsulated interface paradigm is required to handle the different memory layout and allocation strategies used by Object Pascal, C++, etc.

What is the preferred means of accessing data via the SDK?

ADO is the preferred method. see ADO section.

Is there a preferred language for using the SDK?

Any programming language that can use ADO is fair game.

Is there an Object Model for the SDK?

The SDK is essentially used by applying appropriate SQL statements to the exposed tables. The Object Model in the case of the SDK is essentially the logical schema of the exposed logical tables. The SDK is a layer that resides over the VDB via the Universal framework. The VDB validates business rules for the SDK.

Can more than one DSN (Data Source Name) exist for the SDK?

Yes. You can create as many System DSNs as you like.

How is the user name and password configured?

Through the Project Management product. The username and password are the same as the application user.

If you execute a SQL statement such as 'DELETE FROM TASK WHERE TASK_ID = 33210' what occurs? That record is removed from the TASK table, but are any other references to that task removed from other tables as well (i.e., TASKRSRC which stores resources assigned to that activity)? Can you explain how this takes place?

All DELETE statements cascade to outer tables. Outer tables means rows in other tables which are dependent upon the row being deleted will be deleted along with the ‘master’ row. In the case of deleting a TASK row, all associated TASKRSRC rows would be deleted, as well. Additionally, all associated TASKPRED, RSRCHOUR, TASKMEMO, etc. rows would be deleted. The schema documentation should reflect these relationships among tables.

What files are required for the SDK?

see Files Installed section.

Where can I get the latest version of ADO?

ADO is part of Microsoft's Data Access Components (MDAC). You can download the latest version of MDAC from Microsoft's Data Web site. http://www.microsoft.com/data/

How do I know which version of MDAC I have installed on my computer?

The only way to know the version is to look at the ‘msdadc.dll’ file version.

How do I determine (through code) what version of MDAC is installed on a computer?

1) Using the ProgID "ADODB.Connection", get it's CLSID from the system registry.

e.g. HKEY_CLASSES_ROOT\ADODB.Connection\CLSID

=> {00000514-0000-0010-8000-00AA006D2EA4}

2) Using the CLSID, get the path under "HKEY_CLASSES_ROOT\CLSID\<CLSID>\InprocServer32" from the system registry.

e.g. HKEY_CLASSES_ROOT\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\InprocServer32

=> C:\Program Files\Common Files\System\ADO\msado15.dll

3) Get the internal file version of the DLL.

e.g. C:\Program Files\Common Files\System\ADO\msado15.dll

=> 2.10.4202.1

Troubleshooting (return to Table of Contents)

Installation

In order to install the Primavera SDK on WinNT/2000, you need to log in to the PC as an Administrator.

If you are not logged in to the PC as an Administrator you will receive the following message:

Connection

1) Make sure that you are using a System DSN. see ODBC Data Source Admin section.

2) Make sure the Database Alias (BDE) that the System DSN is set to is valid.

Tip: Download the following from http://www.inprise.com/devsupport/bde/utilities.html

Database connection checker (Size 136,482 bytes)

http://www.inprise.com/devsupport/bde/files/conect32.zip

This program will allow you to check connections to SQL, ODBC, and Standard databases;

also verifies that tables can be opened by BDE32.

3) Make sure you are logging into the SDK as an <Admin Superuser>.

4) Make sure you can log into the GUI application (if installed).

5) Make sure you can log directly into the physical database:

Oracle - use SQL+

MS SQL - use Query Analyzer

Interbase - use BDE Administrator

6) Make sure the appropriate physical database client is installed.

7) Make sure the appropriate database client service is running.

8) Make sure you can ‘ping’ the database server.

Ping verifies connections to a remote computer or computers. This command is available only if the TCP/IP protocol has been installed.

>ping <server name>

Data Access

Most data access issues involve not having the appropriate scope_level and/or access_level set for a project.

See the sections on scope level and access level.

Performance

Avoid using SQL ‘SELECT * FROM <table>’ statements to improve performance. Use more efficient SQL statements like

‘SELECT task_id, task_code FROM TASK’ to improve performance when using the Primavera SDK.

When working with very large data sets you may (potentially) improve performance by increasing the ‘CacheMemSize’ setting in the OpenRDA.INI file.

Documentation/URL Sources (return to Table of Contents)

Primavera: http://www.primavera.com/

Primavera SDK installed documentation: ‘\Primavera Common\PMSDK\Doc’

Borland: http://www.inprise.com/

MDAC: http://www.microsoft.com/data/

Microsoft ActiveX Data Objects (ADO): http://www.microsoft.com/data/ado/default.htm

Microsoft Windows Scripting Technologies: http://msdn.microsoft.com/scripting/

Java: http://java.sun.com/

MSDN: http://msdn.microsoft.com/default.asp

Open Access: http://www.atinet.com/