Connecting to different database servers, advanced ways to query your database
Posted at 2004-11-15 by Michael Stenitzer, Last updated at 2004-11-29 by Marcus Agehall
Roxen and ChiliMoon stores some stuff in its local database:
Argument cache, cimg cache, precompiled security patterns (eg htaccess), gbutton cache, gtext cache, roxen module list, precompiled pike files, session cache, update information, etc...
Most of which is information that is easily reconstructed, or doesn't matter much if it gets lost. Therefore this data does not have to be backuped.
One important point here is that the <accessed/> tag may store information in the database. If the database gets zapped, your accesscounters may get zapped as well. This may be bad, so to make sure that you don't suffer any data loss, please back up the internal mysql-files as well.
Applies to: Roxen , ChiliMoon
Posted at 2004-11-15 by Michael Stenitzer, Last updated at 2005-02-14 by
No you can't. Developers have chosen MySQL for many reasons, not the least of them MySQL being the fastest in the way it is used (for caching purposes eg.) It is not recommended to use the internal database for your own project, because there are no backups made of the database.
You are not forced to use MySQL for your own data, it just uses it for its own purposes. You are free to use whatever database server you want for your own data.
Applies to: Roxen , ChiliMoon
Posted at 2004-11-17 by Michael Stenitzer, Last updated at 2005-12-02 by Sascha Nemecek
Yes. "Create new database" in the administration interface's DBs tab. Choose type "external" and insert an Url in the form of
Unix: mysql://user:password@:/path/to/mysql/socket/db-name
Win: mysql://user:password@:C_/path/to/configurations/_mysql/pipe
Roxen uses socket when connection is made to mysql://localhost/db and if the operating system permit it.
Applies to: Roxen , ChiliMoon
Posted at 2004-11-20 by Michael Stenitzer, Last updated at 2004-12-05 by Michael Stenitzer
You can access the the internal databases from the command-line MySQLl program if you have read/write access to the mysql socket file (which is located in the "_mysql/" sub-directory of the configuration directory usally accessed as "../configurations" relative to the server directory).
Start the MySQL command-line client like this:
mysql --socket=/usr/local/roxen/configurations/_mysql/socket -u rw
An alternative is to connect via TCP-IP. Connect to the MySQL server via socket file. Create an user with the rights for external access (see MySQL manual) and change the bind addresse in /usr/local/roxen/configurations/_mysql/my.cfg to
bind-address = 192.168.0.13 # (or whatever your IP is).
port=3307 # optionally, if you want to change from the standard port 3306
Another option to change the bind address and port is to set the environmental variables in roxen's startup-script
export MYSQL_HOST=192.168.0.13 # (optionally, default 127.0.0.1)
export ROXEN_MYSQL_TCP_PORT=3306
Applies to: Roxen , ChiliMoon
Posted at 2004-11-20 by Michael Stenitzer
The cleanest way is to set up your database links inside the administration interface, so you don't have to embed any database names or passwords in the script itself:
<?pike
object sq=DBManager.get("mydb");
array result=sq->query("select id, name, address from mytable");
write("%d records:<br>\n", sizeof(result));
foreach (result, mapping row)
{
write("ID: %s<br>Name:%s<br>Address:%s<br><br>\n",
row->id, row->name, row->address);
}
?>
Applies to: Roxen , ChiliMoon
Posted at 2004-11-20 by Michael Stenitzer, Last updated at 2004-12-02 by Michael Stenitzer
Roxen older than 4.0 shipped with MySQL 3.23 but should also work with MySQL 4.0 (but not later). You have to be careful to compile Pike earlier than 7.4 using MySQL 3.23 client libraries - they connect just fine to a MySQL >=4.0 server. Roxen 4.0 ships with MySQL 4.0. Pike 7.4 and later compiles with MySQL 4.0 / 4.1 client libraries.
ChiliMoon has the same requirements for MySQL version as Pike CVS does. Which means MySQL >=4.0 server for now.
Applies to: Roxen , ChiliMoon
Status: Answer to be confirmed
Posted at 2004-11-20 by Michael Stenitzer
Applies to: Roxen , ChiliMoon
Posted at 2004-11-20 by Michael Stenitzer
Applies to: Roxen , ChiliMoon
Posted at 2004-11-21 by Michael Stenitzer
Internally the column variables are "UNDEFINED" if the value in the row is NULL. Undefined variables in RXML tested with <if variable="sql.col">/ will return false for a NULL value in the row's column "col", and true for an empty string in this column. Inserting an undefined variable in RXML will give an empty string as result.
Applies to: Roxen , ChiliMoon
Posted at 2004-11-21 by Michael Stenitzer
In your main page write
<img src="getimage?id=12332" />
The getimage.html page that looks like
<header name="Content-Type" value="image/jpeg" /><emit source="sql" query="select * from binfiles where id=&form.id"><img src="&sql.binary;"/></emit>
Important note: write this in one line so you don't add any extra linefeeds to the output image.
Alternative option:
<emit source='sql' query='select imagedata from images where id=37'>
<cimg data='&sql.imagedata:none;'/>
</emit>
This solution is worse from a scalability viewpoint, since using the cimg tag produces a copy of the image in the image cache
Applies to: Roxen , ChiliMoon
Posted at 2004-11-27 by Michael Stenitzer
If you install Roxen / ChiliMoon from source you have to ensure to have your native database client libraries installed (and Pike can find them) before you compile Pike.
To verify if your database drivers are available in your Pike, use the Pike module list task in the Roxen / ChiliMoon Administration interface: tasks -> debug_information -> features show the available Pike modules. To get new modules included, you have to install your database client libraries first and either reinstall Roxen or recompile Pike.
On linux you can directly test if your database libraries are available (example checks for Oracle):
ldd /<roxen-home>/pike/lib/pike/modules/___Oracle.so
If you are using other OS then you have to use truss(1) or some equivalent command and try to connect to the oracle from hilfe:
# cd /<roxen-home>/server/bin
# truss ./roxen
Pike v7.0 release xyz running Hilfe a.b (Incremental Pike Frontend)
object db=Sql.sql("oracle:///<sid>");
Native database drivers are supported for Oracle, Sybase, Msql, Postgres, Informix. Others can be used with an ODBC driver.
Applies to: Roxen , ChiliMoon
Posted at 2004-11-30 by IzNoGooD, Last updated at 2004-12-05 by Michael Stenitzer
It is possible, however it is not recommended in a production server. Always keep in mind that there might be no backups made of the internal database.
Applies to: Roxen , ChiliMoon
Posted at 2005-05-02 by Sascha Nemecek, Last updated at 2005-07-21 by Sascha Nemecek
if you have access to the startup scripts of the Roxen server, simply set and export the following environment variables
export ORA_NLS33=/usr/local/oracle/app/oracle/product/8.1.7/ocommon/nls/admin/data
export LNG=american_america.WE8ISO8859P1
or add them to the local/environment.
Applies to: Roxen (4.0) , ChiliMoon
Posted at 2006-02-23 by Marc Dirix, Last updated at 2006-02-24 by Sascha Nemecek
You can use <else>/ directly behind <emit>/ and <sqlquery>/. With <sqlquery>/ the statements between the <else>/ tags are executed when the query fails. Furthermore you can use <then>/ when the query succeeds.
<sqlquery query="foo" />
<then>
Success handling code.
</then>
<else>
Error handling code.
</else>
With <emit>/ the statements between the <else> tags are executed when the query returns no result whereas the contents of the <then>/ container are executed after the last returned result.
<emit source="sql" query="foo" />
Code for the queryresult.
</emit>
<then>
Success handling code.
</then>
<else>
Sorry, no results.
</else>
Note that the <else>/ tag will not suppress SQL error messages.
Applies to: Roxen , ChiliMoon