Connecting to Your Database
Appendix

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

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

   • Use the File Editor in PowerBuilder. (For instructions, see the the section called “Using the file editor” in Users Guide.)

   • Use any text editor outside PowerBuilder.

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.

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

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

   • Use the File Editor in PowerBuilder. (For instructions, see the the section called “Using the file editor” in Users Guide.)

   • Use any text editor outside PowerBuilder.

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.