December 15, 2012

Some funny SQL challenges in CUBRID


Hi!
Do you remember when CUBRID hosted the SQL challenge last year...? It was such great fun!
Unfortunately, this year there was no more such a challenge... :(  Hey, CUBRID team, if you listen, please do it again - is was such a great initiative!

In the mean time, what better way to celebrate one year ending - 2012 - and one new year coming, than having our own SQL challenge...? ha-ha... :)

So today, let’s take a look at some (hopefully!) funny SQL challenges...

Q: Let’s start with an easy one: Given a date, what day of the week it falls on?

A: We will use the TO_CHAR function:

SELECT TO_CHAR(DATETIME'01/01/2013', 'Day')

Result:

Tuesday

Tip: The TO_CHAR function accepts a new parameter in 9.0 – the locale. Try it for your own language… :)

Q: Given an integer (69 for example), how many bits are set to 0 in its binary representation?

A: To solve this one, we will use the BIT_COUNT and the BIN functions:

SELECT LENGTH(BIN(69)) - BIT_COUNT(69)

Result:

4

(69 = 10001012)

Q: Wanna have some great time? If YES, then read about the “Answerto the Ultimate Question of Life, the Universe, and Everything (42)”!
It’s nothing else but pure magic! …Do NOT come back here until you have read it!! :)
Now, one of the cool things which are mentioned in there is the GIGO principle of “Garbage in,garbage out”…
And the challenge for you is: Illustrate the GIGO principle the best you can, in relation to 42, using SQL statements…!

A: Here are some of my own answers… …can you do BETTER? :)

SELECT LENGTH(‘42’) * TO_NUMBER(LENGTH('GARBAGE IN') + LENGTH('GARBAGE OUT')) 

SELECT 42 * BIT_COUNT(4242424242424242 * BIN(LENGTH('GARBAGE IN GARBAGE OUT')))

SELECT TO_NUMBER(CONV(LENGTH('Ultimate Question of Life, The Universe, and Everything.'), 10, 13)) - LENGTH('42')

Result is always (obviously - ha-ha):

42!

  •          The angle at which light reflects off of water to create a rainbow is 42 degrees.
  •          Two physical constants in the universe are the speed of light and the diameter of a proton. It takes light 10 to the minus 42nd power seconds to cross the diameter of a proton.
  •          A barrel holds 42 gallons.

Q: What is number 10, expressed in base -10, when converted to base 10?

A: Let’s see this, by using the CUBRID CONV function:

SELECT CONV(10, -10, 10)

Result:

1!

(…can you figure out why…?)


Q: How do you test in SQL if a number is part of the Fibonacci sequence?

A: Gessel solved this in 1972 with a simple test:
“N is a Fibonacci number if and only if 5 N^2 + 4 or 5 N^2 – 4 is a square number!”

Let’s do this test in plain SQL (let’s use 33 and 34 for example):

SET @x := 34;
SELECT @x,
       CASE WHEN
                (POWER(ROUND(SQRT((5 * POWER(@x, 2)) + 4), 0), 2) = (5 * POWER(@x, 2)) + 4 )
                OR
                (POWER(ROUND(SQRT((5 * POWER(@x, 2)) - 4), 0), 2) = (5 * POWER(@x, 2)) - 4 )
                THEN 'Yes'
       ELSE 'No'
       END

Result for 33:
No!

Result for 34:
Yes!

Q: What is the connection between the 5thelement - WATER and the so-called “end of days (21 December2012)”…?! LOL…

A: Let’s see…

SELECT CHR(TO_DAYS('2012-12-21'));

Result:



Thank you, I hope you learned some new stuff today (and had fun as well!) - See you next time – Bye-bye!
/Dee

P.S. Hey - one last funny CUBRID SQL query… :)



December 1, 2012

CUBRID Connection strings

Hi again!
...Ever had to deal with CUBRID connection strings...? :)
...Ever had to spend you time digging the net to find the precious connection strings syntax...?
Well, if you did, and if you need to have this information easily available at your fingers - here it is! (...and no, connectionstrings.com doesn't have support for CUBRID yet... :()

So I created a small script which will build your CUBRID connection string - just enter the connection data pieces and voila` - you got it! :)

One more thing - at the end of this blog post you will find all the links you need to the available online documentation and some quick connection strings examples.

Connection data:

Host:
Port:
Database:
User id:
Password:
Other data:
Type:

CUBRID connection string:


Be aware, for some drivers you can connect either using a connection string, either providing the connection information to the specific connect drivers method(s).

For example, in PHP you can connect in both ways:
Specific driver method:  
    $conn = cubrid_connect("localhost", 33000, "demodb", "public", "");
or:
Connection string method:
    $con = cubrid_connect_with_url("cci:CUBRID:localhost:33000:demodb:dba::?autocommit=true");

If you would like to see listed more information about the drivers connection specific methods, let me know and I will post a separate blog.


See you next time! :)
Bye-bye!
Dee


Quick examples reference (let's assume the user "dba" has the password "pwd"):

Driver Connection string
JDBC jdbc:cubrid:localhost:33000:demodb:dba:pwd
http://www.cubrid.org/manual/840/en/Connection%20Configuration
.NET server=localhost;database=demodb;port=33000;user=public;password=pwd
http://www.cubrid.org/wiki_apis/entry/connection-string
OLE DB Provider=CUBRID.OLEDBProvider;Location=localhost;Data Source=demodb;User Id=dba;Password=pwd;Port=33000
http://www.cubrid.org/manual/90/en/OLE%20DB%20Programming
Ruby @con = Cubrid.connect('db_name', 'host', port, 'db_user', 'db_password') http://www.cubrid.org/wiki_apis/entry/cubrid-ruby-api-documentation
Python CUBRID:localhost:33000:demodb:dba:pwd:
(CUBRID:host:db_name:db_user:db_password:?properties)

http://www.cubrid.org/wiki_apis/entry/cubrid-python-api-documentation
PHP cci:CUBRID:localhost:33000:demodb:dba:pwd:?autocommit=true
http://www.php.net/manual/en/function.cubrid-connect-with-url.php
ODBC DRIVER=CUBRID Driver;UID=dba;PWD=pwd;FETCH_SIZE=100;PORT=33000;SERVER=127.0.0.1;DB_NAME=demodb;CHARSET=utf-8
http://www.cubrid.org/manual/90/en/ODBC%20Programming#connection
PDO cubrid:host=127.0.0.1;port=33000;dbname=demodb
http://www.cubrid.org/manual/90/en/PDO%20Programming
Perl DBI:cubrid:database=demodb;host=localhost;port=33000;autocommit=true
http://search.cpan.org/~cubrid/DBD-cubrid-8.4.0.0002/cubrid.pm