RAWSChemaKey and RAWSchemaVal don't match up

[DCPCanvas]

We've suddenly started to experience an issue where RAWSchemaKey and RAWSchemaVal don't match up. For example, "Type" values are showing with the description, or column name, etc....not coming across as the data type (varchar, bigint, etc.).

Anyone have any idea why this might be happening? Anyone else experiencing this, or is this possibly isolated to just our data?

[PeterWenTW]

We have same issue now

[PeterWenTW]

I think the issue is in the query below

sql.execute( {
query: "TRUNCATE TABLE dbo.RAWSchemaKey " + "TRUNCATE TABLE dbo.RAWSchemaVal" } ).then( function( results ) { console.log( results ); }, function( err ) { console.log( "Something bad happened:", err ); } ); sql.execute( {
query: "TRUNCATE TABLE dbo.RAWSchemaCom" } ).then( function( results ) { console.log( results ); }, function( err ) { console.log( "Something bad happened:", err ); } ); ////////////// sql.execute( {
query: "INSERT INTO dbo.RAWSchemaKey ([schKey]) " + "SELECT value FROM @schKey", params: { schKey: { val: schKey, type: sql.VARCHAR(4096), asTable: true } } } ).then( function( results ) { console.log( results ); }, function( err ) { console.log( "Something bad happened:", err ); } ); ////////////// sql.execute( {
query: "INSERT INTO dbo.RAWSchemaVal ([schVal]) " + "SELECT value FROM @schVal", params: { schVal: { val: schVal, type: sql.VARCHAR(4096), asTable: true } } } ).then( function( results ) { console.log( results ); }, function( err ) { console.log( "Something bad happened:", err ); } );

The issue is above query does not write into RAWSchemaKey tables as same order as array schKey. If you open the console.log(schKey), you can see the order of values is different from schKey to RAWSchemaKey. Same thing happens to RAWSchemaVal.

If someone knows the solution, please let me know.

[DCPCanvas]

Something causes the SchVal and SchKey arrays to become mismatched (believe it is errors bumping up one array and not the other), and therefore the inserted row numbers in the 2 SQL tables don't match as they are expected. We re-wrote the code to use a stored procedure to do the inserts, where we assign a variable value to each row, and use that variable in the insert commands. This forces a matching row number across tables, as the row is determined by the variable. I'll see if I can post the code here.

[DCPCanvas]

Stored procedures...accepts parameter values from the javascript:

USE [CanvasDataStore] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[sp_canvas]

@Id int,
@schKey varChar(4096),
@schVal varChar(4096)

AS BEGIN

SET NOCOUNT ON;

insert into [dbo].[RAWSchemaKey] (ID, schKey)
values (@Id, @schKey)

insert into [dbo].[RAWSchemaVal] (ID, [schVal])
values (@Id, @schVal)

END GO

[DCPCanvas]

This replaces javascript CanvasDataDL_Schema_Latest. Test before implementing to verify results.

var fs = require('fs'); var https = require('https'); //var json2csv = require('json2csv'); var CanvasDataAPICall = require('./CanvasDataAuth_Schema.js'); var sql = require("seriate"); var datetime = new Date(); var datetimeSimple = (datetime.getMonth() + 1) + '-' + datetime.getDate() + '-' + datetime.getFullYear(); var os = require('os');

var request = require('request');

var schKey=[];
var schVal=[];

var download = function(uri, filename, callback){ request.head(uri, function(err, res, body){

var r = request(uri).pipe(fs.createWriteStream(filename));
r.on('close', callback);

}); };

JSON.flatten = function(data) { var flattenResult = {}; function recurse (cur, prop) { if (Object(cur) !== cur) { flattenResult[prop] = cur; } else if (Array.isArray(cur)) { for(var i=0, l=cur.length; i<l; i++) recurse(cur[i], prop + "[" + i + "]"); if (l == 0) flattenResult[prop] = []; } else { var isEmpty = true; for (var p in cur) { isEmpty = false; recurse(cur[p], prop ? prop+"."+p : p); } if (isEmpty && prop) flattenResult[prop] = {}; } } recurse(data, ""); //console.log("FlattenResult:",flattenResult) return flattenResult; }

var config = {
"server": "[Servername]", "user": "[Username]", "password": "[password]", "database": "[databasename]" };

sql.setDefaultConfig( config );

sql.execute( {  
    query: "TRUNCATE TABLE RAWSchemaKey " +
            "TRUNCATE TABLE RAWSchemaVal"
} ).then( function( results ) {
    console.log( results );
}, function( err ) {
    console.log( "Something bad happened:", err );
} );

CanvasDataAPICall('', function(result) { respAPIObj=result; respAPIObjSchema=respAPIObj.schema

  flattenResult = JSON.flatten(respAPIObjSchema);

  console.log("Starting K Loop");

  var i = 0;

  for (var k in flattenResult){
        if (flattenResult.hasOwnProperty(k)) {

            try {
                value0=JSON.stringify(k);
                value1=JSON.stringify(flattenResult[k]);

                var flatten = flattenResult[k];
                var resultK = "";
                var resultF = "";

                if (!k)
                    resultK = 'undefined';

                if (!flatten)
                    resultF = 'undefined';

                if (k != 'undefined' && flatten != 'undefined')
                {                   

                    sql.execute( {
                        procedure: "sp_canvas",
                        params: {
                            Id: {
                                type: sql.INT,
                                val: i
                            },
                            schKey: {
                                type: sql.VARCHAR(4096),
                                val: k
                            },
                            schVal: {
                                type: sql.VARCHAR(4096),
                                val: flattenResult[k]
                            }
                        }
                    } );      

                    i = i + 1;
                }
            }
            catch(err) {
                console.log(err + " error at :" + k);
            }

        }
    }

});

[DCPCanvas]

Note: above is the workaround. We haven't really identified the problem. Ideally, the problem should be identified to know whether there are other issues to address.

[PeterWenTW]

I will try it out!

I am thinking to create array iterator object to contain key/value pair for each values in the schKey and schVal. However, I will try your workaround first.

[PeterWenTW]

Thank You! With minor modify on sp_canvas as below, the issue looks like resolved.

SET IDENTITY_INSERT dbo.RAWSchemaKey ON insert into [dbo].[RAWSchemaKey] (ID, schKey) values (@Id, @schKey) SET IDENTITY_INSERT dbo.RAWSchemaKey OFF SET IDENTITY_INSERT dbo.RAWSchemaVal ON insert into [dbo].[RAWSchemaVal] (ID, [schVal]) values (@Id, @schVal) SET IDENTITY_INSERT dbo.RAWSchemaVal Off

[wjones20]

Hi everyone -- Just a note that I will have a CanvasDataViewer table on Hack Night (Wed, 6/25) at InstructureCarn. I deeply appreciate everyone's efforts to help each other with CDV here in the Issues threads. If you're attending the conference I'd enjoy a chance to meet with you and talk about the future of this project. Cheers, Bill