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 all

(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 all

(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 all

(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 all

(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 all

 (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 all

(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 all

 @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 all

(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 all

(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 all

 (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 all

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

by doing one of the following:

Code: Select all

(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 all

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

by doing one of the following:

Code: Select all

(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!)