APPENDIX Adding Functions to the PBODB170 Initialization File

About this appendix

Usually, you do not need to modify the PBODB170 initialization file. In certain situations, however, you might need to add functions to the PBODB170 initialization file for connections to your back-end DBMS through the ODBC or OLE DB interface in PowerBuilder.

This appendix describes how to add functions to the PBODB170 initialization file if necessary.

About the PBODB170 initialization file

What is the PBODB170 initialization file?

When you access data through the ODBC interface, PowerBuilder uses the PBODB170 initialization file (PBODB170.INI) to maintain access to extended functionality in the back-end DBMS for which ODBC does not provide an API call. Examples of extended functionality are SQL syntax or function calls specific to a particular DBMS.

See also: Where is PBODB170.INI?

Editing PBODB170.INI

In most cases, you do not need to modify PBODB170.INI. Changes to this file can adversely affect PowerBuilder. Change PBODB170.INI only if you are asked to do so by a Technical Support representative.

However, you can edit PBODB170.INI if you need to add functions for your back-end DBMS.

If you modify PBODB170.INI, first make a copy of the existing file. Then keep a record of all changes you make. If you call Technical Support after modifying PBODB170.INI, tell the representative that you changed the file and describe the changes you made.

Adding functions to PBODB170.INI

PBODB170.INI lists the functions for certain DBMSs that have ODBC drivers. If you need to add a function to PBODB170.INI for use with your back-end DBMS, you can do either of the following:

  • Existing sections

    Add the function to the Functions section for your back-end database if this section exists in PBODB170.INI.

  • New sections

    Create new sections for your back-end DBMS in PBODB170.INI and add the function to the newly created Functions section.

Adding functions to an existing section in the file

If sections for your back-end DBMS already exist in PBODB170.INI, use the following procedure to add new functions.

To add functions to an existing section in PBODB170.INI:

  1. Open PBODB170.INI in one of the following ways:

  2. Locate the entry for your back-end DBMS in the DBMS Driver/DBMS Settings section of PBODB170.INI.

    For example, here is the PBODB170.INI entry for SQL Anywhere:

    ;*********************************************
    ;DBMS Driver/DBMS Settings see comments at end
    ;of file
    ;*********************************************
    ...
    [SQL Anywhere]
    PBSyntax='WATCOM50_SYNTAX'
    PBDateTime='STANDARD_DATETIME'
    PBFunctions='ASA_FUNCTIONS'
    PBDefaultValues='autoincrement,current date,
       current time,current timestamp,timestamp,
       null,user'
    PBDefaultCreate='YES'
    PBDefaultAlter='YES'
    PBDefaultExpressions='YES'
    DelimitIdentifier='YES'
    PBDateTimeInvalidInSearch='NO'
    PBTimeInvalidInSearch='YES'
    PBQualifierIsOwner='NO'
    PBSpecialDataTypes='WATCOM_SPECIALDATATYPES'
    IdentifierQuoteChar='"'
    PBSystemOwner='sys,dbo'
    PBUseProcOwner='YES'
    SQLSrvrTSName='YES'
    SQLSrvrTSQuote='YES'
    SQLSrvrTSDelimit='YES'
    ForeignKeyDeleteRule='Disallow if Dependent Rows
       Exist (RESTRICT),Delete any Dependent Rows
       (CASCADE),Set Dependent Columns to NULL
       (SET NULL)'
    TableListType='GLOBAL TEMPORARY'
  3. Find the name of the section in PBODB170.INI that contains function information for your back-end DBMS.

    To find this section, look for a line similar to the following in the DBMS Driver/DBMS Settings entry:

    PBFunctions='section_name'

    For example, the following line in the DBMS Driver/DBMS Settings entry for SQL Anywhere indicates that the name of the Functions section is ASA_FUNCTIONS:

    PBFunctions='ASA_FUNCTIONS'
  4. Find the Functions section for your back-end DBMS in PBODB170.INI.

    For example, here is the Functions section for SQL Anywhere:

    ;*********************************************
    ;Functions
    ;*********************************************
    [ASA_FUNCTIONS]
    AggrFuncs=avg(x),avg(distinct x),count(x),
       count(distinct x),count(*),list(x),
       list(distinct x),max(x),max(distinct x),
       min(x),min(distinct x),sum(x),sum(distinct x)
    Functions=abs(x),acos(x),asin(x),atan(x),
       atan2(x,y),ceiling(x),cos(x),cot(x),degrees(x),
       exp(x),floor(x),log(x),log10(x),
       mod(dividend,divisor),pi(*),power(x,y),
       radians(x),rand(),rand(x),
       remainder(dividend,divisor),round(x,y),
       sign(x),sin(x),sqrt(x),tan(x),
       "truncate"(x,y),ascii(x),byte_length(x),
       byte_substr(x,y,z),char(x),char_length(x),
       charindex(x,y),difference(x,y)insertstr(x,y,z),
       lcase(x),left(x,y),length(x), locate(x,y,z),
       lower(x),ltrim(x),patindex('x',y),repeat(x,y),
       replicate(x,y),right(x,y),rtrim(x),
       similar(x,y),soundex(x),space(x),str(x,y,z),
       string(x,...),stuff(w,x,y,z),substr(x,y,z),
       trim(x),ucase(x),upper(x),date(x),
       dateformat(x,y),datename(x,y),day(x),
       dayname(x),days(x),dow(x),hour(x),hours(x),
       minute(x),minutes(x),minutes(x,y),month(x),
       monthname(x),months(x),months(x,y),now(*),
       quarter(x),second(x),seconds(x),seconds(x,y),
       today(*),weeks(x),weeks(x,y),year(x),years(x),
       years(x,y),ymd(x,y,z),dateadd(x,y,z),
       datediff(x,y,z),datename(x,y),datepart(x,y),
       getdate(),cast(x as y),convert(x,y,z),
       hextoint(x),inttohex(x),
       connection_property(x,...),datalength(x),
       db_id(x),db_name(x),db_property(x),
       next_connection(x),next_database(x),
       property(x),property_name(x),
       property_number(x),property_description(x),
       argn(x,y,...),coalesce(x,...),
       estimate(x,y,z),estimate_source(x,y,z),
       experience_estimate(x,y,z),ifnull(x,y,z),
       index_estimate(x,y,z),isnull(x,...),
       number(*),plan(x),traceback(*)
  5. To add a new function, type a comma followed by the function name at the end of the appropriate function list, as follows:

    • Aggregate functions

      Add aggregate functions to the end of the AggrFuncs list.

    • All other functions

      Add all other functions to the end of the Functions list.

    Case sensitivity

    If the back-end DBMS you are using is case sensitive, be sure to use the required case when you add the function name.The following example shows a new function for SQL Anywhere added at the end of the Functions list:

    ;*********************************************
    	;Functions
    ;*********************************************
    	[ASA_FUNCTIONS]
    	AggrFuncs=avg(x),avg(distinct x),count(x),
    	   count(distinct x),count(*),list(x),
    	   list(distinct x),max(x),max(distinct x),
    	   min(x),min(distinct x),sum(x),sum(distinct x)
    	Functions=abs(x),acos(x),asin(x),atan(x),
    	   atan2(x,y),ceiling(x),cos(x),cot(x),degrees(x),
    	   exp(x),floor(x),log(x),log10(x),
    	   mod(dividend,divisor),pi(*),power(x,y),
    	   radians(x),rand(),rand(x),
    	   ...
    	   number(*),plan(x),traceback(*),newfunction()
  6. Save your changes to PBODB170.INI.

Adding functions to a new section in the file

If entries for your back-end DBMS do not exist in PBODB170.INI, use the following procedure to create the required sections and add the appropriate functions.

Before you start

For more about the settings to supply for your back-end DBMS in PBODB170.INI, read the comments at the end of the file.

To add functions to a new section in PBODB170.INI:

  1. Open PBODB170.INI in one of the following ways:

  2. Edit the DBMS Driver/DBMS Settings section of the PBODB170 initialization file to add an entry for your back-end DBMS.

    Finding the name

    The name required to identify the entry for your back-end DBMS in the DBMS Driver/DBMS Settings section is in PBODB170.INI.

    Make sure that you:

    • Follow the instructions in the comments at the end of PBODB170.INI.

    • Use the same syntax as existing entries in the DBMS Driver/DBMS Settings section of PBODB170.INI.

    • Include a section name for PBFunctions.

    For example, here is the relevant portion of an entry for a DB2/2 database:

    ;***********************************************
    ;DBMS Driver/DBMS Settings
    ;***********************************************
    [DB2/2]
    ...
    PBFunctions='DB22_FUNCTIONS'
    ...
  3. Edit the Functions section of PBODB170.INI to add an entry for your back-end DBMS.

    Make sure that you:

    • Follow the instructions in the comments at the end of PBODB170.INI.

    • Use the same syntax as existing entries in the Functions section of PBODB170.INI.

    • Give the Functions section the name that you specified for PBFunctions in the DBMS Driver/DBMS Settings entry.

    For example:

    ;***********************************************
    ;Functions
    ;***********************************************
    [DB22_FUNCTIONS]
    AggrFuncs=avg(),count(),list(),max(),min(),sum()
    Functions=curdate(),curtime(),hour(), ...
  4. Type a comma followed by the function name at the end of the appropriate function list, as follows:

    • Aggregate functions

      Add aggregate functions to the end of the AggrFuncs list.

    • All other functions

      Add all other functions to the end of the Functions list.

    Case sensitivity

    If the back-end DBMS you are using is case sensitive, be sure to use the required case when you add the function name. The following example shows (in bold) a new DB2/2 function named substr() added at the end of the Functions list:

    ;***********************************************
    	;Functions
    	;***********************************************
    	[DB22_FUNCTIONS]
    	AggrFuncs=avg(),count(),list(),max(),min(),sum()
    	Functions=curdate(),curtime(),hour(), substr()
  5. Save your changes to PBODB170.INI.