Customization Suite - ODBC

(1)

(2)

(3)

Registered Trademarks and Proprietary Names

Product names mentioned in this document may be trademarks or registered trademarks of Maximizer Software Inc. or other hardware, software, or service providers and are used herein for identification purposes only.

Applicability

This document applies to Maximizer CRM 10.5 and Maximizer CRM 10.5 Customization Suite software.

(4)

Corporate Headquarters Americas

Maximizer Software Inc.

1090 West Pender Street – 10th Floor Vancouver, BC, Canada V6E 2N7 +1 604 601 8000 phone +1 604 601 8001 fax +1 888 745 4645 support [email protected] www.maximizer.com

Knowledge Base: www.maximizer.com/knowledgebase

Europe, Middle East, and Africa

Maximizer Software Ltd

2nd Floor - Apex House, London Road Bracknell, Berkshire RG12 2XH United Kingdom +44-1344-766900 phone +44-1344-766901 fax [email protected] www.maximizer.co.uk Asia

Maximizer Asia Limited 17/F, Regent Centre 88 Queen's Road Central, Hong Kong +(852) 2598 2888 phone +(852) 2598 2000 fax [email protected] www.maximizer.com.hk

Australia

Maximizer Software Solutions Pty. Ltd. Level 10, 815 Pacific Highway

Chatswood, New South Wales Australia, 2067

+61 (0)2 9957 2011 phone +61 (0)2 9957 2711 fax [email protected] www.maximizer.com.au

(5)

Combined_CSCase_User_Fields Table...106 CSCases Table...107 Opportunity Tables...111 Activity Table ...115 Combined_Opp_User_Fields Table ...117 Currency Table...118 OppActivity Table...119 OppComp Table...123 OppContact Table ...125 Opportunity Table...127 OppPartners Table...131 OppRole Table ...133 OppStep Table ...135 OppSuccessFactor Table ...137 Prob_Close_Range Table...138 Reasons Table ...140

Strategy Library Tables...141

Role Table ...142 Role_Standing_Link Table...143 Standing_For_Role Table ...144 Standing_For_Success_Factor Table ...146 Step Table ...147 Step_Activity_Link Table ...149 Strategy Table...151 Strategy_Role_Link Table...152 Strategy_Success_Factor_Link Table...155 Strategy_Step_Link Table...156 Success_Factor Table ...159 Success_Factor_Standing_Link Table...160

User and Group Tables ...162

Security_Group Table ...162

Team Table...163

User_Details Table ...165

Third-Party Synchronization Tables...168

(8)

Custom_Child Table...172 Index ...179

(9)

1

C

H A P T E R

1

Introduction

In this chapter...

“Who Should Use This Guide?” on page 2 “Related Documentation” on page 2

(10)

This guide explains how to use ODBC in the Maximizer environment. It is a reference to the Maximizer database structure. It describes security that the Maximizer ODBC provides at the database, table, record, and field level. In addition the section shows the relationships between Maximizer data tables. It also describes each Maximizer data table, the fields within each table and the table indexes.

Who Should Use This Guide?

This guide is intended for business partners (value-added resellers) and developers.

2

C

H A P T E R

2

Maximizer ODBC:

Overview

In this chapter...

“Using Maximizer ODBC” on page 7 “Access Control and Security” on page 10

(14)

Open Database Connectivity (ODBC) is an open programming interface that you can use to access data that is stored on database management systems (DBMS).

Maximizer ODBC is a translator that allows you to read and write Maximizer data from ODBC-compliant applications. It also lets you access Maximizer databases for reporting and analysis. Typical applications you can use for these purposes include spreadsheets (for example, Excel and Lotus 1-2-3), report writers (for example, Crystal Reports and Impromptu), and executive information systems.

On the basis of its read/write capabilities alone, the ODBC Driver extends the reach of your other applications and increases the usability and value of your Maximizer data. But the Maximizer ODBC Driver is much more than a basic driver. To begin, it uses validation rules to ensure the integrity of your Maximizer data—which is beyond the scope of a basic ODBC driver. It also provides excellent security at the database, table, record, and field levels to ensure that access to data is tightly controlled according to assigned user rights. In addition, the Customization Suite Guides describe the structure and contents of each Maximizer data table, illustrate the links between tables, provide tips for efficient use of the driver, and contain a thorough programmer’s reference. They enable you to optimize your use of the driver and save valuable development time.

(15)

Using Maximizer ODBC

Now that you have installed the driver, you are almost ready to access the data contained in Maximizer databases. Before you can do this, however, you must know how to define a data source. By defining a data source, you create a link between Maximizer and a database that you have selected.

And since you are going to open Maximizer’s database from within another application, you must also consult the user’s guide that comes with that third-party application to determine how to use the program to access the ODBC database. When using ODBC with Address Books that do not have security enabled, you may optionally set the system login registry key as instructed below to suppress the login dialog box. This process should be used only for Address Book folders that do not have security enabled—doing otherwise may result in ODBC connection errors.

➤ To set the registry key for Address Books not using security



Open the Windows Registry Editor. The Registry Editor is an advanced tool that

enables you to change settings in your system Registry. To do this:

• Click the Windows Start button to display the Start menu. • Select Run, type REGEDIT and click OK.



Go to the branch:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\Maximizer ODBC Driver.



Select New > String Value and enter SystemLogin.



Double-click on the string and enter the value of False. This suppresses the default Maximizer ODBC login dialog box.

(16)

Defining a Data Source

A data source is the connection you make between the Maximizer ODBC Driver and a Maximizer database. You need to establish a connection in order to access the data in a given Maximizer database.

You must define a data source for each database you want to access. Once you have defined a data source, however, the link between the Maximizer ODBC Driver and that database is permanent.

➤ To define a data source



Open Data Sources (ODBC) from the Administrative Tools menu. You can access Administrative Tools through either the Control Panel or the Windows Start menu.

The ODBC Data Source Administrator window appears.



Click the System DSN tab.



Click the Add button.

The Create New Data Source window appears.



Select Maximizer ODBC Driver and click Finish.

The ODBC Maximizer Driver Setup window appears.



From the drop-down list, select the name of the Maximizer database you want to access.



Click the Options>> button, and select the following options as required: • UDF Table Items Separation Char: Defines the character used to separate

the UDF table item

• TRUE Date: With the default setting not checked, Maximizer date/time field

information is retrieved as a String data type. However, if the TRUE Date option is checked, then Maximizer returns date/time fields as Date data type. The format for both the String and Date data types is determined by the short Date style and by the Date separator setting, set into the regional setting. • UDF Tables Shown: With the default setting checked, Maximizer ODBC will

include all UDF Tables in a Table Def type record set. If the UDF Tables Shown option is not selected, then all UDF Tables are not available through

Maximizer ODBC.

• MR_MS: With the default setting not checked, the Mister/Miss field name

appears as MR/MS. For systems that don't accept the '/' as a valid character for a field name, check the MR_MS options to have the Mister/Miss field name appear as MR_MS."



Close the Maximizer ODBC Driver Setup dialog box and the ODBC Data Source Administrator.

(17)

Using ODBC-Compliant Software

You are now ready to use ODBC-compliant applications to access data stored in Maximizer databases. However, each ODBC-compliant application has its own set of commands for using an ODBC driver. Therefore, you need to consult the user’s guide for the third-party application you are using for information about how to access data in an external relational database through an ODBC driver.

Note

You must enter a User ID to access Maximizer’s ODBC database even if Maximizer security is not enabled. The default User ID is MASTER; the default password is CONTROL. If Maximizer security is enabled, then enter your User ID as you normally would in Maximizer.

Note

When linking to an external data source in Microsoft Access, you must specify a field or combination of fields as a unique index for each linked table.

For the majority of tables, you can use the Data_Machine_Id and Sequence_Number fields as the unique index fields when linking to Maximizer ODBC driver data tables from Microsoft Access. The Team table is one exception because you must specify the first four fields as unique index fields: Db_Id, Rec_Type, Team_Id, and Team_Member_Id. The Maximizer ODBC Driver Data Tables chapter includes a list of index definitions for each table, and you can use any of the unique indexes to link to the tables from Microsoft Access.

(18)

Access Control and Security

The Maximizer ODBC Driver uses the same access control that is set up in Maximizer’s Security tab, which is accessible from File > Preferences in Maximizer. The security is implemented at four levels:

• database level • table level • record level • field level

Each of these levels are described in this chapter.

➤ To access the Maximizer ODBC Driver



Select Start > Settings > Control Panel > Administrative Tools > Data

Sources (ODBC).



Click the Add button.



Select Maximizer ODBC Driver from the list, and click Finish.



Select the Address Book to use as a data source, and click OK.

Database Level

Only the list of databases available to Maximizer is present when ODBC data sources are being set up. To gain access to the data source, the user must log in as a valid Maximizer user and supply the correct password.

Table Level

Only those tables that are set to be readable in Maximizer are visible to the user. If the user has no Read rights, then a table cannot be attached or opened.

(19)

Record Level

Depending on the access rights of the Maximizer user, only certain records are returned. For example, no private Address Book entries and associated tasks and appointments are shown if the “Open other user’s private entries” checkbox is not selected in the Maximizer’s Security Tab. The user can see OppActivity, OppStep, OppSuccessFactor, OppRole, and OppComp records only if they are a team member of an Opportunity. Also, only the Opportunity leader can delete an Opportunity record once it has been created.

Field Level

Certain fields may be protected depending on the user or group rights. For example, a user who is not a team member of an opportunity cannot modify the opportunity’s Objective field.

Note

(20)

(21)

3

C

H A P T E R

3

Maximizer ODBC:

Programmer’s Reference

In this chapter...

“Maximizer ODBC Limits” on page 14 “ODBC API Conformance” on page 15

“Notes on the ODBC API Conformance Table” on page 17 “SQL Conformance” on page 24

“Scalar Functions” on page 30 “Data Types” on page 32

“Other Characteristics” on page 35

“Extensions to the ODBC GROUP BY Syntax” on page 37 “Left Outer Join” on page 39

(22)

The material in this chapter describes the characteristics of the Maximizer ODBC Driver, including

• operating system platforms supported by the Maximizer ODBC • summary of Maximizer ODBC limits

• ODBC API conformance of the Maximizer ODBC to ODBC API • SQL conformance of the Maximizer ODBC

• scalar functions supported by the Maximizer ODBC • data types supported by the Maximizer ODBC • other characteristics of the Maximizer ODBC

For detailed information on the ODBC API, SQL grammar, scalar functions, and SQL data types, see Microsoft’s ODBC 2.0 Programmer’s Reference and SDK Guide. Maximizer ODBC operates on Windows 95, Windows 98, Window NT 4.0, and Windows 2000.

Maximizer ODBC Limits

The following limits apply to Maximizer ODBC: • Number of rows: 2 billion

• Number of columns in a query: 255 • Size of a column: 2 gigabytes

• Number of connections: limited by memory

• Number of statements per connection: limited by memory • Table name length: 64 characters

• Column name length: 64 characters • Database qualifier: 68 characters

(23)

ODBC API Conformance

Maximizer ODBC conforms to the ODBC Level 1 API. The following table lists the ODBC API functions that Maximizer ODBC supports.

Table 1 ODBC API Functions Supported by Maximizer ODBC Function Conformance Level

SQLAllocEnv Core SQLAllocEnv Core SQLAllocStmt Core SQLBindCol Core SQLCancel Core SQLColAttributes Core SQLColumns Level 1 SQLConnect Core SQLDataSources Level 2 SQLDescribeCol Core SQLDisconnect Core SQLDriverConnect Level 1 SQLError Core SQLExecDirect Core SQLExecute Core SQLFetch Core SQLFreeConnect Core SQLFreeEnv Core SQLFreeStmt Core SQLGetConnectOption Level 1 SQLGetCursorName Core SQLGetData Level 1 SQLGetFunctions Level 1 SQLGetInfo Level 1 SQLGetStmtOption Level 1 SQLGetTypeInfo Level 1

(24)

SQLMoreResults Level 2 SQLNumResultCols Core SQLParamData Level 1 SQLPrepare Core SQLPutData Level 1 SQLRowCount Core SQLSetConnectOption Level 1 SQLSetCursorName Core SQLSetParam Core SQLSetStmtOption Level 1 SQLSpecialColumns Level 1 SQLStatistics Level 1 SQLTables Level 1 SQLTransact Core

Table 1 ODBC API Functions Supported by Maximizer (Continued) ODBC Function Conformance Level

(25)

Notes on the ODBC API Conformance Table

SQLMoreResults

Maximizer ODBC always returns SQL_NO_DATA_FOUND for this function. (Maximizer ODBC supports this function, with its return value, because of Microsoft Access requirements.)

SQLTransact

Maximizer ODBC does not support transactions or ROLLBACK. It supports

AUTOCOMMIT in that all data written is committed automatically. SQLTransact always returns SQL_SUCCESS when fType is SQL_COMMIT and always returns SQL_ERROR when fType is SQL_ROLLBACK.

SQLSetStmtOption / SQLGetStmtOption

The following table describes the options that Maximizer ODBC supports for SQLSetStmtOption and SQLGetStmtOption.

Table 2 Option Supported for SQLSetStmtOption / SQLGetStmtOption fOption vParam Comments

SQL-MAX-LENGTH See Microsoft’s ODBC SDK

Programmer’s Reference.

SQL-NOSCAN See Microsoft’s ODBC SDK

Programmer’s Reference.

SQL-MAX-ROWS See Microsoft’s ODBC SDK

(26)

SQLSetConnectOption / SQLGetConnectOption

The following table describes the options that Maximizer ODBC supports for SQLSetConnectOption and SQLGetConnectOption.

SQLGetTypeInfo

SQLGetTypeInfo generates a list of database-specific data type names (“TYPE_NAME”) specified by Maximizer ODBC.

SQLGetInfo

The following table lists the values returned by Maximizer ODBC for the SQLGetInfo options.

Table 3 Option Supported for SQLSetConnectOption / SQL GetConnectOption fOption vParam Comments

SQL-ACCESS-MODE See Microsoft’s ODBC SDK Programmer’s

Reference.

SQL_AUTOCOMMIT Valid with SQLGetConnectOption but

not valid with SQLSetConnectOption since SQL_AUTOCOMMIT is always on. SQL_CURRENT_QUALIFIER

(65000)

Valid with SQLGetConnectOption. Returns the current table qualifier. To set a table qualifier, use the “USE”

statement.

Table 4 Values Returned for SQLGetInfo Options fInfoType Return Values

SQL_ACCESSIBLE_PROCEDURES “N” for No. SQL_ACCESSIBLE_TABLES “N” for No.

SQL_ACTIVE_CONNECTIONS 0 to indicate no limit. SQL_ACTIVE_STATEMENTS 0 to indicate no limit.

SQL_CONCAT_NULL_BEHAVIOR 1 to indicate that the result is a concatenation of non-NULL valued columns.

SQL_CONVERT_BIGINT 0 for conversion not supported. SQL_CONVERT_BINARY 0 for conversion not supported.

(27)

SQL_CONVERT_BIT Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

ODBC 2.0 Programmer’s Reference and SDK Guide.

SQL_CONVERT_CHAR Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_DATE Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_DECIMAL Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_DOUBLE Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_FLOAT Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_FUNCTIONS 1 for conversion supported.

SQL_CONVERT_INTEGER Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_LONGVARBINAR Y

0 for conversion not supported.

Table 4 Values Returned for SQLGetInfo Options (Continued) fInfoType Return Values

(28)

SQL_CONVERT_LONGVARCHAR Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_NUMERIC Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_REAL Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_SMALLINT Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_TIME Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_TIMESTAMP Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_TINYINT Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

SQL_CONVERT_VARBINARY 0 for conversion not supported.

SQL_CONVERT_VARCHAR Depends on data types supported by Maximizer ODBC. Will be a subset of SQL data type conversions listed in Appendix D of Microsoft’s

(29)

SQL_CORRELATION_NAME (65000)

2 to indicate that any correlation name can be used.

SQL_CURSOR_COMMIT_ BEHAVIOR

2 to indicate that cursors are preserved in the same position as before the COMMIT operation and that the application can execute or fetch without preparing the statement again. SQL_CURSOR_ROLLBACK_

BEHAVIOR

0 to indicate that cursors are closed and deleted and that the application must prepare and execute the next statement.

SQL_DATA_SOURCE_NAME Long pointer to the name of the underlying data source.

SQL_DATA_SOURCE_READ_ONL Y

“N” for No.

SQL_DATABASE_NAME Long pointer to the current table qualifier. SQL_DBMS_NAME Long pointer to the DBMS name character string

supplied by Maximizer ODBC (e.g., “MAXWIN”). SQL_DBMS_VER Long pointer to the version character string

supplied by Maximizer ODBC (e.g., “1.20”). SQL_DEFAULT_TXN_ISOLATION 0 to indicate that transactions are not supported. SQL_Driver_HDBC Handled by the Driver Manager (ODBC.DLL). SQL_Driver_HENV Handled by the Driver Manager (ODBC.DLL). SQL_Driver_HSTMT Handled by the Driver Manager (ODBC.DLL). SQL_Driver_NAME Long pointer to the name of Maximizer ODBC. SQL_Driver_VER Long pointer to the version of Maximizer ODBC in

the form “nn.nn.nnnn” (e.g., “1.00.2117”). SQL_EXPRESSIONS_IN_ORDERBY “Y” for Yes.

SQL_FETCH_DIRECTION SQL_FD_FETCH_NEXT.

SQL_FILE_USAGE (65002) The returned value is a 16-bit integer that indicates how the driver directly treats files in a data source. A value of 0

(SQL_FILE_NOT_SUPPORTED) indicates that the driver is not a single-tier driver. A value of 1 (SQL_FILE_TABLE) indicates a single-tier driver that treats files in a data source as tables. A value of 3 (SQL_FILE_QUALIFIER) indicates a single-tier driver that treats files in a data source as a qualifier.

(30)

SQL_IDENTIFIER_CASE 4 to indicate that identifiers are mixed case (i.e., not case sensitive).

SQL_IDENTIFIER_QUOTE_CHAR Long pointer to the double-quote character (“). SQL_MAX_COLUMN_NAME_LEN 64.

SQL_MAX_CURSOR_NAME_LEN 18.

SQL_MAX_OWNER_NAME_LEN 0 to specify that owner names are not supported. SQL_MAX_PROCEDURE_NAME_

LEN

0 to specify that procedure names are not supported.

SQL_MAX_QUALIFIER_NAME_LE N

68. SQL_MAX_TABLE_NAME_LEN 64.

SQL_MULT_RESULT_SETS “N” for No. SQL_MULTIPLE_ACTIVE_TXN “N” for No.

SQL_NUMERIC_FUNCTIONS SQL_FN_NUM_MOD. SQL_NON_NULLABLE_COLUMNS

(65001)

1 (SQL_NNC_NON_NULL) to indicate that NOT NULL is enforced in CREATE TABLE statements. 0 (SQL_NNC_NULL) to indicate that NOT NULL is not enforced in CREATE TABLE statements. SQL_ODBC_API_CONFORMANCE 1 for Level 1 conformance.

SQL_ODBC_SAG_CLI_ CONFORMANCE

1 for SAG-compliant. SQL_ODBC_SQL_CONFORMANC

E

0 to indicate Minimum SQL conformance, although Maximizer ODBC supports some core SQL functions as well.

SQL_ODBC_SQL_OPT_IEF “N” for No.

SQL_ODBC_VER Handled by the Driver Manager (ODBC.DLL).

SQL_OUTER_JOINS “Y” for Yes.

SQL_OWNER_TERM “” to indicate not supported by this driver. SQL_PROCEDURE_TERM “” to indicate not supported by this driver.

SQL_PROCEDURES “N” for No.

SQL_QUALIFIER_NAME_ SEPARATOR

“\.”

SQL_QUALIFIER_TERM “DATABASE.”

(31)

SQLSpecialColumns

Maximizer ODBC uses unique indexes as the optimal set of columns that uniquely identify a row in the table. Maximizer ODBC cannot identify columns that are automatically updated when any value in the row is updated by a transaction.

SQL_ROW_UPDATES “Y” for Yes.

SQL_SCROLL_CONCURRENCY SQL_SCCO_READ_ONLY. SQL_SCROLL_OPTIONS SQL_SO_FORWARD_ONLY. SQL_SEARCH_PATTERN_ESCAPE “\.”

SQL_SERVER_NAME Long pointer to the server name character string supplied by Maximizer ODBC (e.g.,

SQL_SYSTEM_FUNCTIONS SQL_FN_SYS_USERNAME | SQL_FN_SYS_DBNAME.

SQL_TXN_CAPABLE 0 to indicate that transactions are not supported. SQL_TXN_ISOLATION_OPTION 0 to indicate that transactions are not supported. SQL_USER_NAME Character string of the login name.

(32)

SQL Conformance

Minimum SQL Grammar

Maximizer ODBC supports Minimum SQL Grammar as documented in Appendix C of Microsoft’s ODBC 2.0 Programmer’s Reference and SDK Guide. The following table shows the Minimum SQL Grammar that Maximizer ODBC supports.

Table 5 Minimum SQL Grammar Supported by Maximizer ODBC Minimum SQL

Grammar Examples Comments

DELETE DELETE FROM notes WHERE Owner_Id = ‘Kirsten Allen.’ INSERT INSERT INTO notes (Client_Id,

Contact_Number, [Date], [Time], [Text]) VALUES (‘1234’,0,’1995/ 03/20’,’14:03:32:85’,

’Appointment with CIO’).

Date, Time, and Text are reserved words and thus are enclosed in [.

INSERT INTO RELATED_ENTRIES (RELATED_TO_ID, RELATED_TO_NUMBER, RELATED_FROM_ID, RELATED-FROM_NUMBER, “DESC” ) VALUES (00100200061306367115c’, 0, ‘001124000913063671156c’, 0, ’Insertion into related_entries table’).

DESC is a reserved word. It is enclosed in double quotes.

SELECT SELECT* FROM notes;

SELECT Client_Id FROM notes WHERE Type=0.

* should refrain from using Select* From... since all records will be returned.

UPDATE UPDATE notes SET [Text]= ‘Appointment with CEO’ WHERE Client_Id=’1234’ and

Contact_Number=0 and Date=’1995/03/20’ and Time=’14:03:32:85’.

(33)

Core SQL Grammar

Maximizer ODBC partially supports Core SQL Grammar as documented in Appendix C of Microsoft’s ODBC 2.0 Programmer’s Reference and SDK Guide. The following table shows the Core SQL Grammar that Maximizer ODBC supports.

Table 6 Core SQL Grammar Supported by Maximizer ODBC Core SLQ

SELECT SELECT COUNT(Date), [Date] FROM notes GROUP BY [Date] HAVING [Date]>’1995/ 03/19.’

In addition to supporting a GROUP BY on a column-list, as specified in Microsoft’s

ODBC SDK Programmer’s Reference, the

ODBC Driver has extended the syntax to support a GROUP BY on an expression-list.

HAVING is not supported without GROUP BY.

For a more detailed description of the Maximizer ODBC Driver’s extensions to the ODBC GROUP BY syntax, see the second list of this chapter.

In addition to supporting an ORDER BY on a column-list, as specified in

Microsoft’s ODBC SDK Programmer’s

Reference, the Maximizer ODBC Driver

has extended the syntax to support an ORDER BY on an expression-list or on any expression in a GROUP BY expression-list. e.g., SELECT * FROM EMP ORDER BY a+b,c+d,e.

This causes the result table to be ordered by three expressions: a+b, c+d, and e. SELECT

(continued)

If the expression is a single positive integer literal, then that literal will be interpreted as the number of the column in the result set and ordering will be done on that column. No ordering is allowed on Set Functions or on an expression that contains a Set Function. [UNION select-statement] is not

supported.

Sub-queries are not supported.

approximate-numeric-literal

SELECT * FROM notes WHERE type = 0 INSERT INTO notes (type) VALUES (0)

(34)

between-predicate

SELECT [Text] FROM notes WHERE type BETWEEN 0 AND 3

The syntax expression1 BETWEEN expression2 and expression3 returns TRUE if expression1>=expression2 or returns FALSE if expression1>= expression3.

correlation-name

SELECT * FROM EMP notes WHERE

notes.Client_Id = ‘1234’

The Maximizer ODBC Driver supports both table and column correlation names.

exact-numeric-literal

INSERT INTO notes (Contact_Number, Type) VALUES (49000,3)

in-predicate SELECT * FROM notes WHERE [Time] in ‘13:15:30:95’,’14:03:32:8 5’);

set-function SELECT COUNT (Type) FROM notes

SELECT MIN([Time]) FROM notes

MIN(expression), MAX(expression), SUM(expression), AVG(expression), COUNT(*), and COUNT(expression) are supported.

COUNT(expression) counts all non-NULL values for an expression across a predicate.

e.g.

SELECT COUNT(A+B) FROM Q counts all the rows in Q where A+B does not equal NULL.

Table 6 Core SQL Grammar Supported by Maximizer ODBC (Continued) Core SLQ

(35)

Extended SQL Grammar

Maximizer ODBC partially supports Extended SQL Grammar as identified in the following table.

Table 7 Extended SQL Grammar Supported by ODBC Driver Extended SQL

LEFT OUTER JOIN

Two-table outer join: SELECT * FROM People LEFT OUTER JOIN notes ON People.Client_Id = Notes. Client_Id Three-table outer join: SELECT * FROM (People LEFT OUTER JOIN Notes ON People.Client_Id = Notes. Client_Id) LEFT OUTER JOIN Addr ON People.EmpID = Addr.EmpID.

Embedded in vendor string:

SELECT t1.Client_Id, ename FROM {oj People t2 LEFT OUTER JOIN notes t1 ON

t2.Client_Id=t1.Client_I d}

Maximizer ODBC supports two-table outer joins as specified in Appendix C of Microsoft’s ODBC 2.0 Programmer’s

Reference and SDK Guide.

In addition to simple two-table outer joins, Maximizer ODBC supports n-way nested outer joins.

The outer join may or may not be embedded in a vendor string. If a vendor string is used, Maximizer ODBC will strip it off and parse the actual outer join text. Maximizer ODBC’s implementation of LEFT OUTER JOIN goes beyond the syntax in Appendix C of Microsoft’s ODBC 2.0

Programmer’s Reference and SDK Guide.

For further information on the Driver’s support of LEFT OUTER JOIN, see the “Left Outer Join” section in this appendix.

(36)

date-literal SELECT * FROM emp WHERE hire_date < '1992-02-02'

SELECT * FROM emp WHERE hire_date < {d '1992-02-02'}

Maximizer ODBC supports the following date literal format:

YYYY-MM-DD

Dates may be in the range of year 0 to 9999.

Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. Maximizer ODBC treats the character string

representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_DATE. This becomes important when conversions are attempted. For example, CONVERT({ d '1992-02-02' }, SQL_TIMESTAMP) is valid whereas CONVERT('1992-02-02',

SQL_TIMESTAMP) returns an invalid SQL_TIMESTAMP value.

time-literal SELECT * FROM bday WHERE btime = '10:04:29'

SELECT * FROM bday WHERE btime = {t '10:04:29'}

Maximizer ODBC supports the following time literal format:

HH:MM:SS

Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. Maximizer ODBC treats the character string

representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIME.

timestamp-literal

SELECT * FROM bday WHERE btime = '1965-08-25 05:25:00' SELECT * FROM bday WHERE

btime = {ts '1965-08-25 10:04:29'}

Maximizer ODBC supports the following time literal format:

YYYY-MM-DD HH:MM:SS

Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. Maximizer ODBC treats the character string

representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIMESTAMP.

Table 7 Extended SQL Grammar Supported by ODBC Driver (Continued) Extended SQL

(37)

Delimited Identifiers in SQL Statements

Column names and table names can occur as delimited identifiers if they contain non-ODBC standard characters. The delimiter character for delimiting the SQL syntax for these functions (see Appendix G of Microsoft’s ODBC 2.0 Programmer’s Reference and

SDK Guide) is a double quote (e.g. SELECT “last-name” FROM “non-standard-tbl”).

Date Arithmetic

SELECT * FROM invoices WHERE invoice_date > '1993-01-01' AND invoice_date < {d '1993-01-01'} + 30 SELECT * FROM payment WHERE payment_date -invoice_date > 30

Maximizer ODBC supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a CONVERT on the date.)

Maximizer ODBC also supports subtracting one date from another to yield a number of days.

Note: The “True Date” option must be turned on.

Table 7 Extended SQL Grammar Supported by ODBC Driver (Continued) Extended SQL

(38)

Scalar Functions

Maximizer ODBC supports ODBC scalar functions that may be included in an SQL statement as a primary expression.

The scalar functions that the Maximizer ODBC supports are described below.

String Functions

Example

SELECT [Date] FROM notes WHERE LCASE([Text]) = 'appointment with ceo'

Numeric Functions

Example

SELECT * FROM notes WHERE MOD(Type,3) = 0

Date Functions

Example

SELECT * FROM emp WHERE YEAR(hire_date) < 1990

System Functions

Example

SELECT * FROM notes WHERE Owner_Id = USER()

Note

IF and NULL are internal to Maximizer ODBC.

• CONCAT • LENGTH • RTRIM

• LCASE • LTRIM • SUBSTRING

• LEFT • RIGHT • UCASE

• MOD

• CURDATE • DAYOFMONTH • MONTH

• CURTIME • DAYOFWEEK • YEAR

(39)

Conversion Functions

Example

SELECT Client_Id FROM notes WHERE ‘1993-01-02’ =CONVERT ([Date], SQL_DATE) + 30

(40)

Data Types

The following table shows all the ODBC SQL data types that Maximizer ODBC supports. Application writers can use SQLGetTypeInfo to determine which of these ODBC SQL data types are supported by Maximizer ODBC. (For further details on SQLGetTypeInfo, see Microsoft’s ODBC 2.0 Programmer’s Reference and SDK Guide.) As the following table indicates, how Maximizer ODBC maps a data type to a default type, unless another data type conversion is specified by the user when SQLGetData or SQLBindCol is called, depends on whether or not the type to be mapped is part of a complex expression. (For a discussion of data type conversions, see Appendix D of Microsoft’s ODBC 2.0 Programmer’s Reference and SDK Guide.)

ODBC Data Type Mapping

Table 8 ODBC Data Type Mapping

ODBC Data Type Expression Default Type SUM/AVG

SQL_BIT SQL_BIT N/A

SQL_NUMERIC SQL_DOUBLE same

SQL_DATE SQL_DATE N/A

SQL_TIMESTAMP SQL_TIMESTAMP N/A

SQL_DOUBLE SQL_DOUBLE same

SQL_REAL SQL_DOUBLE same

SQL_INTEGER SQL_DOUBLE same

SQL_LONGVARBINARY N/A N/A

SQL_LONGVARCHAR SQL_LONGVARCHAR N/A

SQL_SMALLINT SQL_DOUBLE same

SQL_CHAR SQL_CHAR N/A

SQL_TIME SQL_TIME N/A

SQL_TINYINT SQL_DOUBLE same

(41)

The table below describes each of the columns in the preceding table.

Limitations on SQL_LONGVARCHAR

Maximizer ODBC imposes the following limitations on columns of data type SQL_LONGVARCHAR:

• LIKE predicate operates on the first 65,500 characters of the column data. • All other predicates operate on the first 256 characters of the column data. • SELECT statements with GROUP BY, DISTINCT, and ORDER BY return all the data

but only order on the first 256 characters of the column data.

• In a single call to SQLGetData, the maximum number of characters returned by Maximizer ODBC for an SQL_LONGVARCHAR column is 65,500. Multiple calls must be made to SQLGetData to retrieve column data over 65,500 characters.

Comparison of Floats

Maximizer ODBC compares floating point numbers in comparison predicates using an “almost equals” algorithm. For example, 12.203 =

12.20300000000000000000000000001, and 12.203 is >=

12.20300000000000000000000000001. The epsilon value is defined as DBL EPSILON in float.h (.2204460492503131e-016). This feature works for large numbers, but > and < will not be detected for small numbers; small numbers will be detected as equal.

Table 9 Description of Columns in ODBC Data Type Mapping Table Column Description

ODBC Data Type The ODBC data type.

Expression Default Type If a column is part of a complex expression, Maximizer ODBC will convert the data type of that column to an expression default type in the result column. For example, in the query SELECT c1 * 10, where c1 is of type

SQL_TINYINT, the result column will be of type

SQL_DOUBLE. The type of the expression result column is determined at expression “compile time” and not at expression “run time.” All numeric expressions will be defaulted to the SQL_DOUBLE data type.

SUM/AVG This is the data type returned by the aggregate functions SUM and AVG. This column corresponds to the type of expression in the aggregate (e.g., SUM(expression)). An entry of “same” means that the type returned by SUM renders the same type as the expression.

(42)

Representation of Float Infinity

Maximizer ODBC has both a binary and a character representation of float infinity as shown in the following table.

Maximizer Date and Time

Maximizer ODBC presents the Maximizer Date and Time in character string format (i.e., “Date_string” and “Time_string”). The application, however, can convert the “Date_string” in SQL_CHAR to SQL_DATE if so desired in any date operations. The same is true for “Time_string.”

Table 10 Representation of Float Infinity

Value Name

Single BIN Positive

Single

Character Double BIN Positive

Double Character Maximum Positive 0x7FEFFFFFFFFFFFFF Maximum Negative 0x7FEFFFFFFFFFFFFF Infinity Positive 0x7F800000 1E999 0x7FF0000000000000 1E999 Infinity Negative 0xFF800000 -1E999 0xFFF0000000000000 -1E999 CAUTION

“Time_string” is in “HH:MM:SS:HS” format (i.e., Maximizer supports the hundredth of a second, such that a loss in precision may result in converting time in SQL_CHAR to that in SQL_TIME).

(43)

Other Characteristics

SQL Statement Limitations

Maximizer ODBC applies the following restrictions to SQL statements: • Maximum of 1000 characters in a literal

• Maximum of 300 ANDed predicates (e.g., SELECT * FROM emp WHERE c1 AND c2 AND c3 use two ANDed predicates). Depending on the stack size allocated by the calling application, limitations on very long expressions (e.g., SELECT * FROM emp where c1 = 1 + 2 + 3 + 4 + 5 + 6...) may also apply. (See “Stack Size” below.) • Maximum of 256 columns in a CREATE TABLE statement

• Maximum of 64 characters in a table or column and in index names

• A character in a character string literal may be any ANSI character between 1 and 255 decimals. A single quote (') must be represented as two consecutive single quotes ('').

Stack Size

Maximizer ODBC requires a minimum available stack size of 10K from the calling application, and in some cases may require more. Therefore, we recommend an available stack size of at least 20K.

Precision and Scale

Maximizer ODBC cannot determine the precision and scale of data values of type SQL_NUMERIC.

Collate Sequence

Maximizer ODBC assumes that all tables accessed during an active connection have the same collate sequence. All tables within a query statement must have the same collating sequence for comparison operations to work properly.

(44)

Closing an Open Table

Calling SQLFreeStmt with the SQL_CLOSE option changes the SQLState but does not close the open tables used by the hStmt. To close the tables currently used by hStmt, SQLFreeStmt must be called with the SQL_DROP option. In the following example, the Emp and Dept tables remain open:

SQLPrepare(hStmt, “SELECT * FROM Emp, Dept”, SQL_NTS); SQLExecute(hStmt);

SQLFetch until NO_DATA_FOUND SQLFreeStmt(hStmt, SQL_CLOSE);

When SQLPrepare is subsequently called on by the hStmt, the tables used in the previous statement are closed. For example, when the following call is made, both the Emp and Dept tables will be closed by Maximizer ODBC:

SQLPrepare(hStmt, SELECT * FROM Customer,SQL_NTS); The following call would then close the table Customer: SQLFreeStmt(hStmt, SQL_DROP);

Concurrency

The timeliness of data, dynamic or snapshot, is determined by whether or not execution of a query results in a sort. Queries with DISTINCT, GROUP BY, or ORDER BY will result in a sort by Maximizer ODBC, unless an index exists that satisfies the required ordering.

For those queries that do not result in a sort by Maximizer ODBC, the data fetched will be from the data files (called dynamic). For those queries that do result in a sort by Maximizer ODBC, the data fetched will be from a temporary table (called a snapshot). The temporary table is built from the required data in the original data file at SQLExecute time. Temporary tables greater than 64K will be written to a temporary file in the directory indicated by the application user’s TEMP environment variable. If there is not enough disk space on the user’s system to write the temporary file, the sort will fail. If the user is short of disk space on a local machine, the TEMP

environment should be set to a network drive.

Note

For some sort operations (e.g., for SELECT statements where long data columns are included in the select-list or for SELECT statements with GROUP BY), Maximizer ODBC may use bookmarks that the Driver assumes are persistent within a SELECT statement. A situation may arise in which another application updates or deletes the row that a bookmark references. To avoid this situation, an application may set an exclusive lock on the table being sorted through a call to SQLSetStmtOption, with fOption = 1153 and vParam = 1.

(45)

Extensions to the ODBC GROUP BY Syntax

GROUP BY Syntax

In addition to the GROUP BY syntax in a SELECT statement as specified in Microsoft’s

ODBC 2.0 Programmer’s Reference and SDK Guide, the Maximizer ODBC Driver

supports an extended GROUP BY syntax as follows:

[GROUP BY expression,[expression]...]

If an SQL_LONGVARCHAR expression is used in a GROUP BY clause, the Maximizer ODBC Driver only uses the first 256 characters of that expression.

GROUP BY Semantics

A GROUP BY query returns a result set which contains one row of the select-list for every group encountered. (See Appendix C of Microsoft’s ODBC 2.0 Programmer’s

Reference and SDK Guide for the syntax of a select-list.) For example,

SELECT Dept FROM Emp GROUP BY Dept

uses the Emp Table to produce a list of unique departments.

Another example might be to count the number of employees in each department:

SELECT Dept, COUNT(*) FROM Emp GROUP BY Dept

In this case, the result set contains a list of unique departments and the count of employees in each department.

SELECT Statement select-list

As the above examples show, a select-list in a SELECT statement containing a GROUP BY clause can contain:

• An exact replica of one of the expressions in the GROUP BY expression-list. The exact replica can actually be a sub-expression in the select-list. For example,

SELECT Dept, Salary + Commission, Salary + Commission /

AVG(Salary+Commission) * 100 FROM Emp GROUP BY Dept, Salary + Commission

is valid since no expression using ambiguous values occurs.

• Α set function. The rows operated on by the set function are those rows remaining after the WHERE search condition is applied. For example, in

SELECT COUNT(*) FROM Emp WHERE Salary > 30000 GROUP BY Dept

COUNT(*) counts only those rows in the Dept groups that have Salary > 30000.

(46)

HAVING With GROUP BY

The expressions in a HAVING clause may contain constants, set functions, or an exact replica of one of the expressions in the GROUP BY expression-list. For example,

SELECT Dept, COUNT(*) FROM Emp GROUP BY Dept HAVING COUNT(*) > 2

returns departments where the count of employees is greater than 2.

SELECT Dept, COUNT(*) FROM Emp GROUP BY Dept HAVING COUNT(*) > 2 AND Dept BETWEEN '120' AND '180'

returns departments between 120 and 180 where the number of employees is greater than 2.

The Maximizer ODBC Driver has implemented LEFT OUTER JOIN using SQL92 (SQL2) as a model. Since SQL2 is a new SQL standard and adequate descriptions do not exist, this appendix provides an explanation of this new outer join syntax and its associated meaning in the Maximizer ODBC Driver.

Note

(47)

Left Outer Join

Syntax

The syntax taken here is a subset of the entire SQL92 syntax, which includes right outer joins, full outer joins, and inner joins. The TableRefList non terminal below occurs after the FROM keyword in a SELECT statement and before any subsequent WHERE, HAVING, and other clauses. Note the cooperatively recursive nature of TableRef and

LeftOuterJoin. In other words, a TableRef can be a left outer join that can include TableRef’s which, in turn, can be left outer joins ad infinitum.

TableRefList : TableRef [, TableRefList] | TableRef | OuterJoinVS [, TableRefList] TableRef : TableName [CorrelationName] | LeftOuterJoin | ( LeftOuterJoin ) LeftOuterJoin :

TableRef LEFT OUTER JOIN TableRef ON SearchCond

The SearchCond contains join conditions which in their usual form are

LT.ColumnName = RT.ColumnName, where LT is left table, RT is right table, and ColumnName represents some column within a given domain. No literals can exist in the ON SearchCond.

Vendor Strings

The syntax above includes but goes beyond the current ODBC syntax in Appendix C of Microsoft's ODBC 2.0 Programmer’s Reference and SDK Guide. Furthermore, the vendor string escape sequence at the beginning and end of the LeftOuterJoin does not change the core syntax of the outer join. As shown in the above syntax, the Maximizer ODBC Driver accepts outer join syntax without the vendor strings. However, for applications that want to comply with ODBC across multiple databases, the vendor string construction should be used. Since ODBC vendor string outer joins do not support more than two tables, it may be necessary to use the syntax shown below in Example 4 below rather than that shown in Examples 2 and 3.

(48)

Examples of Left Outer Join

The following four tables provide examples of Left Outer Join.

Employee

Department

Address

Location

FirstName LastName DeptID EmpID

Frankie Avalon D103 E1

Gordon Lightfoot D102 E2

Lawrence Welk D101 E3

Bruce Cockburn D102 E4

DeptID LocID Name

D101 L1 TV D102 L2 Folk EmpID Street E1 101 MemLane E2 14 Yonge St. LocID Name L1 PlanetX L2 PlanetY

(49)

Example 1: Simple Two-way Left Outer Join

SELECT * FROM Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID

This simple two-way outer join produces the following result set:

Notice the NULL entry for Frankie Avalon. This occurs because no DeptID of D103 was found in the Dept table. In a standard (INNER) join, Franky Avalon would have been dropped from the result set altogether.

Algorithm

The algorithm that the Maximizer ODBC Driver uses in the above example is as follows: Taking the left table (TL), traverse the right table(TR), and for every case where the ON condition is TRUE, return a result set row composed of the appropriate TR row appended to the current TL row. If the ON condition is false, in all cases of TR row create a row instance of TR with all column values NULL. That result set, combined with the current TL row, becomes the returned result set row. In the simple two-way left outer join shown above, Emp is TL and Dept is TR.

Emp Dept

FirstName LastName DeptID EmpID DeptID LocID Name

Frankie Avalon D103 E1 NULL NULL NULL

Gordon Lightfoot D102 E2 D102 L2 Folk

Lawrence Welk D101 E3 D101 L1 TV

Bruce Cockburn D102 E4 D101 L1 TV

Note

Although irrelevant to the algorithm, appending TL to TR assumes proper projection as specified in the select list of the query. This projection ranges from all columns (i.e., SELECT * FROM...) to only on column in the result set (i.e., SELECT FirstName FROM...).

(50)

Example 2: Three-way Radiating Left Outer Join

With radiating left outer joins, all other tables are joined onto one central table. In this example, Emp is the central table and all joins radiate from that table.

SELECT * FROM (Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID) LEFT OUTER JOIN Addr ON Emp.EmpID = Addr.EmpID

Example 3: Three-way Chaining Left Outer Join

In a chaining left outer join, one table is joined to another, and that table, in turn, is again joined to another.

SELECT * FROM (Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID) LEFT OUTER JOIN Loc ON Dept.LocID = Loc.LocID

This join could also be expressed as:

SELECT * FROM Emp LEFT OUTER JOIN (Dept LEFT OUTER JOIN Loc ON Dept.LocID = Loc.LocID) ON Emp.DeptID = Dept.DeptID

We recommend the first syntax since it lends itself to both the radiating and chaining joins. The second syntax cannot be used for radiating joins because nested left outer join ON conditions cannot reference columns in tables outside their nesting. In other words, in the following query, the reference to Emp.EmpID is illegal:

SELECT * FROM Emp LEFT OUTER JOIN (Dept LEFT OUTER JOIN Addr ON Emp.EmpID = Addr.EmpID) ON Emp.DeptID = Dept.DeptID

Emp Dept Addr

FirstName LastName DeptID EmpID DeptID LocID Name EmpID Street

Frankie Avalon D103 E1 NULL NULL NULL E1 101

MemLane

Gordon Lightfoot D102 E2 D102 L2 Folk E2 14 Young St

Lawrence Welk D101 E3 D101 L1 TV NULL NULL

Bruce Cockburn D102 E4 D101 L1 TV NULL NULL

Emp Dept Loc

FirstName LastName DeptID EmpID DeptID LocID Name LocID Name

Frankie Avalon D103 E1 NULL NULL NULL NULL NULL

Gordon Lightfoot D102 E2 D102 L2 Folk L2 PlanetY

Lawrence Welk D101 E3 D101 L1 TV L1 PlanetX

(51)

Example 4: Three-way Radiating Left Outer Join, Less Optimized

SELECT * FROM Emp E1 LEFT OUTER JOIN Dept ON E1.DeptID = Dept.DeptID, Emp E2 LEFT OUTER JOIN Addr ON E2.EmpID = Addr.EmpID WHERE E1.EmpID = E2.EmpID

This query returns the same results as the one in Example 2, assuming there are no NULL values for EmpID in Emp. This query, however, is not optimized as well as Example 2 and can be much slower.

Emp Dept Addr

FirstName LastName DeptID EmpID DeptID LocID Name EmpID Street

Frankie Avalon D103 E1 NULL NULL NULL E1 101

MemLane

Gordon Lightfoot D102 E2 D102 L2 Folk E2 14 Young

St

Lawrence Welk D101 E3 D101 L1 TV NULL NULL

(52)

Tips and Tricks for Maximizer ODBC

These tips and techniques help optimize use of the Maximizer ODBC Driver.

Joining

• Use indexed fields to speed up processing in the Where clause. • Join only the tables that you require.

• See the table relational diagrams and index references in Chapter 4: “Maximizer ODBC: Data Tables” for fields to use for joining tables.

Selecting

• Do not select unnecessary fields.

• Limit the result set by specifying conditions (e.g., Client_Id =1234567 and Contact_Number=1).

• Consult the Maximizer ODBC Table Index Definitions table (in Chapter 9) to identify which fields are indexed so they can be used in the search condition. • Do not skip the beginning portion of the index. For example, if Client_Id and

Contact_Number are the fields in the index, do not use “Contact_Number=2” as the search condition.

• Always specify indexed fields together in the Where clause. For example, “SELECT ... FROM Client WHERE (Client_Id=... AND Contact Number=...) AND (Client_Id= ... AND Contact_Number= ...)” is preferred over “SELECT ... FROM Client WHERE (Client_Id= ... AND Client_Id= ...) AND (Contact_Number= ... AND

Contact_Number=...)”

Updating

• Do not include fields that cannot be updated in the SQL statement. Non-modifiable fields will be rejected and require extra validation.

General Tips

• Be creative in determining the correct syntax; some implementations of ODBC SQL are non-standard and may be quite challenging. You may have to be resourceful and consult the application’s ODBC SQL reference for the appropriate syntax. If you find syntax errors to be persistent with a specific application, call the

manufacturer’s technical support line for help. You may also find additional help in that application’s Readme file.

• Use database tools to refine the SQL statement before attempting to use it in embedded SQL in a C/C++ application.

• Read the ODBC section of the application’s documentation. To avoid frustration, make a note of any limitations and restraints.

(53)

Examples of Inserting Records Using Maximizer ODBC

If you are using Maximizer ODBC to insert records, you may find these examples useful. These examples show SQL statements for adding records to certain tables, such as the Appointments table, Company table, or Hotlist table, among others. All examples show the minimum fields that are required to successfully perform an insert—for other available fields, refer to the Maximizer CRM Customization Suite - Volume I.

Appointments Table

INSERT INTO APPOINTMENTS (App_Date,App_Time, [Text])

VALUES ('11/11/00','08:30:00:00','Inserting Appointments');

Address Table

INSERT INTO ADDRESS (Client_ID,Name)

VALUES ('000512009323076431425C','Alternate Address');

Client Table

It is not possible to perform an insert, as the Client table is a read-only table.

Company Table

INSERT INTO COMPANY (Company_Name)

VALUES ('New Company');

HotList Table

INSERT INTO HOTLIST (Schedule_Date,Schedule_Time, [Text])

VALUES ('11/11/00','08:30:00:00','Inserting Hotlist');

Note

Some tables are read-only and you cannot perform an insert. These tables include: CLIENT, COMBINED_CLIENT_USER_FIELDS, and COMBINED_OPP_USER_FIELDS.

(54)

Notes

INSERT INTO NOTES (Client_ID,Contact_Number, [Text])

VALUES ('000512009323076431425C',0,'Inserting Note');

Opportunity Table

INSERT INTO OPPORTUNITY (Client_ID,Team_Id)

VALUES ('980204100691730339346C',-1);

People Table

INSERT INTO PEOPLE (Last_Name)

VALUES ('Smith');

Prob_Close_Range Table

In order for the insert to work correctly, there should be no existing Prob_Close_Range record with a Range_Min or Range_Max value which falls between 90 and 100. INSERT INTO PROB_CLOSE_RANGE (Range_Min,Range_Max,Name)

(55)

4

C

H A P T E R

4

Maximizer ODBC:

Data Tables

“Alphabetical List of Maximizer ODBC Tables” on page 48 “Common Fields” on page 50

“User-Defined Field Tables” on page 53 “Address Book Entry Tables” on page 64

“Appointment, Hotlist, and Notes Tables” on page 85 “Campaign Tables” on page 97

“Customer Service Tables” on page 106 “Opportunity Tables” on page 111 “Strategy Library Tables” on page 141 “User and Group Tables” on page 162

(56)

Alphabetical List of Maximizer ODBC Tables

The following table lists the documented Maximizer ODBC tables and their page numbers.

Table Name

Starting on Page Number

ABPartners Table page 65

Activity Table page 115

Address Table page 66

Appointments Table page 85

Automatic_Campaign Table page 98

CampaignAccounts Table page 100

Client Table page 68

Combined_Campaign_User_Fields Table page 102

Combined_Client_User_Fields Table page 63

Combined_CSCase_User_Fields Table page 106

Combined_Opp_User_Fields Table page 117

Company Table page 74

CSCases Table page 107

Custom_Child Table page 172

Custom_Independent Table page 168

Hotlist Table page 90

Manual_Campaign Table page 103

Notes Table page 93

OppActivity Table page 119

OppComp Table page 123

OppContact Table page 125

Opportunity Table page 127

OppPartners Table page 131

OppRole Table page 133

OppStep Table page 135

(57)

People Table page 77

Prob_Close_Range Table page 138

Reasons Table page 140

Related_Entries Table page 82

Role Table page 142

Role_Standing_Link Table page 143

Security_Group Table page 162

Standing_For_Role Table page 144

Standing_For_Success_Factor Table page 146

Step Table page 147

Step_Activity_Link Table page 149

Strategy Table page 151

Strategy_Role_Link Table page 152

Strategy_Step_Link Table page 156

Strategy_Success_Factor_Link Table page 155

Success_Factor Table page 159

Success_Factor_Standing_Link Table page 160

Team Table page 163

UDF_List Table page 56

UDF_Table_Item Table page 61

User_Details Table page 165

Table Name

Starting on Page Number

(58)

Common Fields

The following fields are common to many, but not necessarily all, Maximizer ODBC database tables. Some tables use these fields in different ways than described here. Refer to the table

documentation for specific information.

Identity Fields

Security Fields

Note that users who have the “Modify other users’ private entries” access right can view private records.

Table 11 Identity Fields

Field Name Data Type Len.

Foreign Table Reference

Read-Only Description and Rules

Data_Machine_Id Numeric 9 Yes System-generated unique

database ID for MaxExchange synchronization.

Cannot insert or update. Sequence_Numb

er

Numeric 9 Yes System-generated unique

record ID for MaxExchange synchronization.

Cannot insert or update.

Table 12 Security Fields

Private Integer 2 Specifies Full Access security

for the record:

0 = Public (any user may view or modify the record) 1 = Private (only the user or

group specified in the Owner_Id field may view or modify the record) Can insert. Can update.

(59)

History Fields

All Creation information (Creator_Id, Creation_Date, and Creation_Time) is read-only for all users with the exception of the MASTER user who may insert, update, and delete. If a creation

Owner_Id String 11 User_Details.

User_Id or

Security_Group. Group_ID

Yes User or group who owns the record. Applicable only if Private field value is 1 (Private). Otherwise, the field is Public and is ignored. Can insert. Can update.

ReadOnly_Id String 11 Ignored if Private = 0.

Identifies which user or group has read-only rights to the record. Valid if ReadPriv = 1 and Private = 1. Can insert. Can update.

ReadPriv Integer 2 Ignored if Private = 0.

Identifies whether read-only access to the record is public or private:

0 = Public (everybody can see the record)

1 = Private (only user or group specified in ReadOnly_Id field can see the record, except user or group specified in Owner_Id field, who still has read and modify rights)

Can insert. Can update.

Table 12 Security Fields (Continued)

(60)

information field is deleted, it will be replaced by the default. This rule applies to all tables that are not read-only.

Table 13 History Fields

Creation_Date – or – Create_Date – or – Date (Notes table)

DateString 10 Yes Date and time when the

record was created. Default is current system date at time of creation.

Read-only for all users with the exception of MASTER user who may insert and update the Creator_Id. Otherwise, cannot insert, cannot update. Creation_Time – or – Create_Time – or – Time (Notes table) TimeString 11 Yes

Creator_Id String 11 Yes User who created the record.

Default is current user ID. However, may be any user ID.

System generated.

Read-only for all users with the exception of MASTER user who may insert and update the Creator_Id. Otherwise, cannot insert, cannot update.

Last_Modify_Dat e

DateString 10 Must be populated by the

user. Not automatically updated.

Can insert. Can update. Last_Modify_Tim

e

Customization Suite - ODBC

Registered Trademarks and Proprietary Names

Applicability

Contents

1

C

Introduction

In this chapter...

Who Should Use This Guide?

Related Documentation

2

C

Maximizer ODBC:

Overview

In this chapter...

Using Maximizer ODBC









Defining a Data Source















Using ODBC-Compliant Software

Access Control and Security









Database Level

Table Level

Record Level

Field Level

3

C

Maximizer ODBC:

Programmer’s Reference

In this chapter...

Maximizer ODBC Limits

ODBC API Conformance

Notes on the ODBC API Conformance Table

SQLMoreResults

SQLTransact

SQLSetStmtOption / SQLGetStmtOption

SQLSetConnectOption / SQLGetConnectOption

SQLGetTypeInfo

SQLGetInfo

SQLSpecialColumns

SQL Conformance

Minimum SQL Grammar

Core SQL Grammar

Extended SQL Grammar

Delimited Identifiers in SQL Statements

Scalar Functions

String Functions

Numeric Functions

Date Functions

System Functions

Conversion Functions

Data Types

ODBC Data Type Mapping

Limitations on SQL_LONGVARCHAR

Comparison of Floats

Representation of Float Infinity

Maximizer Date and Time

Other Characteristics

SQL Statement Limitations

Stack Size

Precision and Scale

Collate Sequence

Closing an Open Table

Concurrency

Extensions to the ODBC GROUP BY Syntax

GROUP BY Syntax

GROUP BY Semantics