Help > Appendix >
Format Specifiers
PreviousNext

Content

Introduction

Most Tools / Programming language supports making SQL Query dynamic. However there will be a time when its not possible or hard. ZappySys drivers support unique way to make your SQL Query dynamic. You can use Function Placeholders anywhere in the SQL string and it will be replaced before its evaluated by Query Parser. For example using placeholder in your SQL as below pass current date / time in yyyy-MM-dd format.
select * from mytable where date='<<yyyy-MM-dd,FUN_GETDATE>>'

Step-By-Step

In this tutorial we will show you how to use static Placeholders to make things much cleaner and easier. You can pass input values for placeholders by Direct value. Here is just an example of static placeholder.
  1. Firstly, You need to Download and Install ZappySys ODBC PowerPack.
  2. After you install ZappySys ODBC Drivers you can use Driver functionality few ways (see below).
    1. Using ODBC DSN (User level DSN or System level DSN)
    2. Using Direct ODBC Connection String
    3. Connect client app/server from Windows, Linux, Mac OS to ZappySys Data Gateway using Microsoft SQL Server Driver (i.e. JDBC, ODBC, OLEDB ...)

  3. Regardless which method you choose from your App below steps will help you to configure your connectivity in correct way.
    If you choose to connect using direct ConnectionString then also try below Steps to create correct ConnectionString which you can copy from DSN Config Tool and paste inside your App / Programming Language Code.
  4. Type odbcad32.exe in your search box and launch the DSN Config utility.
  5. If you want access for yourself then stay of User DSN Tab. If you want grant access other users then go to System DSN tab. For SQL Server Integration go to System Tab and add new System DSN rather than User DSN. Click New button.
    ZappySys ODBC Driver - Open UI
  6. From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys JSON Driver].
    ZappySys ODBC Driver - Create JSON Driver
  7. Click on Finish button.
  8. Now, Move in Preview Tab, Enter or Modify query in editor and hit on Preview Button to see value of Placeholder. Use following query for test.
    SELECT * FROM $ 
    WITH (SRC='http://httpbin.org/get?mypara=<<ABCDEF123##,FUN_BASE64ENC>>')
    
    For more about function click here.
    Configure JSON Driver for Static Placeholder

Function List

Along with format specifiers you can also use functions. See below list.

Function name Description
FUN_READFILE Reads file content (input must be file path) and replace placeholder
Syntax: input_file_path,FUN_READFILE
FUN_TRIM Trim white spaces from start and end of input string
Syntax: input_string,FUN_TRIM
FUN_BASE64ENC Encodes input string to BASE64 format
Syntax: input_string,FUN_BASE64ENC
FUN_FILE_BASE64ENC Generates BASE64 encoded string of file data (Binary or Text)
Syntax: input_file_path,FUN_BASE64ENC
FUN_BASE64DEC Decodes input string from BASE64 format
Syntax: input_encoded_string,FUN_BASE64DEC
FUN_URLENC Encodes input string to percentage encoding format for URL (e.g. 1*&*2 is encoded to 1*%26*2)
Syntax: input_string,FUN_URLENC
FUN_URLDEC Decodes url encoded value to plain text
Syntax: input_string,FUN_URLDEC
FUN_GETDATE Returns current local date/time in a specified format
Syntax: [date_format,]FUN_GETDATE
FUN_GETDATE_UTC Returns current UTC date/time in a specified format
Syntax: [date_format,]FUN_GETDATE_UTC
FUN_TO_UTC_DATE Converts local date to UTC date (Time portion is removed)
Syntax: input_date_time,FUN_TO_UTC_DATE
FUN_TO_UTC_DATETIME Converts local date/time to UTC date/time
Syntax: input_date_time,FUN_TO_UTC_DATETIME
FUN_TO_LOCAL_DATE Converts UTC date to local date (Time portion is removed)
Syntax: input_date_time,FUN_TO_LOCAL_DATE
FUN_TO_LOCAL_DATETIME Converts UTC date/time to local date/time
Syntax: input_date,FUN_TO_LOCAL_DATETIME
FUN_MD5 Returns MD5 hash in Base64 format for specified input
Syntax: input_string,FUN_MD5
FUN_FILE_MD5 Returns MD5 hash in Base64 format for specified file content (input is file path)
Syntax: input_file_path,FUN_FILE_MD5
FUN_MD5HEX Returns MD5 hash in Hex format for specified input
Syntax: input_string,FUN_MD5HEX
FUN_HMAC_MD5 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the MD5 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA1 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA1 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA256 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA256 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA384 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA384 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA512 Returns a Base64 string of Hash-based Message Authentication Code (HMAC) by using the SHA512 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_MD5_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the MD5 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA1_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA1 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA256_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA256 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA384_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA384 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_HMAC_SHA512_HEX Returns a Hex formatted Hash-based Message Authentication Code (HMAC) by using the SHA512 hash function (Hash input data using user defined secret key)
Syntax: secret_key||input_string,FUN_HMAC_XXXX
FUN_JSONENC Encodes string for JSON value (e.g. New line is replaced by \r\n, double quote is replaced by \")
Syntax: input_string,FUN_JSONENC
FUN_JSONDEC Decodes JSON string (e.g. \r\n is replaced by New line, \" is replaced by double quote)
Syntax: input_string,FUN_JSONDEC
FUN_XMLENC Encodes string for XML value (e.g. > is replaced by &gt; , < is replaced by &lt;)
Syntax: input_string,FUN_XMLENC
FUN_XMLDEC Decodes XML value (e.g. &gt; is replaced by > , &lt; is replaced by <)
Syntax: input_string,FUN_XMLDEC
FUN_SEQUENCE Returns a unique number in a sequence (Previous number + 1)
Syntax: FUN_SEQUENCE
FUN_NEW_GUID Returns a unique GUID. Format can be blank or any valid format listed here (i.e. N, D, B, P, X)
Syntax: [format,]FUN_NEW_GUID
FUN_RANDOM_INT Returns a random integer number
Syntax: [max_int_value,]FUN_RANDOM_INT
FUN_RANDOM_STR Returns a random string of a specified length (If length is not specified, it returns max 10 chars long string)
Syntax: [max_string_length,]FUN_RANDOM_STR

Date Time Formatting

You can use DateTime format specifiers like below as long as DataType is DateTime. Click here to see full list of datetime format specifiers. Click Here to see list of predefined standard date time formats.

Example Sample Output
<<yyyy,FUN_TODAY>> 2016
<<yyyyMMdd,FUN_TODAY>> 20161231
<<MMM dd, yyyy,FUN_TODAY>> Dec 31, 2016
<<MMMM dd, yyyy,FUN_TODAY>> December 31, 2016
<<yyyy-MM-ddTHH:mm:ss.fffZ,FUN_TODAY>> 2016-12-31T23:59:59.999Z
<<yyy-MM-dd hh:mm:ss tt,FUN_TODAY>> 2016-12-31 11:59:59 PM

URL Encode/decode functions

Many times you have to pass parameters to your REST API Calls which needs to be in URL encoded format (Also known as percentage format e.g. %20). If you have this need then you can use FUN_URLENC or FUN_URLDEC specifiers.
Usecase: When you pass data in URL or POST body then you can use FUN_URLENC and FUN_URLDEC like below
UserID=<<myUserID,FUN_URLENC>> & Pass=<<myPassword,FUN_URLENC>>

Example Sample Output
<<1*&*2,FUN_URLENC>> 1*%26*2
<<1 2 3,FUN_URLENC>> 1+2+3
<<1+2+3,FUN_URLDEC>> 1 2 3
<<ABCDEF123##,FUN_BASE64ENC>> QUJDREVGMTIzIyM=
<<QUJDREVGMTIzIyM=,FUN_BASE64DEC>> ABCDEF123##

JSON Encode/decode functions

Many times you have to create simple JSON by inserting static placeholder into JSON template. ODBC Powerpack has functions to make sure your value is properly encoded as per XML rules.
Usecase: When you pass JSON data in API POST body which needs JSON Encoded value, then you can use FUN_JSONENC like below. Tab is encoded as \t, new line as \r\n and doublequote as \"
<<sam's club,FUN_JSONENC>>

Example Sample Output
SELECT args_data FROM $
WITH(
     Src='http://httpbin.org/get?data={notes:"<<sam's club,FUN_JSONENC>>"}'
)				
				
{notes:"sam\u0027 club"}

XML Encode/decode functions

Many times you have to create simple XML by inserting static placeholder into XML template. ODBC Powerpack has functions to make sure your value is properly encoded as per XML rules.
Usecase: When you pass XML data in API POST body which needs XML Encoded value, then you can use FUN_XMLENC like below.
<ErrorMessage><<MyString,FUN_XMLENC>></ErrorMessage>

Example Sample Output
<<"1<2<3",FUN_XMLENC>> &quot;1&lt;2&lt;3&quot;
<<&quot;1&lt;2&lt;3&quot;,FUN_XMLDEC>> "1<2<3"

MD5 Hashing functions

Many times you have to send value HASH to your API server. You can use following Hashing function to hash your input value.
Example Sample Output
<<ABC,FUN_MD5>> kC+90rHfDE9wtKXSNSXpMg==
<<ABC,FUN_MD5>> kC+90rHfDE9wtKXSNSXpMg==
<<C:\data\MyFile.txt,FUN_MD5>> where content of that File is ... ABC kC+90rHfDE9wtKXSNSXpMg==
<<ABC,FUN_MD5HEX>> 902FBDD2B1DF0C4F70B4A5D23525E932

HMAC functions

Many times you have to send secure data by using Hash-based Message Authentication Code (HMAC). Below section summarizes various functions you can use to generate secure data. Basically you have to supply your Secret Key (Must be Base64 format) and Value along with FUNCTION name.
Example Sample Output
<<a2V5MTIz||Hi my name is A,FUN_HMAC_MD5>>

where a2V5MTIz is secret key used to generate HMAC
EICjBYkqIW6HpU0VOkIABQ==
<<a2V5MTIz||Hi my name is A,FUN_HMAC_MD5_HEX>>

where a2V5MTIz is secret key used to generate HMAC
1080a305892a216e87a54d153a420005
<<a2V5MTIz||POST\nx-csod-api-key:ABCD12345\nx-csod-date:2012-09-08T11:27:32.000\n/services/api/sts/session,FUN_HMAC_MD5_HEX>>

where a2V5MTIz is secret key used to generate HMAC. Notice that we have used \n in the input string. You can use \r or \n in the input string
RMZtvDgBFOubtNOKzQcbB5K6G93nVeZEu1uVnXIvkTYcaM81s1gexu+psi++H6XxQ3imJwaIhv+mOE0G3UKWdg==

File Operation functions

Many times you have to read file content and supply it part of some property. Use below function to perform indirect read.
Example Sample Output
<<c:\url.txt,FUN_READFILE>>

where data inside c:\url.txt contains some URL
http://myhost

Using direct static placeholders

New Version introduced new feature to apply static placeholders on string.
To apply placeholder function on direct string you must enclose data in double angle brackets (not curly brackets) and function name must start with FUN_ (e.g. <<myuser1:mysecretpass123,FUN_BASE64ENC>>).

Usecase: You want to encode userid and password to base64 format without extra steps to specifying static placeholder like below
Authorization: <<mysecretpass123,FUN_BASE64ENC>>

Above string will produce following result.
Authorization: bXlzZWNyZXRwYXNzMTIz


Copyrights reserved. ZappySys LLC.