Sybase query_runner

[EvgenyKiselgof]

Hi, Are the Sybase support is on your roadmap? I have tried to create Sybase query_runner by this guide https://discuss.redash.io/t/creating-a-new-query-runner-data-source-in-redash/347, but nothing :( can't read schema, and run queries. runner based on MSSQL_ODBC and using Pyodbc and FreeTDS (odbc driver)

`import logging import sys import uuid

from redash.query_runner import * from redash.query_runner.mssql import types_map from redash.utils import json_dumps, json_loads

logger = logging.getLogger(name)

try: import pyodbc

enabled = True

except ImportError: enabled = False

class SybaseODBC(BaseSQLQueryRunner): should_annotate_query = False noop_query = "SELECT 1"

@classmethod
def configuration_schema(cls):
    return {
        "type": "object",
        "properties": {
            "server": {"type": "string"},
            "port": {"type": "number", "default": 5000},
            "user": {"type": "string"},
            "password": {"type": "string"},
            "db": {"type": "string", "title": "Database Name"},
        },
        "order": [
            "server",
            "port",
            "user",
            "password",
            "db",
        ],
        "required": ["server", "user", "password", "db"],
        "secret": ["password"],
    }

@classmethod
def enabled(cls):
    return enabled

@classmethod
def name(cls):
    return "Sybase"

@classmethod
def type(cls):
    return "sybase_odbc"

def _get_tables(self, schema):
    query = """
    select 'admin' as schema_name, dbo.sysobjects.name as table_name, dbo.syscolumns.name as column_name
    from dbo.sysobjects
    inner join dbo.syscolumns
    on dbo.sysobjects.id = dbo.syscolumns.id
    where dbo.sysobjects.type = 'U'
    """

    results, error = self.run_query(query, None)

    if error is not None:
        raise Exception("Failed getting schema.")

    results = json_loads(results)

    for row in results["rows"]:
        if row["table_schema"] != self.configuration["db"]:
            table_name = "{}.{}".format(row["table_schema"], row["table_name"])
        else:
            table_name = row["table_name"]

        if table_name not in schema:
            schema[table_name] = {"name": table_name, "columns": []}

        schema[table_name]["columns"].append(row["column_name"])

    return list(schema.values())

def run_query(self, query, user):
    connection = None

    try:
        server = self.configuration.get("server")
        user = self.configuration.get("user", "")
        password = self.configuration.get("password", "")
        db = self.configuration["db"]
        port = self.configuration.get("port", 5000)

        connection_string_fmt = "DRIVER={{FreeTDS}};PORT={};SERVER={};DATABASE={};UID={};PWD={}"
        connection_string = connection_string_fmt.format(
            port, server, db, user, password
        )

        connection = pyodbc.connect(connection_string)
        cursor = connection.cursor()
        logger.debug("SybaseODBC running query: %s", query)
        cursor.execute(query)
        data = cursor.fetchall()

        if cursor.description is not None:
            columns = self.fetch_columns(
                [(i[0], types_map.get(i[1], None)) for i in cursor.description]
            )
            rows = [
                dict(zip((column["name"] for column in columns), row))
                for row in data
            ]

            data = {"columns": columns, "rows": rows}
            json_data = json_dumps(data)
            error = None
        else:
            error = "No data was returned."
            json_data = None

        cursor.close()
    except pyodbc.Error as e:
        try:
            # Query errors are at `args[1]`
            error = e.args[1]
        except IndexError:
            # Connection errors are `args[0][1]`
            error = e.args[0][1]
        json_data = None
    except (KeyboardInterrupt, JobTimeoutException):
        connection.cancel()
        raise
    finally:
        if connection:
            connection.close()

    return json_data, error

register(SybaseODBC)`

[susodapop]

Hey there thanks for your interest! We don't really have a roadmap for new query runners since most of them are community contributions like this. In this case, for example, we don't use Sybase internally so we don't have a target instance to test against. I'm happy to provide guidance, though.

What errors do you receive when you attempt to use this? Do you have a branch on github I can look at?

[DenisBessa]

I got Redash working with Sybase databases using SQL Server Linked Server. In this mode, SQL Server works as a bridge to other databases.

@EvgenyKiselgof let me know if you could get the Sybase datas source working.