plainblack.com
Username Password
search
Bookmark and Share

SQLExt - SQL macro for external databases (database links) version 7 compatible

This macro extends SQL.pm macro to execute query to an external database. You can also execute not-returning query like delete, insert and update.

It's forward compatible with SQL macro, so you can write something like this

^SQLExt("select count(*) from users","There are ^0; users on this system.");

If you have defined some databaseLink, you can use them too like this:

^SQLExt("select * from table1","^0;-^1;","asdfasdf")

where asdfasdf is the databaseLinkId.

The complete syntax of this macro is:

^SQLExt($sql_statement,[$format],[$databaseLink])

where

  • $sql_statement: is a standard SQL query to execute
  • $format: is the format for output result
  • $databaseLink: is the id of the databaseLink to use

To install it copy SQLExt.pm into lib/WebGUI/Macro. Then open your site config file and add to the "macros" declaration

            "SQLExt" : "SQLExt",

 

NOTE: I will update this shortly (next day or two) to have a Help module included. 

System RequirementsPlease be advised: this contribution was tested with something older than WebGUI 7.5. When this contribution was uploaded there was no field for the author to fill out regarding it's requirements.
Comments
4jws: "
Could you tell me how secure it is to do this?

I am intrested in using this feature but am concerned with security. 
"
4isaac: "
Heh.  This is kind of a loaded question.  Security is a system.  If you use the SQLExt macro for an external website where the content editors are trusted, then you will be fine.  If you allow many people to edit content, then the risk is higher.  Since SQLExt uses the same database handles as the SQLReport for executing remote queries, the risk should be no higher then the already built in tool, SQLReport.

However, both of these tools can become risky when you combine them with the other flexible features of WebGUI;  with the FormParam macro, for example, you can pull form/query info into the SQL you are executing (in both SQLExt and SQLReport) which then makes the whole story much more risky as you are providing a web-accessible input into the SQL.   Since all this is possible by a content manager and you can control what macros/assets are available to use, you have to weigh the trust in your users (content managers) with the security you've implemented using the configuration of the system.   

 All this said, I don't believe SQLExt gives you any more ability to hurt yourself than is already provided by WebGUI.

"
4elnino: "
hello. I'm having a heck of a time getting this to work:

^SQLExt("select count(*) from devices;","","BUILDWIZARD");


I keep getting the following error: Can't call method "db" on an
undefined value at /data/WebGUI/lib/WebGUI/Macro/SQLExt.pm line 27.


My databaselinks work, Ihave sqlreports running off of them. My queries are select count(*) type queries. 

Thoughts? I'm on 7.2.1

LN 
"
4isaac: "
Sorry, you need to use the Database Link ID.  Not the name.  You can see this on the edit page of the database link.


 So you need something like: 

^SQLExt("select count(*) from devices;","^0;",9PPfF5ddEEkWTkwa630eLw);


 Note the ^0;  to output the first column.

^1; for the second column,

^2; for the third, and so on... 
"
4elnino: "
Grr. I can't believe that's been the problem all this time. Thank you so much for this cool macro. and for responding!


One more question, Does this macro return an array so then I can
go thru each row using a while statement? Or is there a way to make the
second parameter create an array? kinda like this:

$myarray = ^SQLExt("select field1,field2,field3 from devices;","^0; ^1; ^2;",9PPfF5ddEEkWTkwa630eLw);

    while (my ($field1, $field2,
$field3) = $myarray->array) {

       //I need to manipulate the output
depending on values and such, not just a straight output for each row


    }

Hopefully that makes sense? 
"
2isaac: "
So the second parameter is how to output the sql. Where each parameter (^0; ^1; ^2; ^3; etc...) is the column. This parameter is repeated for each row. So to shove the data into javascript might try something like this in a template:

<script> 

var myarray1=new Array();

var myarray2=new Array();

var myarray3=new Array();

 

 &#94;SQLExt("select field1,field2,field3 from devices;"," 

myarray1.push('&#94;0;');

myarray2.push('&#94;1;');

myarray3.push('&#94;2;');

",9PPfF5ddEEkWTkwa630eLw);

</script>

 

This will cause you to end up with three arrays containing the values for each of the three columns, where the length of each array will equal the number of rows returned from the SQL query. 
"
DownloadSQLExt.pm SQLExt.pm
LinksNo Support Offered
Statistics Downloads: 607
Views: 5329
Rating: 4
Updated: 9/13/2006
Keywords macros
NavigationBack to the Bazaar
© 2018 Plain Black Corporation | All Rights Reserved