Forum OpenACS Q&A: Getting the SQL from the .XQL

Posted by Benjamin Jensen on 04/12/03 12:20 AM

I am currently working on porting packages from ACS to OpenACS at my workplace. As I do so, I am making the packages support Postgresql as well as Oracle. I am running into the problem where I have procedures that require an SQL string be passed to them. How do I get the querytext from the .xql file so I can pass it to a procedure? The first time I ran across this I just left that query in the .tcl file because it was a generic query that worked in both postgresql and oracle. Now I have a query that has to have to versions for the different databases and therefore must be in .xql files. Any help would be greatly appreciated. Thanks

2: Re: Getting the SQL from the .XQL (response to 1)

Posted by John Sequeira on 04/12/03 12:57 AM

If statement_name is the name of the query you're trying to resolve, use:

set pre_sql "";  #default value
set full_statement_name [db_qd_get_fullname $statement_name]
set sql [ db_qd_replace_sql $full_statement_name $pre_sql]

You have to do this in the file with the same name as the xql so that it resolves correctly.

3: Re: Getting the SQL from the .XQL (response to 1)

Posted by Dave Bauer on 04/12/03 01:08 AM

You can use the db_map procedure.

set sql_fragment [db_map sql_fraq ""]

And then put the part of the sql into either the generic XQL file or the database specific ones.

4: Re: Getting the SQL from the .XQL (response to 1)

Posted by Jade Rubick on 04/14/03 11:25 PM

Benjamin, please post if you port any packages that were publically available. We're in the midst of the same porting process.

5: Re: Getting the SQL from the .XQL (response to 1)

Posted by Benjamin Jensen on 04/15/03 10:25 PM

Thank you all for the replies. Sorry It's taken so long to respond. I don't work Mondays (I'm just a student working part-time). I tried what John suggested and I got a "no such variable" error for the $statement_name (and, yes, I did replace statement_name with the name of the query). I may have done something wrong, though.

The db_map procedure that Dave suggested, however, worked great! Thanks again.

ps to Jade: I am not working on any packages that were publicly available at the moment, just packages that were written for internal use. I do not know if/when I will be.

6: Re: Getting the SQL from the .XQL (response to 2)

Posted by Paul Cannon on 04/15/03 10:40 PM

This won't work from the file with the same name as the xql, because db_qd_get_fullname requires a certain level between it and that file. If you do want to use it directly, pass a 0 as the second parameter so that it can uplevel to the right place. Example:

set full_statement_name [db_qd_get_fullname $statement_name 0]
set sql [db_qd_replace_sql $full_statement_name $pre_sql]

Of course, the db_map option is probably a better one.

paul