[BUG] Snowflake - Convert operation is expecting a property 'rowType' not defined in the swagger file

[schabungbam]

Type of Connector

Independent Publisher Connector

Name of Connector

Snowflake

Describe the bug

The 'Convert result set rows from array to obects' is always failing with a 400 error: { "Message": "rowType or data parameter are empty!" }

The reason is because the script file is expecting a 'rowType' property: (https://github.com/microsoft/PowerPlatformConnectors/blob/f7f4c9100692791b610228ce4f6a737bc2cad6b5/independent-publisher-connectors/snowflake/script.csx#L33)

This property is not defined in the swagger file definition. I see a property called 'resultSetMetaData' (https://github.com/microsoft/PowerPlatformConnectors/blob/f7f4c9100692791b610228ce4f6a737bc2cad6b5/independent-publisher-connectors/snowflake/apiDefinition.swagger.json#L1225).

They should be the same.

Is this a security bug?

No, this is not a security bug

What is the severity of this bug?

Severity 2 - One or more important connector features are down

To Reproduce

Just test the operation. You dont even need a working connection - since this is implemented in code. You will always get this error.

Expected behavior

The operation succeeds.

Environment summary

N/A

Additional context

N/A

[schabungbam]

@rekodus We will want to fix this. Hopefully it is just a trivial change.

[mamurshe]

@schabungbam the schema also has "data" property, which is what the code is looking for. Do you have a sample JSON that's failing?

              "schema": {
                "type": "object",
                "properties": {
                  "resultSetMetaData": {
                    "type": "string"
                  },
                  "data": {
                    "type": "string"
                  }
                }

[schabungbam]

There are couple of things to fix. First, the swagger definition is not correct. "rowType" is an array of objects as described here (https://docs.snowflake.com/en/developer-guide/sql-api/reference.html#resultset-resultsetmetadata). "data" is an array of arrays of strings. The number of strings items in the inner array varies on the table.

Sample Input will be like the following: { "rowType": [ { "name": "C_CUSTKEY", "database": "SNOWFLAKE_SAMPLE_DATA", "schema": "TPCH_SF1", "table": "CUSTOMER", "precision": 38, "type": "fixed", "scale": 0, "nullable": false }, {...} ], "data": [ [ "60001", "Customer#000060001", "9Ii4zQn9cX", "14", "24-678-784-9652", "9957.56", "HOUSEHOLD", "l theodolites boost slyly at the platelets: permanently ironic packages wake slyly pend" ], [...] ] }

[schabungbam]

The other issue is a bug in the custom code. The handling of the 'fixed' type is incorrect in that it can be a decimal number when the scale > 0.

So, this line should be changed. (https://github.com/microsoft/PowerPlatformConnectors/blob/25a70f048002d6ddc09274fcf6dbbe57df558141/independent-publisher-connectors/snowflake/script.csx#L56)

To something like

`                            case "fixed":
                                long scale = 0;
                                long.TryParse(col["scale"].ToString(), out scale);
                                if (scale == 0)
                                {
                                    long myLong = long.Parse(row[i].ToString());
                                    newRow.Add(new JProperty(name.ToString(), myLong));
                                } else {
                                    // we are losing fidelity here
                                    double myDouble = double.Parse(row[i].ToString());
                                    newRow.Add(new JProperty(name.ToString(), myDouble));
                                }
                                break;
`

[schabungbam]

There are couple of things to fix. First, the swagger definition is not correct. "rowType" is an array of objects as described here (https://docs.snowflake.com/en/developer-guide/sql-api/reference.html#resultset-resultsetmetadata). "data" is an array of arrays of strings. The number of strings items in the inner array varies on the table.

BTW, it might be better NOT to fix this. Our designer looks so bad with the right schema.

[rekodus]

There is typo in the script.csx that causes the rowType issue. line 33+ (current): if (contentAsJson["data"] == null || contentAsJson["rowType"]==null) { return createErrorResponse("rowType or data parameter are empty!", HttpStatusCode.BadRequest); }

line 33+ (fixed): if (contentAsJson["data"] == null || contentAsJson["resultSetMetaData"]==null) { return createErrorResponse("resultSetMetaData or data parameter are empty!", HttpStatusCode.BadRequest); }