SQL: replace parameters

[ale870]

Hello, I have another question :-(



I'm working with SQL (Oracle via ODBC), and I need to create parametrized queries. The problem is I cannot remember how to substitute params.



Example:

Code Select Expand

(setq sqlString "select * from table1 where field1 = ? and field2 = ?")

Now I want to do this (method execute is just to simplify the example):

Code Select Expand

(execute sqlString (value1 value2) )


It means, when I created base SQL, I could send data. Furthermore, can this system automatically send params directly via odbc (not like a simple string replacement, but with an automatic management of apex ' ' for string, NULL values, etc...).



Thank you!

[cormullion]

I think there are various ways. I use this:

Code Select Expand

(define (select-query columns table details)
  (let ((sql-query
          (string {select } (join columns {,}) { from } table (if details (string details) {})))
        (sql-results '()))
    (set 'sql-results (sql3:sql sql-query))))


which gets called like this:

Code Select Expand


(set 'required-columns '({post-name} {post-id} {post-body}))
(set 'results (select-query required-columns "posts" (string { where post_id = '} post-id {'} etc....


or you could use format:

Code Select Expand


(sql3:sql (format "update posts set post_fetch_count = post_fetch_count + 1 where post_id =  '%s';" post-id))


where the value of post-id is incorporated into the string built by format using '%s'.



There may be better ways - I just try stuff until I find something which works... :)

[ale870]

Hello,

thank you! I will try them tomorrow!



Today, after my post, I created this piece of code to solve the problem:

Code Select Expand


(define (replace-sql argSql argReplace , (delimiter "") )
(dolist (i argReplace)
(cond
(
(= (i 1) 'string)
(setq delimiter "'")
)
);cond

(replace (i 0) argSql (string delimiter (i 2) delimiter)  )
);dolist
)


How to use it? It's easy:

Code Select Expand


@return SQL string with all terms replaced.

 Replace one or more sql parameters with the real values.

 @example
In this example, the original sql string is "sql". The variable "val"
contains two lists that contain needed info to make the replacement.
":t1" is INTEGER and the value is "100"
":t2" is STRING and the value is "Alessandro"

  (setq sql "select * from myTable where t1 = :t1 and t2 = :t2")
 
  (setq val '(
  (":t1" integer "100")
  (":t2" string "MyName")
  ) )

(replace-sql sql val)


Since I'm not too much expert using NewLisp (not yet ;-)  ) can you take a look to my code and suggest me if something could be optimized?

[ale870]

I think there are various ways. I use this:

Code Select Expand

(define (select-query columns table details)
  (let ((sql-query
          (string {select } (join columns {,}) { from } table (if details (string details) {})))
        (sql-results '()))
    (set 'sql-results (sql3:sql sql-query))))


which gets called like this:

Code Select Expand


(set 'required-columns '({post-name} {post-id} {post-body}))
(set 'results (select-query required-columns "posts" (string { where post_id = '} post-id {'} etc....


or you could use format:

Code Select Expand


(sql3:sql (format "update posts set post_fetch_count = post_fetch_count + 1 where post_id =  '%s';" post-id))


where the value of post-id is incorporated into the string built by format using '%s'.



There may be better ways - I just try stuff until I find something which works... :)

Hello cormullion,

can you explain me the usage of { } ?

Thank you!

[m i c h a e l]

Hi ale870!



You could shorten this code:

Code Select Expand

     (cond
        (
           (= (i 1) 'string)
           (setq delimiter "'")
        )
     );cond


by doing one of the following:

Code Select Expand

(if (= (i 1) 'string) (setq delimiter "'"))

;; or

(and (= (i 1) 'string) (setq delimiter "'"))


Also, you taught me something with your code: the variables after the comma (,) in a function parameter list can have default values! Thanks for the education :-)



m i c h a e l



P.S. Welcome to the wonderful world of newLISP!

[newdep]

Yes your right.. I was looking at that code part two ;-)



(define (one, (two 2)) two)  



the initial value is new to me too, I like that (one)



Norman.

[ale870]

Hi ale870!



You could shorten this code:

Code Select Expand

     (cond
        (
           (= (i 1) 'string)
           (setq delimiter "'")
        )
     );cond


by doing one of the following:

Code Select Expand

(if (= (i 1) 'string) (setq delimiter "'"))

;; or

(and (= (i 1) 'string) (setq delimiter "'"))


Also, you taught me something with your code: the variables after the comma (,) in a function parameter list can have default values! Thanks for the education :-)



m i c h a e l



P.S. Welcome to the wonderful world of newLISP!

Thank you for your suggestion! I will do it immediately! :-)

[cormullion]

The { } are like " " - they're string delimiters. I prefer to use { } because they're easier to see, the editor I use matches them, and you don't need to double-escape any backslashes. I use " " when I want to insert newlines: "n".



When I'm working with regular expressions, I often use [text] and [/text] as delimiters. That way, all the { } and " " characters can be used without escaping/matching...

[ale870]

The { } are like " " - they're string delimiters. I prefer to use { } because they're easier to see, the editor I use matches them, and you don't need to double-escape any backslashes. I use " " when I want to insert newlines: "n".



When I'm working with regular expressions, I often use [text] and [/text] as delimiters. That way, all the { } and " " characters can be used without escaping/matching...

Thank you! (I forgot it!)