Ruby - Implement an SQL server?

Question

I have an application which has a Ruby API. I would like to link to this application from a SQL server system.

Is there a way for me to implement a Ruby SQL server which receives SQL statements and returns the requested data from the applications. Is it then possible to hook into this from an SQL server applications?

E.G.

# request as string like "SELECT * FROM MAIN_TABLE WHERE SOME_COLUMN = <SOME DATA>"
SQLEngine.OnRequest do |request|
   Application.RunSQL(request)
end

P.S. I don't have any experience with SQL server, so have no idea how one would go about this...

Note: I'm not asking how I can query an SQL server database, I'm asking how I can implement an SQL server connection.

Potentially one can implement an ODBC driver in C which can be hooked into from ruby's C API using [SimbaEngine](https://www.simba.com/drivers/simba-engine-sdk/)? — Sancarn, Jun 19 '20 at 17:23
For example for MySQL you can check here: https://dev.mysql.com/downloads/ruby.html, for Postgres check here: https://rubygems.org/gems/pg/versions/0.18.4. — iGian, Jun 19 '20 at 17:35
If I understood correctly you want your application to function *as* a server that returns data via incoming SQL statements? Sure, you just need to write a parser first the language, write the logic which retrieved the data etc etc. But the question is why? — Sami Kuhmonen, Jun 19 '20 at 18:02
@SamiKuhmonen kind of, I want database engines to recognise my ruby application as a query-able database. — Sancarn, Jun 19 '20 at 21:13

score 0 · Answer 1 · answered Jun 24 '20 at 23:41

After some searching I found a few other stack overflow questions about how to make Database Drivers in other languages:

creating a custom odbc driver for application

Implementing a ODBC driver

Creating a custom ODBC driver

Alternatives to writing an ODBC driver

Potentially these will be useful for others going down this road, the most hopeful suggestion is implementing the wire protocol, of which one has been made in python which should be relatively easy to port

import SocketServer
import struct

def char_to_hex(char):
    retval = hex(ord(char))
    if len(retval) == 4:
        return retval[-2:]
    else:
        assert len(retval) == 3
        return "0" + retval[-1]

def str_to_hex(inputstr):
    return " ".join(char_to_hex(char) for char in inputstr)

class Handler(SocketServer.BaseRequestHandler):
    def handle(self):
        print "handle()"
        self.read_SSLRequest()
        self.send_to_socket("N")

        self.read_StartupMessage()
        self.send_AuthenticationClearText()
        self.read_PasswordMessage()
        self.send_AuthenticationOK()
        self.send_ReadyForQuery()
        self.read_Query()
        self.send_queryresult()

    def send_queryresult(self):
        fieldnames = ['abc', 'def']
        HEADERFORMAT = "!cih"
        fields = ''.join(self.fieldname_msg(name) for name in fieldnames)
        rdheader = struct.pack(HEADERFORMAT, 'T', struct.calcsize(HEADERFORMAT) - 1 + len(fields), len(fieldnames))
        self.send_to_socket(rdheader + fields)

        rows = [[1, 2], [3, 4]]
        DRHEADER = "!cih"
        for row in rows:
            dr_data = struct.pack("!ii", -1, -1)
            dr_header = struct.pack(DRHEADER, 'D', struct.calcsize(DRHEADER) - 1 + len(dr_data), 2)
            self.send_to_socket(dr_header + dr_data)

        self.send_CommandComplete()
        self.send_ReadyForQuery()

    def send_CommandComplete(self):
        HFMT = "!ci"
        msg = "SELECT 2\x00"
        self.send_to_socket(struct.pack(HFMT, "C", struct.calcsize(HFMT) - 1 + len(msg)) + msg)

    def fieldname_msg(self, name):
        tableid = 0
        columnid = 0
        datatypeid = 23
        datatypesize = 4
        typemodifier = -1
        format_code = 0 # 0=text 1=binary
        return name + "\x00" + struct.pack("!ihihih", tableid, columnid, datatypeid, datatypesize, typemodifier, format_code)

    def read_socket(self):
        print "Trying recv..."
        data = self.request.recv(1024)
        print "Received {} bytes: {}".format(len(data), repr(data))
        print "Hex: {}".format(str_to_hex(data))
        return data

    def send_to_socket(self, data):
        print "Sending {} bytes: {}".format(len(data), repr(data))
        print "Hex: {}".format(str_to_hex(data))
        return self.request.sendall(data)

    def read_Query(self):
        data = self.read_socket()
        msgident, msglen = struct.unpack("!ci", data[0:5])
        assert msgident == "Q"
        print data[5:]


    def send_ReadyForQuery(self):
        self.send_to_socket(struct.pack("!cic", 'Z', 5, 'I'))

    def read_PasswordMessage(self):
        data = self.read_socket()
        b, msglen = struct.unpack("!ci", data[0:5])
        assert b == "p"
        print "Password: {}".format(data[5:])


    def read_SSLRequest(self):
        data = self.read_socket()
        msglen, sslcode = struct.unpack("!ii", data)
        assert msglen == 8
        assert sslcode == 80877103

    def read_StartupMessage(self):
        data = self.read_socket()
        msglen, protoversion = struct.unpack("!ii", data[0:8])
        print "msglen: {}, protoversion: {}".format(msglen, protoversion)
        assert msglen == len(data)
        parameters_string = data[8:]
        print parameters_string.split('\x00')

    def send_AuthenticationOK(self):
        self.send_to_socket(struct.pack("!cii", 'R', 8, 0))

    def send_AuthenticationClearText(self):
        self.send_to_socket(struct.pack("!cii", 'R', 8, 3))

if __name__ == "__main__":
    server = SocketServer.TCPServer(("localhost", 9876), Handler)
    try:
        server.serve_forever()
    except:
        server.shutdown()

score -1 · Answer 2 · answered Jun 19 '20 at 17:26

-1

Every programming language – including Ruby – supplies packages which implement interfaces to various SQL servers.

Start here: Ruby database access.

answered Jun 19 '20 at 17:26

Mike Robinson

8,490
5
28
41

As I said in my note, i am not looking for how to access database with Ruby. I want the oppisite. I want database engines to recognise my ruby application as a query-able database. – Sancarn Jun 19 '20 at 21:12

Ruby - Implement an SQL server?

2 Answers2