Database

Questions:

• How do I connect to an ODBC database in the administrator interface?
• How do I connect to an Oracle database in the administrator interface?
• How do I use CLOB:s with Oracle?
• How can I use the MySQL command-line prompt to administrate the internal Roxen database?
• Why can't I insert large data into an external MySQL database?
• How can I connect to a MS SQL 2000 server from Roxen CMS on Linux?
• How do I run the internal MySQL as user different to the one that started Roxen?

How do I connect to an ODBC database in the administrator interface?

Create a remote db-connection with the following syntax: odbc://<user>:<pass>@<dsn-name>/<db-name>

How do I connect to an Oracle database in the administrator interface?

If you are using Unix and are able to use the native Oracle connection follow the steps below. Otherwise like on Windows use an ODBC connection instead.

1. Install Oracle client libraries

2. Add Oracle environment variables in the roxen/local/environment file. See the example below:
   

   ORACLE_HOME=/export/oracle8 export ORACLE_HOME ORACLE_SID=TEST export ORACLE_SID NLS_LANG=american_america.WE8ISO8859P1 export NLS_LANG

3. Restart Roxen

4. Create a remote db-connection with the following syntax:
   

   oracle://<user>:<pass>@<oracle_sid>

   
   or if you are using tns names:
   

   oracle://<user>:<pass>@<tns_name>

How do I use CLOB:s with Oracle?

In theory, there is no difference in using CLOBS apart from other datatypes. However Oracle has a 4000 byte limit on the size of your SQL query, which often is too short when inserting and updating clobs. To get around this, you can put your data in a variable and use variable bindings instead, like so:
<set variable='var.foo' value='texttexttext' />
<sqlquery query='insert into mytable VALUES (4,:foo,:bar)'
          bindings='foo=var.foo,bar=form.bar' />

Check the documentation for <sqlquery /> and <emit sql></> for further reference.
Variable bindings are supported in 3.3 release 3 and later.

How can I use the MySQL command-line prompt to administrate the internal Roxen database?

Unix:

path/to/server-x.x.x/mysql/bin/mysql -u rw -S path/to/configurations/_mysql/socket

Windows:

path\to\server-x.x.x\mysql\bin\mysql -u rw --pipe --socket=C_/path/to/configurations/_mysql/pipe

Note that the socket name should be the full path including the drive prefix but with : written as _ and with / as the directory separator.

Why can't I insert large data into an external MySQL database?

From the MySQL documentation:

"The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you can actually transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size, but you must do so on both the server and client ends. See section 10.1 Tuning server parameters."

"max_allowed_packet
The maximum size of one packet. The message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use."

So if you add a line like
max_allowed_packet = 4M
to the file my.cnf everything should work fine.

How can I connect to a MS SQL 2000 server from Roxen CMS on Linux?

There exist two methods listed below:

• Via ODBC
  

  It is not a trivial operation to install odbc on Linux. Two packages are needed unixODBC and freeTDS. The configuration files are /etc/odbc.ini, /etc/odbcinst.ini and /etc/freetds.conf. Note it is necessary to add the following to the roxen/local/environment file:
  

  ODBCINI=/etc/odbc.ini export ODBCINI ODBCINSTINI=/etc/odbcinst.ini export ODBCINSTINI

• Via SQL Proxy
  

  An easier solution is to use an sql-proxy. This method demands a pike deamon on the windows machine that provides the odbc connection. Below are instructions to set up the connection:

  1. Install a pike distribution on the windows machine.

  2. Install rsql.pike. This file is available upon request.

  3. Modify rsql.pike and change the user and password at the top of the file.

  4. Create a shortcut to pike.exe with the absolute path to rsql.pike as the first argument.

  5. Launch the shortcut.

  6. Create a odbc connection to the sql-server. Verify the connection.

  7. Add an external database in Roxen Administration Interface, db-tab. URL:
     

     rsql://<rsql-user>:<rsql-password>@<windows-machine>/odbc://<sql-user>:<sql-password>@<dsn-name>

How do I run the internal MySQL as user different to the one that started Roxen?

The MySQL user is controlled with the 'user' variable in the file 'roxen/configurations/_mysql/my.cfg', like 'user = mysql'.

If this variable isn't set then MySQL will run as the user that started roxen unless that user is root, in which case the 'user' variable has to be set to 'user = root'.