topic MS SQL Connector in Connectivity & Data Prep

MS SQL Connector

justinaskr — Fri, 02 Sep 2016 13:38:08 GMT

Hi,

i am new to qlik sense custom connectors.

I need to create custom connector to ms sql database with visual studio. is there any examples for it?

Can someone share simple example of it?

Justinas.

Re: MS SQL Connector

Mon, 05 Sep 2016 11:49:52 GMT

Hi J K,

If your question is about how to connect Qlik Sense with My SQL , so you need to download the ODBC driver for MYSQL install it in your machine, then create a connection with your MYSQL server and Go to Qlik Sense , create new connection , Choose ODBC or OLEDB , select your created ODBC connection and click okay , you will find a DB list ,S select yoru DB and table and accept it .

Re: MS SQL Connector

justinaskr — Mon, 05 Sep 2016 12:12:15 GMT

Hi, Rohit

The think am looking for is custom connector in visual studio for SQL DB.

I can't find information how to pass table name and select statement and then read response.

Here is code sample i am working on:

namespace QvSQLConnector
{

    internal class QvSQLConnection : QvxConnection
    {
        public override void Init()
        {
            var SQLFields = new QvxField[]
      {
              new QvxField("n17_kodas_ps", QvxFieldType.QVX_TEXT,
   QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII),
               new QvxField("n17_tipas", QvxFieldType.QVX_TEXT,
   QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII),
               new QvxField("n17_pav", QvxFieldType.QVX_TEXT,
      QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII),
             };

            MTables = new List<QvxTable>
   {
        new QvxTable
        {
              TableName = "SQL SELECT n17_kodas_ps, n17_tipas, n17_pav FROM SQL.dbo.N17_PROD",
     GetRows = GetProducstFromSQL,
                  Fields = SQLFields
    }
   };
        }

        private IEnumerable<QvxDataRow> GetProducstFromSQL()
        {
            QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "GetDataFromSQL()");

            string itemCode = "";
            string tipe = "";
            string name = "";
           QvxTable table= FindTable("SQL SELECT n17_kodas_ps, n17_tipas, n17_pav FROM SQL.dbo.N17_PROD", MTables);

if (table == null) yield return null;

                itemCode = table.Fields[0].ToString();
                tipe = table.Fields[1].ToString();
                name = table.Fields[2].ToString();

                var row = new QvxDataRow();
                row[MTables[1].Fields[0]] = itemCode;
                row[MTables[1].Fields[1]] = tipe;
                row[MTables[1].Fields[2]] = name;

                yield return row;
            }
        }

        public override QvxDataTable ExtractQuery(string query, List<QvxTable> qvxTables)
        {
            return base.ExtractQuery(query, qvxTables);
        }
    }

}

Re: MS SQL Connector

jonathandienst — Mon, 05 Sep 2016 12:38:02 GMT

Why do you need to create a custom connector? For MSSQL you can simply use an OLE DB or ODBC connection.

Re: MS SQL Connector

justinaskr — Mon, 05 Sep 2016 13:04:02 GMT

Hi, Jonathan

I am connecting to ERP Database and want to create separate connectors for different groups of data,to make it simpler and reduce number of tables.

For example, "person" information is stored in 15 different tables, so i want to create connector for it to have all in one place.

The other thing is i want to learn how to create connectors.