Database test SQL for each vendor

When I setup a connection pool or database tool with a new database profile I like to test that it works, here are some vendor specific SQL you can use to test your db connection.

Cloudscape	#SQL SELECT 1
#DB2	#SQL SELECT COUNT(*) FROM SYSIBM.SYSTABLES
#Informix	#SQL SELECT COUNT(*) FROM SYSTABLES
#Microsoft SQL Server	#SQL SELECT COUNT(*) FROM SYSOBJECTS
#MySQL	#SQL SELECT 1
#Oracle	#SQL SELECT 1 FROM DUAL
#PointBase	#SQL SELECT COUNT(*) FROM SYSTABLES
#PostgreSQL	#SQL SELECT 1
#Progress	#SQL SELECT COUNT(*) FROM SYSTABLES
#Sybase	#SQL SELECT COUNT(*) FROM SYSOBJECTS

This is useful because you don't often know what tables exist in a database to test a query with, you can't assume select * from person would work if no person table was created. These queries are for the builtin system database resource in the specific vendor database types, they should exist even on a freshly installed or blank database server.

How do I insert CR LF in string column of a DB2 insert statement?

The insert syntax for adding new lines or line terminators in varchar columns in DB2 is not straightforward:

INSERT INTO EXAMPLE_TABLE ("ID","LEVEL_ID","VERSION","CONTENT","TYPE","FRAGMENT_ID") VALUES (158781,317339,20110701, 'your line of text' || x'0D' || x'0A' || 'your next line of text','HTML',0); 

The key to the query is to use || operator to concatenate the strings for the content column. The syntax for adding CR LF is :

'your line of text' || x'0D' || x'0A' 'your next line of text' 

DB2 will take the above and combine the strings into a single value for the database. The x'0D' x'0A' is to say hex value OD (carriage return) and hex value 0A (line feed). Windows expects both, for Linux/Unix OS only the 0A character is expected. For some versions of Mac OS you use only 0D, the newest ones use only 0A to end a line. For browsers, 0A is also the only needed character which is often ignored unless surrounded by pre or code tags however having both 0D and 0A will not cause display issues. The newest Windows OS may have support for just 0A as well in some cases.

How do I setup SSL for my apache web server?

Here is what I did to setup SSL for an apache web server on my local 64 bit Windows 7 box.

Download Apache2 with SSL module.

Install it here C:\Program Files (x86)\Apache Software Foundation\Apache2.2

Create an APACHE_HOME environment variable, from now on this is APACHE_HOME=C:\Program Files (x86)\Apache Software Foundation\Apache2.2

Add %APACHE_HOME%\bin to the path

Make a link to fix a bug with 64-bit Windows:

mklink /D %USERPROFILE%\apache "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\"

Next I did the following:

cd %APACHE_HOME%\bin   openssl req -config "%APACHE_HOME%\conf\openssl.cnf" -new -out "%USERPROFILE%\cg.csr" -keyout "%USERPROFILE%\cg.pem" 

Loading 'screen' into random state - done  Generating a 1024 bit RSA private key .. ++++++ ...................++++++ writing new private key to  'C:\Documents and Settings\Administrator\cg.pem'  Enter PEM pass phrase:  Verifying - Enter PEM pass phrase:  -----  You are about to be asked to enter information that will be  incorporated into your certificate request. What you are about to  enter is what is called a Distinguished Name or a DN. There are  quite a few fields but you can leave some blank For some fields  there will be a default value, If you enter '.', the field will be  left blank.  -----  Country Name (2 letter code) [AU]:US  State or Province Name (full name) [Some-State]:Utah  Locality Name (eg, city) []:Orem  Organization Name (eg, company) [Internet Widgits Pty Ltd]:CG, LLC  Organizational Unit Name (eg, section) []:Engineering  Common Name (eg, YOUR name) []:   Email Address []:   Please enter the following 'extra' attributes to be sent with your  certificate request A challenge password []:  An optional company name []: 

for the "YOUR" name parameter openssl asks for (domain name) I used my machine's network name. (this is important because your HTTPS urls will check for the domain name in the browser request.

Next I did this to create a key file:

openssl rsa -in "%USERPROFILE%\cg.pem" -out "%USERPROFILE%\cg.key" 

Enter pass phrase for C:\Documents and Settings\Administrator\cg.pem: writing RSA key 

Next I ran this to create a certificate file:

openssl x509 -in "%USERPROFILE%\cg.csr" -out "%USERPROFILE%\cg.cert" -req -signkey "%USERPROFILE%\cg.key" -days 365 

Loading 'screen' into random state - done  Signature ok subject=/C=US/ST=Utah/L=Orem/O=CG, LLC/OU=Engineering/CN=cg.com Getting Private key 

The %USERPROFILE%\cg.key and %USERPROFILE%\cg.cert are used by apache for encrypting data over HTTPS. I copied them into %APACHE_HOME%\conf:

copy "%USERPROFILE%\cg.key" "%APACHE_HOME%\conf"  copy "%USERPROFILE%\cg.cert" "%APACHE_HOME%\conf" 

*The excessive use of quotations(") is to account for pesky directory names with spaces fouling up the command line.

Now it is time to configure apache to enable SSL, and configure apache with your generated SSL key and certificate:

uncommment these lines in %APACHE_HOME%\conf\httpd.conf

..  LoadModule ssl_module modules/mod_ssl.so  ...  Include conf/extra/httpd-ssl.conf  ... 

Replace localhost with your fully qualified domain name or IP address in httpd.conf and extra/http-ssl.conf

ServerName cg.com:80 

Edit the extra/httpd-ssl.conf file and fix the Windows 7 bug using the soft link created earlier:

change this  SSLSessionCache        "shmcb:C:/Program Files (x86)/Apache Software Foundation/Apache2.2/logs/ssl_scache(512000)"   to this  #SSLSessionCache        "shmcb:C:/Program Files (x86)/Apache Software Foundation/Apache2.2/logs/ssl_scache(512000)"  SSLSessionCache        "shmcb:%USERPROFILE%/apache/logs/ssl_scache(512000)" 

because the (x86) in the path confuses apache !

Check that this is set and uncommented:

SSLEngine on 

Also change these values:

SSLCertificateFile "C:/Program Files (x86)/Apache Software Foundation/Apache2.2/conf/cg.cert"  SSLCertificateKeyFile "C:/Program Files (x86)/Apache Software Foundation/Apache2.2/conf/cg.key" 

Restart apache and you should be able to use https urls.

If you don't have mklink, use junction from here http://technet.microsoft.com/en-us/sysinternals/bb896768

junction "%USERPROFILE%\apache" "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\"

Or make sure to change the default folder you install Apache HTTPD to one which does not contain parenthesis, I use a set of folders

C:\Apps\

C:\Tools\ as folders to install most software and avoid Program Files and Program Files (x86) default locations for this reason.

For setting up Request Forwarding (Apache as a Proxy server)

Uncomment this in httpd.conf:

LoadModule rewrite_module modules/mod_rewrite.so 

Turn off ProxyRequests (apparently the Internet will asplode if you leave it on):

ProxyRequests off

For each path you want to proxy with Apache do this:

ProxyPass ProxyPassReverse /          SetOutputFilter  proxy-html          RequestHeader    unset  Accept-Encoding  

For example, if I want to pass all requests made to the Apache web server on /CG root path to a WebSphere J2EE application server running at http://cg.com:9080/CG, then I would create this:

ProxyPass /CG http://cg.com:9080/CG  ProxyPassReverse /CG http://cg.com:9080/CG ProxyPassReverse /          SetOutputFilter  proxy-html          RequestHeader    unset  Accept-Encoding 

You may also need to uncomment these lines in httpd.conf to get some of the proxy configuration to work:

LoadModule proxy_module modules/mod_proxy.so

LoadModule headers_module modules/mod_headers.so

LoadModule proxy_module modules/mod_proxy.so

Use this command to restart apache whenever changing the httpd.conf:

httpd.exe -k restart

Also to force HTTPS for a proxy path using Apache Rewrite:

First turn on mod_rewrite in httpd.conf:

LoadModule rewrite_module modules/mod_rewrite.so 

Change your location that you want to force HTTPS to this

ProxyPassReverse /          SetOutputFilter  proxy-html          RequestHeader    unset  Accept-Encoding       RewriteEngine On      RewriteCond %{HTTPS} off       RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} 

Conditional Request Headers

I need to have a redirected request from a login service which sets a response cookie for successful login, add a special header value to the request:

httpd.conf needed this:

SetEnvIf Cookie "x_ldap_userdata=([^;]+)" USERDATA=$1  SetEnvIf Cookie "x_ldap_userdata=([^;]+)" HAVE_USERDATA=1  RequestHeader append LDAP-Username "%{USERDATA}e" env=HAVE_USERDATA

Now the string

LDAP-Username:username 

will be added to the header of requests when a cookie for a similar domain and path named x_ldap_userdata has been set in the browser by the login service.

The browser is dumb and just follows the redirect but cannot be told to put a header value in the request, you must add these changes to the httpd.conf to modify the request to include a special authentication header value.

Clear as mud?

I used these links to download apache 2 with ssl and also openssl:

http://downloads.sourceforge.net/gnuwin32/openssl-0.9.8h-1-setup.exe

http://apache.osuosl.org//httpd/binaries/win32/httpd-2.2.17-win32-x86-openssl-0.9.8o.msi

My Smoked Brisket

 

Used the rest of my favorite rub, turned out pretty good.