PlugIns.JdbcSlim.UserGuide.9NullTrailingSpacesAndEmptyStrings

PlugIns JdbcSlim UserGuide




To insert or update null values define input parameters and use "?" syntax.

Specify null as #null#

To insert leading and trailing Spaces use the !- text -! syntax

The text Denver below has 5 leading and two trailing spaces
The text Bob below has 5 trailing spaces

Inline Parameter

SQLCommand TestDatabase insert into TestData values (DEFAULT, ?{Name:12=more}, '%Phone%', '%City%', '%Profession%') dbgetgeneratedkeys
SCOPE_IDENTITY()? City Name Phone Profession Count?
$ID3= Paris #null# 112 1
$ID2= Denver Bob 6789 1

Alternative locations for null


Inline default value as null
Inline default value with leading and trailing spaces

SQLCommand TestDatabase insert into TestData values (DEFAULT, ?{Name:12=#null#}, '%Phone%', ?{City:12= Kathmandu }, '%Profession%') dbgetgeneratedkeys
SCOPE_IDENTITY()? Phone Profession Count?
$ID4= 009771 Sherpa 1

Parameters in Properties

Define Properties inParameters
key value
Name I:1:12:0
City I:2:12:0

SQLCommand TestDatabase insert into TestData values (DEFAULT, ?, '%Phone%', ?, '%Profession%') dbgetgeneratedkeys dbQueryParameters=inParameters
SCOPE_IDENTITY()? City Name Phone Profession Count?
$ID1= Berlin #null# 12345 1

null as default in Properties
leading and trailing spaces are lost!

Define Properties myDefaults
key value
Name #null#
City Pune

SQLCommand TestDatabase insert into TestData values (DEFAULT, ?, '%Phone%', ?, '%Profession%') dbgetgeneratedkeys dbQueryParameters=inParameters inputDefaults=myDefaults
SCOPE_IDENTITY()? Phone Profession Count?
$ID0= 0091 1

To test null values in output specify null as #null#

To test leading and trailing spaces use the !- text -! syntax or regex '=~/\A text \z/

To test for an empty string use regex =~/\A\z/

Define this pattern in a variable it will likely changes in the future to something more readable

variable defined: EmptyString==~/\A\z/
SQLCommand TestDatabase select City, Name, length(City) as CityLength, length(Name) as NameLength, Profession from TestData where ID ='%ID%'
ID City? CityLength? Name? NameLength? Profession?
$ID1 =~/\ABerlin\z/ 6 #null# #null#
$ID2 =~/\A Denver \z/ 13 Bob 8 =~/\A\z/
$ID3 =~/\AParis\z/ 5 #null# #null#
$ID4 Kathmandu 11 #null# #null# Sherpa
$ID0 Pune 4 #null# #null#

Configuration options

if you must insert the string #null# in the database you can define a different null pattern with the property inputNullString

Define Properties inParameters
key value
Name I:1:12:0
City I:2:12:0

SQLCommand TestDatabase update TestData set Name = ?{Name:12}, City = ? where ID ='%ID%' inputNullString=--null-- dbQueryParameters=inParameters
ID City Name Count?
$ID2 --null-- #null# 1

SQLCommand TestDatabase select City, Name, length(City) as CityLength, length(Name) as NameLength from TestData where ID ='%ID%'
ID City? CityLength? Name? NameLength?
$ID2 #null# #null# #null# 6

You can also change the output format for null values with the property outputNullString

SQLCommand TestDatabase select City, Name, length(City) as CityLength, length(Name) as NameLength from TestData where ID ='%ID%' outputNullString=++null++
ID City? CityLength? Name? NameLength?
$ID2 ++null++ ++null++ #null# 6

Contents: