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:
(setq sqlString "select * from table1 where field1 = ? and field2 = ?")
Now I want to do this (method execute is just to simplify the example):
(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!
I think there are various ways. I use this:
(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:
(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:
(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,
thank you! I will try them tomorrow!
Today, after my post, I created this piece of code to solve the problem:
(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:
@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?
Quote from: "cormullion"
I think there are various ways. I use this:
(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:
(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:
(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!
Hi ale870!
You could shorten this code:
(cond
(
(= (i 1) 'string)
(setq delimiter "'")
)
);cond
by doing one of the following:
(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!
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.
Quote from: "m i c h a e l"
Hi ale870!
You could shorten this code:
(cond
(
(= (i 1) 'string)
(setq delimiter "'")
)
);cond
by doing one of the following:
(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! :-)
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...
Quote from: "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...
Thank you! (I forgot it!)