sql.query.builder ( )

Function stats

Average user rating
37
362
9999
Support
FileMaker 10.0 +
Date posted
05 December 2013
Version
1
Recursive function
Yes

Author Info
 Fabrice

74 functions

Average Rating 4.4

author_avatar



 

Function overview

Prototype

sql.query.builder  ( _query;   _param.list )


Parameters

_query  a SQL query with # wildcards


_param.list  an ordered list of parameters


Description

Tags:  Sql   dev  

Helps building naming independent sql queries using # wildcards.
Will replace each # by the corresponding parameter in _param.list
Double quotes are added to protect the query from reserved words conflicts.

Examples

Sample input

sql.query.builder ( "SELECT #,# FROM # WHERE #=?" ; list ( "id" ; "whateverfield" ; "theTableName" ; "anotherFieldName" ))


Sample output

SELECT "id","whateverfield" FROM "theTableName" WHERE "anotherFieldName"=?

 

Function code

/* sql.query.builder
by Fabrice Nordmann, 1-more-thing
http://www.1-more-thing.com
Twitter: 1morethingtweet

v.1 dec 2013

Helps building naming independent sql queries using # wildcards.
Will replace each # by the corresponding parameter in _param.list
Double quotes are added to protect the query from reserved words conflicts.

Example :
sql.query.builder ( "SELECT #,# FROM # WHERE #=?" ; list ( "id" ; "whateverfield" ; "theTableName" ; "anotherFieldName" ))

-> SELECT "id","whateverfield" FROM "theTableName" WHERE "anotherFieldName"=?

so you can pass the result to ExecuteSQL.

*/

Let ([
    _pos = Position ( _query ; "#" ; 0 ; 1 )
];
    Case ( _pos ; Left ( _query ; _pos - 1 ) & Quote ( GetValue ( _param.list ; 1 )) & sql.query.builder ( Replace ( _query ; 1 ; _pos ; "" ) ; RightValues ( _param.list ; ValueCount ( _param.list ) -1 )) ; _query )
)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/362

    Prototype: sql.query.builder( _query; _param.list )
    Function Author: Fabrice (http://www.fmfunctions.com/mid/37)
    Last updated: 05 December 2013
    Version: 1

*/
// ===================================

 

 

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (31)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Interface  (15)
Layout  (15)
Text  (14)
Variables  (12)
Layout Objects  (11)
Filter  (11)
Design  (10)
Array  (7)