PrepareStatement.execute() with Geometry / Geography : TypeError: parameter.type.validate is not a function

[markddrake]

The following code

"use strict"
const sql = require('mssql');

   const connectionDetails = {
      server    : "192.168.1.250"
     ,user      : "sa"
     ,database  : "clone"
     ,password  : "oracle"
	 ,options: {
        encrypt: false // Use this if you're on Windows Azure

      }
    }
  
async function main() {  

  let conn
  let results
  try {

    conn = new sql.ConnectionPool(connectionDetails);
	await conn.connect()
	await conn.query(`SET QUOTED_IDENTIFIER ON`);
    
    const statement = `if object_id('"dbo"."T1"','U') is NULL create table "dbo"."T1" ("location" geometry);`
    const results = await conn.query(statement);
    
    const ps = new sql.PreparedStatement(conn);
    ps.input('P1',sql.Geometry);
    ps.prepare(`insert into "dbo"."T1" ("location") values (@P1)`);
    resuls = await ps.execute({P1:null});
  } catch (e) {
    console.log('Failed');
    console.log(e);
  }
  
  conn.close();
}

main();

Results in

C:\Development\YADAMU\MSSQL\node\node_modules\tedious\lib\request.js:191
        var _value = parameter.type.validate(parameter.value);
                                    ^

TypeError: parameter.type.validate is not a function
    at Request.validateParameters (C:\Development\YADAMU\MSSQL\node\node_modules\tedious\lib\request.js:191:37)
    at Connection.callProcedure (C:\Development\YADAMU\MSSQL\node\node_modules\tedious\lib\connection.js:1427:15)
    at Immediate.parent.acquire (C:\Development\YADAMU\MSSQL\node\node_modules\mssql\lib\tedious.js:1060:20)
    at runCallback (timers.js:697:11)
    at tryOnImmediate (timers.js:667:5)
    at processImmediate (timers.js:649:5)

C:\Development\YADAMU\MSSQL>

Admitted newbie to MSQL so apologies in advance if I'm being stupid. Similar behavior is seen with Geography. Also note the error is not 'caught' by the try/catch block..

[markddrake]

I think UniqueIdentifier also exhibits this behavior.

[dhensby]

What version of mssql are you using?

[dhensby]

@markddrake we have an issue template for multiple reasons:

1. It helps us help you as efficiently as possible; we don't have to write mundane questions like "What version of mssql are you using?"
2. It saves you time; you don't have to answer mundane questions like "What version of mssql are you using?"
3. Any time spent by people trying to help you is completely voluntary and comes out of their own time and good will, you probably want to keep these people happy by using their issue template.

I'm closing this issue until you have provided the information the issue template requests

[markddrake]

PrepareStatement.execute() with Geometry / Geography raises TypeError: parameter.type.validate is not a function

Expected behaviour:

Error should not be thrown, or these types should be documented as unsupported and workarounds provided. For instance it looks like you can work around UniqueIdentifier by binding as CHAR(36).
See: ### Expected behaviour:

Actual behaviour:

mssql rasies " TypeError: parameter.type.validate is not a function" when performing a prepare operation on a prepared statement that has an input defined using Geography, Geometry or UniqueIdentifier.

Configuration:

Software versions

• NodeJS: v10.6.0
• node-mssql: "version": "4.2.2",
• SQL Server:SQL Server 2017 Developer Edition

[markddrake]

David, could you please re-open the issue now.. I really think it would not be unreasonable to leave an issue open for at least 24 hours afte requesting additional information.

[markddrake]

Please note I have also updated the standalone testcase.

[dhensby]

Thanks for providing the requested information.

Have you performed any debugging or attempted any remedial steps to get this working?

[markddrake]

Note sure what I can do, if you have any pointers I'll be happy to try it. Basically the code I have works fine except when I try to bind sql.Geography, sql.Geometry and sql.UniqueIdentifier. Obviously I have already boiled down a much larger complex application into the standalone testcase provided above.

[dhensby]

@markddrake have you tried executing the code with a debugger enabled to see where the error is originating; possibly identifying a fix that could be applied?

[jameshowe]

Bit of a different callstack for me:

TypeError: column.type.writeTypeInfo is not a function
    at Object.writeParameterData (<path>/node_modules/tedious/lib/data-types/tvp.js:38:19)
    at new RpcRequestPayload (<path>/node_modules/tedious/lib/rpcrequest-payload.js:95:12)
    at Connection.callProcedure (<path>/node_modules/tedious/lib/connection.js:1459:51)
    at parent.acquire (<path>/node_modules/mssql/lib/tedious.js:1079:20)
    at _acquire.then.connection (<path>/node_modules/mssql/lib/base.js:174:42)
    at process._tickCallback (internal/process/next_tick.js:68:7)

There doesn't seem to be any form of workaround using the sql.Geography type that I've found. My workaround for the minute is an SP that accepts the geodata as a string and then parsing it at the DB side.

[willmorgan]

This is caused by:

• "parameter.type.validate is not a function" when using "geometry" data type tedious#722
• UDT parameter for requests tedious#487

There are a couple of things we can do here:

• Document the non-support of geometry/geography fields in prepared statements; maybe Tedious brought in type validation after the geom/geog types were introduced.
• Contribute back and add some noop validate functions to Tedious.

I can see tests in this project: https://github.com/tediousjs/node-mssql/blob/master/test/common/tests.js#L151-L193

@patriksimek do you have any recommendation on how to proceed?

[dhensby]

closing as stale and tedious error