[ Back to main index ] | [ Show all FAQs on a single page ]

Connecting to And Working With a Database

Connecting to different database servers, advanced ways to query your database

Why is Roxen using its own "internal" MySQL setup?

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

Can I use a different database server for the internal caching?

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

Can I connect to a Database-Server using a socket file?

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

How can I access the internal database?

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 =               # (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=            # (optionally, default 
 export ROXEN_MYSQL_TCP_PORT=3306 

Applies to: Roxen , ChiliMoon

How can I connect to a database from an embedded Pike script?

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:

 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

What MySQL versions can be used?

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 by Sascha Nemecek (2005-05-21)

If you receive the following message:

Couldn't reconnect to SQL-server Client does not support authentication protocol requested by server; consider upgrading MySQL client

You must either update your Pike version to support the MySQL 4.1 authentication or tell mysql to use the old password authentication: mysqld --old-passwords

Reply by Sascha Nemecek (2008-02-08)

When trying to access a MySQL Server >= 4.1 using newer user account, you have to recreate the passwords with MySQL's OLD_PASSWORD() function.

mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd'); 

Posted by Sascha Nemecek (2008-02-08)

At least under Windows, Roxen seems to have problems with UTF8 character encoding. Try disabling it in my.ini as a quick workaround:

# default-character-set=utf8 
[hide comments]

Is there a way to set an option somewhere in Roxen or the environment so that Oracle date/time fields are returned as ISO standard dates (yyyy-mm-dd 24:00:00)?

Posted at 2004-11-20 by Michael Stenitzer

  • insert <sqlquery host="your_host" query="alter session set nls_date_format='YYYY-MM-DD'" /> somewhere at the top of every page, or
  • if you have access to the startup scripts of the Roxen server, simply set the environment variable NLS_DATE_FORMAT there, or
  • or even better, just add a line "export NLS_DATE_FORMAT=..." to local/environment.

Applies to: Roxen , ChiliMoon

I can not access a certain database from my site

Posted at 2004-11-20 by Michael Stenitzer

  • Check if you have access to the database from the DB manager (DB tab in the admin interface)
  • Check if your site has according rights (R=Read and/or W=Write) in the administration interface's DB manager
  • You might also check if you have access to DB from your host in general and from Pike in special

Applies to: Roxen , ChiliMoon

How are NULL values returned from DB queries handled in RXML?

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

How do I get binary data (eg. an image) out of my database?

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;'/> 

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

How can I use other database systems than MySQL?

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 by oNNoZeLe (2005-12-13)

If you want to use a oracle database within your network and you are using roxen on a windows OS you have to specifiy a DSN in the ODBC settings.

Then Roxen pulls this from the Registry.

Then define the DB in roxen with the DSN name. To keep it clear DON'T give the DSN the same name as the database.

odbc://username:pass@<dnsname>/database so like this:


this works for me.


Posted by oNNoZeLe (2012-08-24)

When using Oracle as DB i noticed the connect string should end with /


that should be the connect string.

when omitting the last / one gets:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

[hide comments]

Can I use the internal database to store my application data?

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

How can I change Oracle's character set? I do not get any "umlauts".

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

How do I catch sqlqueries?

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" /> 
  Success handling code.  
  Error handling code. 

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. 
  Success handling code.  
  Sorry, no results. 

Note that the <else>/ tag will not suppress SQL error messages.

Applies to: Roxen , ChiliMoon

[ Back to main index ] | [ Show all FAQs on a single page ]