Forum Discussion

krupalibshah's avatar
krupalibshah
Contributor
8 years ago

Connecting to SQLMX

I am working on connecting to a SQLMX database using the JDBC connection.
The connection is successful when I add the details in the JDBC account, but when I try to execute queries from the pipeline it throws error.

I could run the same query using SQL workbench tool. See below the screenshots.

Have anyone come across these errors? or if there is a known setting that is missed?
Any help will be appreciated.

*** ERROR[1183] Error 73 was returned by the file system on metadata table IFDB.DEFINITION_SCHEMA_VERSION_3500.OBJECTS (file name (partition \HPENS01.$S00X02.ZSD8KWJP.OBJECT00)). [2017-08-09 11:24:59] [SQL State=X0153, DB Errorcode=-1183]
Next: *** ERROR[4082] Object IFDB.DEFAULT_SCHEMA.KRUPALI does not exist or is inaccessible. [2017-08-09 11:24:59] [SQL State=42000, DB Errorcode=-4082]
Next: *** ERROR[8822] The statement was not prepared. [2017-08-09 11:24:59] [SQL State=X08MU, DB Errorcode=-8822]

/KS

2 Replies

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor

    jfpelletier 

    1) You can define the parameters(the ones that are expected to be sent by the system) in the pipeline properties, and access the parameters with "_" and the name of the parameter. ex: _param1. 

    2) You can access all the parameters with the QUERY_STRING argument. 

          ex: _QUERY_STRING

          The result is string containing all the parameters sent by the system. ex: "test=value&test1=value1". 

          So, probably you will need to utilize some of the string functions in order to get/extract the parameters. 

     

     

    • jfpelletier's avatar
      jfpelletier
      Contributor

      Hello SpiroTaleski,

      Thanks a lot for your reply, both options work perfectly well for me! 🙂

      I wish you have a great day!

      Kind regards,

      JF

      • jfpelletier's avatar
        jfpelletier
        Contributor

        Hello again SpiroTaleski,

        May I bother you with another question if you don't mind?

        What about the other data passed during the call?

        In the documentation of the application sending the webhook that I'm monitoring, they say that the callback is sent as a POST request with an application/x-www-urlencoded content type and an 'additionalData' field in the request payload containing a JSON body.

        I tried to configure and capture that 'additionalData' field in the pipeline parameters and in the mapper snap that reads the input from the JSON parser snap used in the open end of the pipeline, and I'm getting this error message:

        Unable to parse JSON input, Unrecognized token 'additionalData': was expecting (JSON String, Number, Array, Object or token 'null', 'true' or 'false')

        This is what the pipeline looks like (it's not doing anything yet, it's just writing the payload into a file):

        There is no file written by the "File Writer" snap because of the error, however there is a "request-content-<data-time>.bin" file that is automatically created, and it contains the "additionalData" values that I'm expecting and looking for.

        How can I get that "additionalData" (request content) in my pipeline?

        Many thanks in advance again! 🙂

        JF

    • jfpelletier's avatar
      jfpelletier
      Contributor

      Hello SpiroTaleski,

      The "additionalData" is not sent as query parameter, and I can't seem to capture it as payload with the mapper.

      This is what my mapper looks like, and I'm still getting the same error message:

      I also tried to use simply "$" instead of "$additionalData" in the Expression field, but same error message...

      I used the "webhook.site" website utility to capture the full data, and the "additionalData" field was displayed in the "Form values" section (sorry about the blurred sections, I have to anonymize before posting...):

       

      Thanks,

      JF

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor

    jfpelletier 

    As I can see from the first screenshot, seems to fails on JSON Parser Snap. So, it looks like that the message being sent from the system is not valid JSON. 

    • jfpelletier's avatar
      jfpelletier
      Contributor

      Hello SpiroTaleski,

      Indeed, the JSON doesn't make it to the mapper, so the issue seems to be with that input...

      I have taken the value that webhook.site returned for that field and validated it, it is valid JSON apparently...

      I tried to check the "Non-standard JSON" option in the JSON parser snap, but there's no change, I'm still getting the error.

      Any idea?

      Thanks!

      JF

    • jfpelletier's avatar
      jfpelletier
      Contributor

      Hello again SpiroTaleski,

      I think that I'm getting somewhere, however it's still not working.

      To fix the issue with the invalid JSON, I started the pipeline with a "Document to Binary" snap, and I'm not getting the error anymore!

      What happens however is strange... The File Writer snap creates the files with the content I'm looking for, and now I realize why it's not valid JSON, it's because somehow all the double quote characters ( " ) have been escaped by a backslash ( \" ) making the JSON not valid. Like this:

      The other strange thing is that I'm not getting the values from the query string anymore... Actually I do, and found the data by accident, when I tried to use the option "Write header file" in the "File Writer" snap. There was an extra file with the extension ".header" created, and it contained the query string that I couldn't find:

      I need to use that information from the header file (query string), but I don't know where to find it in the snap variables...

      Any idea?

      Thanks a lot in advance!

      JF

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor

    jfpelletier 

    Try first to store the data being sent from the system in a file on SLDB. 

    Put only File writer Snap with open input view, and store the data as JSON. 

    Then you can analyze the validity of the the JSON. 

    • jfpelletier's avatar
      jfpelletier
      Contributor

      Hello SpiroTaleski,

      I did that already (write the JSON and analyze its validity) using the "Record Replay" snap, and I also used the results that I observed in "webhook.site". That's how I found out first about the JSON with all the double quote characters ( " ) that have been escaped by a backslash ( \" ) making the JSON not valid.

      You can see in the screenshot below:

      And also on "webhook.site" results:

      So basically I need to use the above values from "Query strings" and "Forms values".

      Thanks for any help! 🙂

      Kind regards,

      JF