Skip to content

DF_DATABASE_TRIM_VARCHAR_VALUES

Determines whether values are trimmed when stored in a SQL varchar column.

Level

Database

Supported by

The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 6 and higher.

Type

Boolean, temporary

Access

Read/Write

Values

True or False

Syntax

Use cli.pkg
Get_Attribute DF_DATABASE_TRIM_VARCHAR_VALUES of {driverNumber} {databaseHandle} to {BooleanVariable}
Set_Attribute DF_DATABASE_TRIM_VARCHAR_VALUES of {driverNumber} {databaseHandle} to {BooleanVariable}

Driver Configuration Keyword

Trim_Varchar_Values

Remarks

Determines whether values are trimmed when stored in a SQL varchar column. By default, this is False.

SQL database servers usually have char and varchar column types for storing string data. Char and varchar types behave differently when it comes to padding and trimming spaces and in string comparisons. Behavior may differ between char and varchar, but also between different SQL database systems.

  • Char types are stored space padded in the database. In a char(10) column, when writing ‘Stephen’, it will be stored as ‘Stephen ’ (3 padding spaces) in the database.
  • Varchar types are not space padded in the database. In a varchar(10) column, when writing ‘Stephen’, it will be stored as ‘Stephen’ (7 chars). When writing ‘Stephen ’ (2 padding spaces), it will be stored as ‘Stephen ’ (2 padding spaces).

See Padding and Trimming in SQL Databases for more information.

The following sample code returns the setting of this attribute for the "MSSQLDRV" database on all connected servers, if the driver is loaded in the current program.

Function DriverIndex String sDriver Returns Integer
    String sCurrentDriver
    Integer iDriver iNumDrivers
    Get_Attribute DF_NUMBER_DRIVERS to iNumDrivers
    For iDriver from 1 to iNumDrivers
        Get_Attribute DF_DRIVER_NAME of iDriver to sCurrentDriver
        If (Uppercase(sDriver) = Uppercase(sCurrentDriver)) ;
            Function_Return iDriver
    Loop
    Function_Return 0
End_Function

Function DatabaseServerNumber String sDriver String sServerName Returns Integer
    Integer iDriver
    Integer iServer iNumServers
    // returns 0 if fails to get valid Server number
    Move 0 to iServer
    If (Uppercase(sDriver) <> "DATAFLEX") Begin
        Get DriverIndex sDriver to iDriver
        If (iDriver <> 0) Begin
            Get_Attribute DF_DRIVER_NUMBER_SERVERS of iDriver to iNumServers
            For iServer from 1 to iNumServers
                Get_Attribute DF_DRIVER_SERVER_NAME of iDriver iServer to sServer
                If (Uppercase(sServer) = Uppercase(sServerName)) Begin
                    Function_Return iServer
                End
            Loop
        End
    End
    Function_Return iServer
End_Function

Function DatabaseHandleForServer String sDriver String sDBServerName Returns Handle
    Handle hDatabase
    Integer iDBServer iDriverIndex
    // returns 0 if fails to get a valid handle for the database
    Move 0 to hDatabase
    Get DatabaseServerNumber sDriver sDBServerName to iDBServer
    If (iDBServer <> 0) Begin
        Get DriverIndex sDriver to iDriverIndex
        Get_Attribute DF_DATABASE_ID of iDriverIndex iDBServer to hDatabase
    End
    Function_Return hDatabase
End_Function

Function CheckDatabaseVarCharValueTrim String sDriver String sServer Returns Boolean
    Boolean bTrim
    Integer iDriverIndex
    Handle hDatabase
    Get DatabaseHandleForServer sDriver sServer to hDatabase
    If (hDatabase) Begin
        Get DriverIndex sDriver to iDriverIndex
        Get_Attribute DF_DATABASE_TRIM_VARCHAR_VALUES of iDriverIndex hDatabase to bTrim
    End
    Function_Return bTrim
End_Function