5 Replies Latest reply: Apr 23, 2014 12:27 PM by Antonio Rizzelli RSS

    QVX Connector & SQLCOLUMNS

    Darius Pranskus

      Hi

       

      I have created a QVX custom connector using QVX SDK. Everything works fine except I am unable to run SQLCOLUMNS against that connection. IF I run that command I get the following message

       

      QVX_UNKNOWN_ERROR: The given key was not present in the dictionary.. Stack trace written to C:\ProgramData\QlikTech\Custom Data\CurrencyRateConnector\Log\StackTrace.txt

      SQLCOLUMNS

       

      Is there anything I can do to enable that functionality. Can you give short example?

       

      Thanks

      Darius

        • Re: QVX Connector & SQLCOLUMNS
          Antonio Rizzelli

          Hi,

          as far as I know, you should interpret the SQLColumns, SQLTables or SQLTypes statements overriding the ExtractQuery method:

           

          public override QvxDataTable ExtractQuery(string query, List<QvxTable> tables)
          {
            // here you should get the query and give back a QvxDataTable object
            // get _tableName from query and find the table in the preloaded tablelist
            // or alternatively, create a new QvxTable object designed for SQLColumns.
            string tableName = "";
            QvxTable table = this.FindTable(tableName, tables);
            if (table == null)
            {
              QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, string.Format("ExtractQueryAndTransmitTableHeader() - QvxPleaseSendReplyException({0}, \"Table not found: {1}\")", QvxResult.QVX_TABLE_NOT_FOUND, _tableName));
             throw new QvxPleaseSendReplyException(QvxResult.QVX_TABLE_NOT_FOUND, string.Format("Table not found: {0}", _tableName));
            }
            QvxDataTable table2 = new QvxDataTable(table);
            return table2;
          }
          

           

           

          Hope this helps.

          Antonio

            • Re: QVX Connector & SQLCOLUMNS
              Darius Pranskus

              Thanks Antonio

               

              I tried this, but in case of SQLColumns command, the connector raises an error before it reaches ExtractQuery.

               

              I put an MsgBox call as first line to display the query and it works fine for SQL SELECT, event for SQL COLUMNS (see space after SQL), but not for SQLCOLUMNS.

               

              Could it be that the command must start with SQL (space) to be recognized as a valid command?

               

              Cheers

              Darius

                • Re: Re: QVX Connector & SQLCOLUMNS
                  Antonio Rizzelli

                  Hi Darius,

                   

                  you are almost right: try putting the statement:

                   

                  SELECT SQLColumns; 
                  

                   

                  Then it will enter the ExtractQuery method (tested on my qvx connector). here's the first fragment of ExtractQuery


                  public override QvxDataTable ExtractQuery(string query, List<QvxTable> tables)  
                  { 
                    MessageBox.Show("Query:"+query);  
                    QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "Internal ExtractQuery");  
                    ... 
                  

                   

                  Antonio

                    • Re: QVX Connector & SQLCOLUMNS
                      Darius Pranskus

                      Hi Antonio

                       

                      SELECT SQLColumns is not exactly the same command as SQLCOLUMNS. Say If you run SELECT SQLColumns against MS SQL server it will end up with an error. I am building reusable library and I need exact SQLColumns command to work.

                       

                      Thanks

                      Darius

                        • Re: Re: QVX Connector & SQLCOLUMNS
                          Antonio Rizzelli

                          Darius,

                           

                          that's true. The problem is that you are passed to the ExtractQuery method when the parser reaches a select that can be run against the connector.

                          You can even try using a statement like

                           

                          SQL SQLColumns;
                          

                           

                          this also passes through the ExtractQuery method, but you need to identify which statements should be parsed by the custom connector.

                           

                          For example, when connecting to AS/400 through ODBC driver, every select statement will be preceded by the SQL reserved word:

                           

                          SQL SELECT
                              FLD1,   
                              FLD2,
                              FLD3
                          FROM TAB01;
                          

                           

                           

                           

                          Antonio