How to use the mssql.VarChar function in mssql
To help you get started, we’ve selected a few mssql examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
dymajo / waka / server-worker / lines / regions / nz-akl.js View on Github 
let getOperator = function(route) {
if (route >= routes.length) {
log('nz-akl'.magenta, 'Completed Lookup of Agencies')
return
}
// caches the operator
const sqlRequest = connection.get().request()
sqlRequest.input('route_short_name', sql.VarChar(50), routes[route])
sqlRequest.query(`
SELECT top(1)
agency_id
FROM routes
where
route_short_name = @route_short_name
`).then(result => {
// query was successful
if (result.recordset.length > 0) {
const agency_id = result.recordset[0].agency_id
lineColors[routes[route]] = getColor(agency_id, routes[route])
lineOperators[routes[route]] = agency_id
} else {
log('could not find agency for', routes[route])
}
getOperator(route + 1)conf.status = conf.status || 'empty'
conf.startpolicy = conf.startpolicy || 'auto'
conf.dbconfig = conf.dbconfig || 'slave'
conf.dbname =
conf.dbname ||
conf.prefix.replace(/-/g, '_') +
'_' +
conf.version.replace(/-/g, '_').replace(/\./g, '_')
const sqlRequest = connection.get().request()
sqlRequest.input('prefix', sql.VarChar(50), conf.prefix)
sqlRequest.input('version', sql.VarChar(50), conf.version)
sqlRequest.input('status', sql.VarChar(50), conf.status)
sqlRequest.input('startpolicy', sql.VarChar(50), conf.startpolicy)
sqlRequest.input('dbconfig', sql.VarChar(50), conf.dbconfig)
sqlRequest.input('dbname', sql.VarChar(100), conf.dbname)
sqlRequest
.query(
`
INSERT INTO workers (prefix, version, status, startpolicy, dbconfig, dbname)
VALUES (@prefix, @version, @status, @startpolicy, @dbconfig, @dbname)
`
)
.then(() => {
log('Added Worker', conf.prefix, conf.version)
WorkerManager.load()
.then(resolve)
.catch(reject)
})
.catch(reject)
})
},getTable(name) {
const table = new sql.Table(name)
if (name === 'agency') {
table.columns.add('agency_id', sql.VarChar(50), {nullable: false})
table.columns.add('agency_name', sql.VarChar(100), {nullable: false})
table.columns.add('agency_url', sql.VarChar(100), {nullable: false})
table.columns.add('agency_timezone', sql.VarChar(100), {nullable: false})
table.columns.add('agency_lang', sql.VarChar(50), {nullable: true})
table.columns.add('agency_phone', sql.VarChar(50), {nullable: true})
table.columns.add('agency_fare_url', sql.VarChar(100), {nullable: true})
table.columns.add('agency_email', sql.VarChar(50), {nullable: true})
} else if (name === 'stops') {
table.columns.add('stop_id', sql.VarChar(100), {nullable: false})
table.columns.add('stop_code', sql.VarChar(50), {nullable: true})
table.columns.add('stop_name', sql.VarChar(100), {nullable: false})
table.columns.add('stop_desc', sql.VarChar(150), {nullable: true})
table.columns.add('stop_lat', sql.Decimal(10,6), {nullable: false})
table.columns.add('stop_lon', sql.Decimal(10,6), {nullable: false})
table.columns.add('zone_id', sql.VarChar(50), {nullable: true})
table.columns.add('stop_url', sql.VarChar(100), {nullable: true})
table.columns.add('location_type', sql.Int, {nullable: true})
table.columns.add('parent_station', sql.VarChar(100), {nullable: true})table.columns.add('block_id', sql.VarChar(100), {nullable: true})
table.columns.add('shape_id', sql.VarChar(100), {nullable: true})
table.columns.add('wheelchair_accessible', sql.Int, {nullable: true})
table.columns.add('bikes_allowed', sql.Int, {nullable: true})
} else if (name === 'stop_times') {
table.columns.add('trip_id', sql.VarChar(100), {nullable: false})
table.columns.add('arrival_time', sql.Time(0), {nullable: false})
table.columns.add('departure_time', sql.Time(0), {nullable: false})
table.columns.add('arrival_time_24', sql.Bit, {nullable: false})
table.columns.add('departure_time_24', sql.Bit, {nullable: false})
table.columns.add('stop_id', sql.VarChar(50), {nullable: false})
table.columns.add('stop_sequence', sql.Int, {nullable: false})
table.columns.add('stop_headsign', sql.VarChar(50), {nullable: true})
table.columns.add('pickup_type', sql.Int, {nullable: true})
table.columns.add('drop_off_type', sql.Int, {nullable: true})
table.columns.add('shape_dist_traveled', sql.VarChar(50), {nullable: true})
table.columns.add('timepoint', sql.Int, {nullable: true})
} else if (name === 'calendar') {
table.columns.add('service_id', sql.VarChar(100), {nullable: false})
table.columns.add('monday', sql.Bit, {nullable: false})
table.columns.add('tuesday', sql.Bit, {nullable: false})
table.columns.add('wednesday', sql.Bit, {nullable: false})
table.columns.add('thursday', sql.Bit, {nullable: false})
table.columns.add('friday', sql.Bit, {nullable: false})
table.columns.add('saturday', sql.Bit, {nullable: false})
table.columns.add('sunday', sql.Bit, {nullable: false})
table.columns.add('start_date', sql.Date, {nullable: false})
table.columns.add('end_date', sql.Date, {nullable: false})
} else if (name === 'calendar_dates') {
table.columns.add('service_id', sql.VarChar(100), {nullable: false})
table.columns.add('date', sql.Date, {nullable: false})
table.columns.add('exception_type', sql.Int, {nullable: false})async getTimetable(stopCode, routeId, date, direction, procedure = 'GetTimetable') {
const sqlRequest = connection
.get()
.request()
.input('stop_id', sql.VarChar(100), stopCode)
.input('route_short_name', sql.VarChar(50), routeId)
.input('date', sql.Date, date)
.input('direction', sql.Int, direction)
const result = await sqlRequest.execute(procedure)
return result.recordset
}getTable(name) {
const table = new sql.Table(name)
if (name === 'agency') {
table.columns.add('agency_id', sql.VarChar(50), {nullable: false})
table.columns.add('agency_name', sql.VarChar(100), {nullable: false})
table.columns.add('agency_url', sql.VarChar(100), {nullable: false})
table.columns.add('agency_timezone', sql.VarChar(100), {nullable: false})
table.columns.add('agency_lang', sql.VarChar(50), {nullable: true})
table.columns.add('agency_phone', sql.VarChar(50), {nullable: true})
table.columns.add('agency_fare_url', sql.VarChar(100), {nullable: true})
table.columns.add('agency_email', sql.VarChar(50), {nullable: true})
} else if (name === 'stops') {
table.columns.add('stop_id', sql.VarChar(100), {nullable: false})
table.columns.add('stop_code', sql.VarChar(50), {nullable: true})
table.columns.add('stop_name', sql.VarChar(100), {nullable: false})
table.columns.add('stop_desc', sql.VarChar(150), {nullable: true})
table.columns.add('stop_lat', sql.Decimal(10,6), {nullable: false})
table.columns.add('stop_lon', sql.Decimal(10,6), {nullable: false})
table.columns.add('zone_id', sql.VarChar(50), {nullable: true})
table.columns.add('stop_url', sql.VarChar(100), {nullable: true})
table.columns.add('location_type', sql.Int, {nullable: true})
table.columns.add('parent_station', sql.VarChar(100), {nullable: true})
table.columns.add('stop_timezone', sql.VarChar(100), {nullable: true})
table.columns.add('wheelchair_boarding', sql.Int, {nullable: true})
} else if (name === 'routes') {
table.columns.add('route_id', sql.VarChar(100), {nullable: false})
table.columns.add('agency_id', sql.VarChar(100), {nullable: true})
table.columns.add('route_short_name', sql.VarChar(50), {nullable: false})
table.columns.add('route_long_name', sql.VarChar(150), {nullable: false})
table.columns.add('route_desc', sql.VarChar(150), {nullable: true})
table.columns.add('route_type', sql.Int, {nullable: false})
table.columns.add('route_url', sql.VarChar(100), {nullable: true})function getNewDataPointsTable () {
var dataPointsTable = new mssql.Table('dataPoints')
dataPointsTable.create = true
dataPointsTable.columns.add('contractHash', mssql.VarChar(40), {nullable: false, primary: true})
dataPointsTable.columns.add('variableName', mssql.VarChar(50), {nullable: false, primary: true})
dataPointsTable.columns.add('blockNumber', mssql.BigInt, {nullable: false})
dataPointsTable.columns.add('value', mssql.VarChar(78), {nullable: false})
return dataPointsTable
}function getNewVariablesTable () {
var variablesTable = new mssql.Table('variables')
variablesTable.create = true
variablesTable.columns.add('contractHash', mssql.VarChar(40), {nullable: false, primary: true})
variablesTable.columns.add('variableName', mssql.VarChar(50), {nullable: false, primary: true})
variablesTable.columns.add('cachedFrom', mssql.BigInt, {nullable: true})
variablesTable.columns.add('cachedUpTo', mssql.BigInt, {nullable: true})
return variablesTable
}table.columns.add('stop_name', sql.VarChar(100), {nullable: false})
table.columns.add('stop_desc', sql.VarChar(150), {nullable: true})
table.columns.add('stop_lat', sql.Decimal(10,6), {nullable: false})
table.columns.add('stop_lon', sql.Decimal(10,6), {nullable: false})
table.columns.add('zone_id', sql.VarChar(50), {nullable: true})
table.columns.add('stop_url', sql.VarChar(100), {nullable: true})
table.columns.add('location_type', sql.Int, {nullable: true})
table.columns.add('parent_station', sql.VarChar(100), {nullable: true})
table.columns.add('stop_timezone', sql.VarChar(100), {nullable: true})
table.columns.add('wheelchair_boarding', sql.Int, {nullable: true})
} else if (name === 'routes') {
table.columns.add('route_id', sql.VarChar(100), {nullable: false})
table.columns.add('agency_id', sql.VarChar(100), {nullable: true})
table.columns.add('route_short_name', sql.VarChar(50), {nullable: false})
table.columns.add('route_long_name', sql.VarChar(150), {nullable: false})
table.columns.add('route_desc', sql.VarChar(150), {nullable: true})
table.columns.add('route_type', sql.Int, {nullable: false})
table.columns.add('route_url', sql.VarChar(100), {nullable: true})
table.columns.add('route_color', sql.VarChar(50), {nullable: true})
table.columns.add('route_text_color', sql.VarChar(50), {nullable: true})
} else if (name === 'trips') {
table.columns.add('route_id', sql.VarChar(100), {nullable: false})
table.columns.add('service_id', sql.VarChar(100), {nullable: false})
table.columns.add('trip_id', sql.VarChar(100), {nullable: false})
table.columns.add('trip_headsign', sql.VarChar(100), {nullable: true})
table.columns.add('trip_short_name', sql.VarChar(50), {nullable: true})
table.columns.add('direction_id', sql.Int, {nullable: true})
table.columns.add('block_id', sql.VarChar(100), {nullable: true})
table.columns.add('shape_id', sql.VarChar(100), {nullable: true})
table.columns.add('wheelchair_accessible', sql.Int, {nullable: true})
table.columns.add('bikes_allowed', sql.Int, {nullable: true})
} else if (name === 'stop_times') {table.columns.add('route_url', sql.VarChar(100), {nullable: true})
table.columns.add('route_color', sql.VarChar(50), {nullable: true})
table.columns.add('route_text_color', sql.VarChar(50), {nullable: true})
} else if (name === 'trips') {
table.columns.add('route_id', sql.VarChar(100), {nullable: false})
table.columns.add('service_id', sql.VarChar(100), {nullable: false})
table.columns.add('trip_id', sql.VarChar(100), {nullable: false})
table.columns.add('trip_headsign', sql.VarChar(100), {nullable: true})
table.columns.add('trip_short_name', sql.VarChar(50), {nullable: true})
table.columns.add('direction_id', sql.Int, {nullable: true})
table.columns.add('block_id', sql.VarChar(100), {nullable: true})
table.columns.add('shape_id', sql.VarChar(100), {nullable: true})
table.columns.add('wheelchair_accessible', sql.Int, {nullable: true})
table.columns.add('bikes_allowed', sql.Int, {nullable: true})
} else if (name === 'stop_times') {
table.columns.add('trip_id', sql.VarChar(100), {nullable: false})
table.columns.add('arrival_time', sql.Time(0), {nullable: false})
table.columns.add('departure_time', sql.Time(0), {nullable: false})
table.columns.add('arrival_time_24', sql.Bit, {nullable: false})
table.columns.add('departure_time_24', sql.Bit, {nullable: false})
table.columns.add('stop_id', sql.VarChar(50), {nullable: false})
table.columns.add('stop_sequence', sql.Int, {nullable: false})
table.columns.add('stop_headsign', sql.VarChar(50), {nullable: true})
table.columns.add('pickup_type', sql.Int, {nullable: true})
table.columns.add('drop_off_type', sql.Int, {nullable: true})
table.columns.add('shape_dist_traveled', sql.VarChar(50), {nullable: true})
table.columns.add('timepoint', sql.Int, {nullable: true})
} else if (name === 'calendar') {
table.columns.add('service_id', sql.VarChar(100), {nullable: false})
table.columns.add('monday', sql.Bit, {nullable: false})
table.columns.add('tuesday', sql.Bit, {nullable: false})
table.columns.add('wednesday', sql.Bit, {nullable: false})
