Balaji's Blog - Think inside the Box

Wednesday, May 2, 2018

Connecting to SQL Server from KDB+ (Using Babel)

I already blogged about connecting to Oracle and Redshift from KDB.

Here I will cover SQL Server.

Step 0: Install Java (preferably 8) if you have not installed. java -version should give you the version

Step 1:
Follow instruction http://code.kx.com/wiki/Babel and Install Babel.
You can download Babel from wget https://github.com/CharlesSkelton/babel/raw/master/dist/babel.zip
and unzip it.

Step 2:
Credit: https://stackoverflow.com/questions/5307048/where-do-i-install-a-jdbc-driver-on-ubuntu
I assume you already have Java 8 installed. Download the corresponding SQL Server JDBC driver.
https://www.microsoft.com/en-us/download/details.aspx?id=11774
Download sqljdbc_6.0.8112.200_enu.tar.gz
Unzip and untar the file
$ gzip -d sqljdbc_6.0.7507.100_enu.tar.gz
$ tar -xf sqljdbc_6.0.7507.100_enu.tar
You can optionally create a soft link for your corresponding SQL JDBC under babel/lib. This will come in handy while starting Babel.
Note: Choose the right driver while creating the soft link: sqljdbc_6.0/enu/jre8/sqljdbc42.jar is the right one Java 8

Step 3: Start Babel
java -Xmx1024m -cp "babel.jar:sqljdbc42.jar" de.skelton.babel.Babel 6868 com.microsoft.sqlserver.jdbc.SQLServerDriver
You might have to substitute the actual path of babel.jar & sqljdbc42.jar (For Java 8)
You will see message something like this
Babel for kdb+ v1.34 2014.03.24

2018.05.02 10:05:22.571 Listening on localhost:6868 for connections...
2018.05.02 10:08:06.513 Authenticating balaji
2018.05.02 10:08:06.513 Accepted connection from /127.0.0.1:38126
2018.05.02 10:15:32.779 Request from /127.0.0.1:38126

Step 4: Start Q and connect to SQL Server

$ q
KDB+ 3.2 2015.05.27 Copyright (C) 1993-2015 Kx Systems
l32/ 4()core 7965MB balaji balaji 127.0.01 NONEXPIRE

Welcome to kdb+ 32bit edition
q)h:hopen 6868
q)r:h("query";"jdbc:sqlserver://sqldb-host:1433;databaseName=mysqldb;user=sqluser;password=xxxTopSecret";"SELECT * FROM <your table>")
q)meta r
q)count r
q)r
..... Your Table shown here ....

And that's it

Tuesday, October 17, 2017

Changing the default Apache web directory(document root) on RHEL without disabling SE Linux

Default document root of Apache in RHEL is "/var/www/html". if you have tried changing it to something else like "/web/apps" then you would get 403 forbidden error.
If you google it, I am sure you will find a solution to include /web/apps into <Directory> directive and you will be asked to set +x permissions on /web & /web/apps folder (using chmod 755)
And your Apache vhost will look similar to it

<VirtualHost *:80>
 ServerName  www.example.com
 DocumentRoot /web/apps
  <Directory "/web/apps" >
   Options Indexes FollowSymLinks
   AllowOverride None
   Require all granted
  </Directory>
</VirtualHost>

After doing it if you restart Apache and try you will still get "403 forbidden error"

If you further google, you will find out this is because of SE Linux and steps to disable that.
Basically "sudo setenforce 0", and you can call it a day since everything start to work.

However with "sudo setenforce 0" you are disabling the entire SE Linux security. There is a better way to do that and that is what all this Blog is about.
And here you go.


sudo semanage fcontext -a -t httpd_sys_content_t "/web/apps(/.*)?"

sudo restorecon -R -v /web/apps

That's it folks. If you want to see what magic it does, use the special flag -Z with ls

[ec2-user@ip-10-0-0-1 ~]$ ls -Z /var/www

drwxr-xr-x. root root system_u:object_r:httpd_sys_script_exec_t:s0 cgi-bin

drwxr-xr-x. root root system_u:object_r:httpd_sys_content_t:s0 html

[ec2-user@ip-10-0-0-1 ~]$ ls -Z /web

drwxrwxr-x. rxds rxds unconfined_u:object_r:httpd_sys_content_t:s0 apps

drwxrwxr-x. rxds rxds unconfined_u:object_r:default_t:s0 another_directory

httpd_sys_content_t is what makes the Apache to server from /web/apps directory

More info at
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security-Enhanced_Linux/sect-Security-Enhanced_Linux-Troubleshooting-Top_Three_Causes_of_Problems.html

Thursday, September 21, 2017

$.get Cross Domain request with withCredentials: true

Even though $.get works for Cross-Domain(As long as CORS enabled), it doesn't send cross domain cookies while making request.

Meaning, let's say you have logged into https://cd-example.org and cookie is all set.
Now you want to make a cross domain request to https://cd-example.org from another domain https://example.org

If you make $.get("https://example-cd.org/someurl/",function(data){console.log(data)})
it won't work, because it doesn't send cd-example.org cookie. You need to use $.ajax with xhrFields: { withCredentials: true}

I just found a trick to workaround this $.get issue. Set {xhrFields: { withCredentials: true}} in $.ajaxSetup, which automatically gets attached to $.get and everything works magically.

So first
$.ajaxSetup({xhrFields: { withCredentials: true} })
and then make
$.get("https://example-cd.org/someurl/",function(data){console.log(data)})

Wednesday, June 21, 2017

Connecting to Redshift from KDB+ (Using Babel)

If you want to connect to Oracle from KDB+ refer to my "Connecting to Oracle from KDB+ (Using Babel)" post.

Let's assume your q home is /opt/q

$ mkdir babel

$ cd babel

Download babel.zip & unzip it

$ wget https://github.com/CharlesSkelton/babel/raw/master/dist/babel.zip

$ unzip babel.zip

You will see babel.jar & lib folder

$ ls -la

total 4664

drwxr-xr-x 5 balaji staff 170 Jun 21 17:53 .

drwxr-xr-x@ 10 balaji staff 340 Jun 21 17:52 ..

-rw-r--r-- 1 balaji staff 27713 Mar 24 2014 babel.jar

-rw-r--r--@ 1 balaji staff 2357704 Jun 21 17:52 babel.zip

drwxr-xr-x 8 balaji staff 272 Jun 21 17:57 lib

Goto lib and download Amazon Redshift jdbc driver
$cd lib

$ wget https://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC42-1.2.1.1001.jar

$ ls -la

total 9392

drwxr-xr-x 8 balaji staff 272 Jun 21 17:57 .

drwxr-xr-x 5 balaji staff 170 Jun 21 17:53 ..

-rw-r--r--@ 1 balaji staff 2316196 Nov 17 2016 RedshiftJDBC42-1.2.1.1001.jar

-rw-r--r-- 1 balaji staff 21986 Mar 24 2014 csvjdbc.jar

-rw-r--r-- 1 balaji staff 706710 Mar 24 2014 hsqldb.jar

-rw-r--r-- 1 balaji staff 463412 Mar 24 2014 janino.jar

-rw-r--r-- 1 balaji staff 703265 Mar 24 2014 mysql-connector-java-5.1.6-bin.jar

-rw-r--r-- 1 balaji staff 583286 Mar 24 2014 sqljdbc.jar

Here is the scary part. Including Redshift driver (lib/RedshiftJDBC42-1.2.1.1001.jar) into babel.jar
http://code.kx.com/wiki/Babel#Specifying_your_jdbc_driver_and_repacking_the_jar

$ jar -xvf babel.jar

created: META-INF/

inflated: META-INF/MANIFEST.MF

created: de/

created: de/skelton/

created: de/skelton/babel/

created: de/skelton/util/

created: kx/

inflated: de/skelton/babel/Babel$1.class

inflated: de/skelton/babel/Babel$2.class

inflated: de/skelton/babel/Babel.class

inflated: de/skelton/util/Logger.class

inflated: de/skelton/util/SmartArray.class

inflated: kx/c$Dict.class

inflated: kx/c$Flip.class

inflated: kx/c$IAuthenticate.class

inflated: kx/c$KException.class

inflated: kx/c$Minute.class

inflated: kx/c$Month.class

inflated: kx/c$Msg.class

inflated: kx/c$Second.class

inflated: kx/c$Timespan.class

inflated: kx/c.class

$vi META-INF/MANIFEST.MF
add lib/RedshiftJDBC42-1.2.1.1001.jar

Save and exit vi
Recompile the jar file and ignore the warnings

$ jar umf META-INF/MANIFEST.MF babel.jar

Jun 21, 2017 6:07:47 PM java.util.jar.Attributes read

WARNING: Duplicate name in Manifest: Manifest-Version.

Ensure that the manifest does not have duplicate entries, and

that blank lines separate individual sections in both your