module.exports["chain dorm transaction tests"] = function(beforeExit, assert) { client.query("begin;", function(err, result) { console.log('begin finished'); if (!err) { } else { console.log('error in begin'); assert.ok(false); } }); setTimeout(function() { client.query("savepoint first;", function (err, result) { if (err) { console.log("error in savepoint"); assert.ok(false); } else { console.log('savepoint set'); } }); }, 500); setTimeout(function() { client.query("select raise_notice();", function (err, result) { if (err) { console.log("error in raise_notice()"); assert.ok(false); } else { console.log('procedure call finished'); } }); }, 1000); setTimeout(function() { client.query("rollback to first;", function (err, result) { if (err) { console.log("error in rollback to"); assert.ok(false); } else { //done(); console.log('rollback successful'); } }); }, 1500); setTimeout(function() { client.query("commit;", function (err, result) { if (err) { console.log("error in commit"); assert.ok(false); } else { console.log('commit finished'); } }); }, 2000); setTimeout(function() { client.end(); }, 2500); assert.ok(true); };
query.on('end', function() { client.end(); });
/* * app: express server */ function setup(app, settings) { var connectionString = 'tcp://' + settings.psqlUser + ':' + settings.psqlPass + '@' + settings.psqlHost + '/' + settings.psqlName; if (client === null) { client = new pg.Client(connectionString); client.connect(); } var cache = lru({ max: 500, maxAge: 1000 * 60 * 60 // 1 hour }); function useCache(req, res, next) { var etag = req.headers['if-none-match']; if (etag !== undefined && cache.get(req.url) === etag) { res.set('ETag', etag); res.send(304); return; } var end = res.end; res.end = function (body) { var etag = this.get('ETag'); if (etag === undefined) { var hash = crypto.createHash('md5'); hash.update(body); etag = '"' + hash.digest('base64') + '"'; res.set('ETag', etag); } cache.set(req.url, etag); end.call(res, body); }; return next(); } // Get parcels // Filter to include only parcels inside a bounding box or only parcels that // intersect a point. // Use outputBuilder to generate the appropriate type of output function getParcels(req, response, outputBuilder) { var bbox = req.query.bbox; var lon = req.query.lon; var lat = req.query.lat; var query; var coordString; var coords; var output; var error; var i; // Require a filter if (bbox === undefined && (lon === undefined || lat === undefined)) { response.send(413); return; } if (bbox !== undefined) { // Bounding box query // Don't allow both filters at once if (lon !== undefined || lat !== undefined) { response.send(400); return; } // Split bounding box into coordinates coordString = bbox.split(','); if (coordString.length !== 4) { response.send(400); return; } // Convert coordinates to numbers coords = []; for (i = 0; i < 4; i += 1) { coords[i] = parseFloat(coordString[i]); // Make sure the conversion worked if (isNaN(coords[i])) { response.send(400); return; } } query = client.query({ text: 'SELECT object_id, name1, name2, source, created, ST_AsGeoJSON(wkb_geometry) AS polygon, ST_AsGeoJSON(ST_Centroid(wkb_geometry)) AS centroid, GeometryType(wkb_geometry) as type FROM objects WHERE ST_Intersects(wkb_geometry, ST_SetSRID($1, 4326))', values: [bboxToPolygon(coords)], name: 'parcelBBoxQuery' }); } else { // Point query // Convert coordinates to numbers lat = parseFloat(lat); lon = parseFloat(lon); if (isNaN(lat) || isNaN(lon)) { response.send(400); return; } query = client.query({ text: 'SELECT object_id, name1, name2, source, created, ST_AsGeoJSON(wkb_geometry) AS polygon, ST_AsGeoJSON(ST_Centroid(wkb_geometry)) AS centroid, GeometryType(wkb_geometry) as type FROM objects WHERE ST_Contains(wkb_geometry, ST_SetSRID($1, 4326))', values: ['POINT(' + lon + ' ' + lat + ')'], name: 'parcelPointQuery' }); } output = []; query .on('row', function (row, result) { try { outputBuilder.addParcel(clean(row.object_id), clean(row.name1) + ' ' + clean(row.name2), JSON.parse(row.polygon), JSON.parse(row.centroid), clean(row.type)); } catch (e) { console.log(row); error = e; } }) .on('error', function (e) { console.log(e.message); error = e; }) .on('end', function (result) { if (error) { console.log(error); response.send(500); } else { response.send(outputBuilder.generate()); } }); } // Get parcels // Filter to include only parcels inside a bounding box or only parcels that // intersect a point. // We do not allow filtering by both, and we require one of the filters. // GET http://localhost:3000/api/parcels?bbox=-{SW_LON},{SW_LAT},{NE_LON},{NE_LAT} // GET http://localhost:3000/api/parcels?bbox=-83.0805,42.336,-83.08,42.34 // GET http://localhost:3000/api/parcels?lon={LONGITUDE}&lat={LATITUDE} // GET http://localhost:3000/api/parcels?lon=-83.08076&lat=42.338 app.get('/api/parcels', function(req, response) { return getParcels(req, response, new CustomParcelArrayBuilder()); }); // Get parcels as a GeoJSON FeatureCollection // Filter to include only parcels inside a bounding box or only parcels that // intersect a point. // We do not allow filtering by both, and we require one of the filters. // GET http://localhost:3000/api/parcels?bbox=-{SW_LON},{SW_LAT},{NE_LON},{NE_LAT} // GET http://localhost:3000/api/parcels?bbox=-83.0805,42.336,-83.08,42.34 // GET http://localhost:3000/api/parcels?lon={LONGITUDE}&lat={LATITUDE} // GET http://localhost:3000/api/parcels?lon=-83.08076&lat=42.338 app.get('/api/parcels.geojson', useCache, function(req, response, next) { return getParcels(req, response, new GeoJSONBuilder()); }); }
client.query(drop_tables, function(err, result) { if(err){ console.log(err); } console.log('Tables Dropped : users + letters'); client.end(); process.exit(code=0); });
'use strict'; var express = require('express'); var app = express(); var morgan = require('morgan'); var swig = require('swig'); var makesRouter = require('./routes'); var fs = require('fs'); var path = require('path'); var mime = require('mime'); var bodyParser = require('body-parser'); var socketio = require('socket.io'); var pg = require('pg'); var conString = 'postgres://localhost:5432/twitterdb'; var client = new pg.Client(conString); client.connect(); // templating boilerplate setup app.set('views', path.join(__dirname, '/views')); // where to find the views app.set('view engine', 'html'); // what file extension do our templates have app.engine('html', swig.renderFile); // how to render html templates swig.setDefaults({ cache: false }); // logging middleware app.use(morgan('dev')); // body parsing middleware app.use(bodyParser.urlencoded({ extended: true })); // for HTML form submits app.use(bodyParser.json()); // would be for AJAX requests
client.connect(function () { client.on('drain', client.end.bind(client)); client.query(sql, function (err, data) { next(); }); });
var pg = require('pg') , config = require('./config') , conString = config.getConnectionString() , client , query; console.log('Libraries Imported'); client = new pg.Client(conString); client.connect(function(err) { if(err) { return console.error('could not connect to postgres', err); } console.log('Connection to Postgres succsefully established.'); }); /* -------------------- Queries -------------------- */ var insert_letters_table = [ 'create table IF NOT EXISTS letters (', 'id SERIAL PRIMARY KEY,', 'letter VARCHAR(2),', 'user_id INTEGER,', 'created TIMESTAMP DEFAULT current_timestamp', ');' ] insert_letters_table = insert_letters_table.join(" ");
module.exports.readData = function(callback){ console.log("in DB read for vendor read"); function onFailure(err) { process.stderr.write("Select Failed: " + err.message + "\n"); } if(this.dbVendor == dbVendor.Sqlite){ }else if (this.dbVendor == dbVendor.Postgress){ var conString = process.env.DATABASE_URL ; var client = new pg.Client(conString); client.connect(); console.log("Length : " + Object.keys(this.fields).length); if(Object.keys(this.fields).length == 3) { var row= client.query("select * from "+this.table + " where " + this.fields.key + this.fields.operator + this.fields.value,function(err, result) { if (err) onFailure(err); console.log('Query_result before return :: ' + JSON.stringify(row)); client.end(); callback (result.rows); }); } else if(Object.keys(this.fields).length == 4) { console.log("select "+ this.fields.inoperator + " from "+this.table + " where " + this.fields.key + this.fields.operator + this.fields.value); var row= client.query("select "+ this.fields.inoperator + " from "+this.table + " where " + this.fields.key + this.fields.operator + this.fields.value,function(err, result) { if (err) onFailure(err); console.log('Query_result before return :: ' + JSON.stringify(row)); client.end(); callback (result.rows); }); } else if(Object.keys(this.fields).length == 6) { console.log("select * from " + this.table + " where " + this.fields.key + this.fields.operator + this.fields.value + " and " + this.fields.key1 + this.fields.operator1 + this.fields.value1); var row = client.query("select * from " + this.table + " where " + this.fields.key + this.fields.operator + this.fields.value + " and " + this.fields.key1 + this.fields.operator1 + this.fields.value1 ,function(err,result){ if (err) onFailure(err); console.log('Query_result before return when fields not null :: ' + JSON.stringify(row)); client.end(); callback (result.rows); }); } else { console.log("select * from " + this.table + " where " + this.fields.key + this.fields.operator + this.fields.value + " and " + this.fields.key1 + this.fields.operator1 + this.fields.value1 + " and " + this.fields.key2 + this.fields.operator2 + this.fields.value2); var row = client.query("select * from " + this.table + " where " + this.fields.key + this.fields.operator + this.fields.value + " and " + this.fields.key1 + this.fields.operator1 + this.fields.value1 + " and " + this.fields.key2 + this.fields.operator2 + this.fields.value2 ,function(err,result){ if (err) onFailure(err); console.log('Query_result before return when fields not null :: ' + JSON.stringify(row)); client.end(); callback (result.rows); }); } }else if (this.dbVendor == dbVendor.MySQL){ } }
var pg = require("pg"); var conString = process.env.DATABASE_URL || "pg://*****:*****@localhost:5432/forum"; var client = new pg.Client(conString); client.connect(); //client.query("CREATE DATABASE forum") client.query( "CREATE TABLE IF NOT EXISTS questions (" + "id serial, " + "title varchar(255)," + "body text, " + "created TIMESTAMP not null default NOW(), " + "user_id bigint, " + "votes int DEFAULT 0, " + "views int DEFAULT 0, " + "category_id int, " + "PRIMARY KEY(id) )" ); client.query( "CREATE TABLE IF NOT EXISTS users(" + "username varchar(100), " + "avatar varchar(255), " + "social_id bigint, " + "PRIMARY KEY(social_id) )" );
operdb.insertaData = function (data){ client.query('INSERT INTO estacion_chx (id,fecha_hora,dia,registro) VALUES ($1,$2,$3,$4)',data); };
operdb.finaliza= function(){ client.end(); };
stream.on('close', function onPgClose(){ client.end(); console.log('pg done'); });
var pg = require('pg') var elastic = require('elasticsearch') var QueryStream = require('pg-query-stream') var ElasticsearchBulkIndexStream = require('elasticsearch-bulk-index-stream'); var transform = require('stream-transform'); var client = new pg.Client(process.env.PG_CONNECTION_STRING); var elasticClient = new elastic.Client({ host: process.env.ELASTIC_HOST, maxRetries: 10, requestTimeout: 60000 }); var query = new QueryStream('SELECT * FROM companies ORDER BY id'); client.connect(function onPgConnect(err) { var stream, elasticStream, transformer; if(err) throw err; stream = client.query(query); elasticStream = new ElasticsearchBulkIndexStream(elasticClient, { highWaterMark: process.env.BATCH_SIZE || 10000, flushTimeout: 500 }); transformer = transform(function(record) { console.log('write', record.id); return { index: 'companies_index', type: 'companies-type',
setTimeout(function() { client.end(); }, 2500);
connect: function(callback) { client.connect(callback); },
client.query(query, function (err, result) { console.log(err); if (err) error(errorMessages.postgresQueryError); success(result); client.end(); });
handle.write(sql); } else { handle.query(sql, callback); } queue = []; } }; //***************************************************************************** var writeStream = fs.createWriteStream(myFile); writeStream.write("DELETE FROM "+ myTable +" WHERE country='" + myCountry + "' AND city='" + myCity + "';\n"); var myInserter = new BulkInsert(writeStream, "INSERT INTO " + myTable + " (height, footprint, country, city) VALUES {values};\n", 5000); var sql = new pg.Client('postgres://' + pgUsername + ':' + pgPassword + '@' + pgHost + '/' + pgDatabase); sql.connect(); var query = "\ SELECT\ " + pgHeightField + " AS height,\ ST_AsText(ST_ExteriorRing(" + pgFootprintField + ")) AS footprint\ FROM\ " + pgTable + "\ WHERE " + filterByBBox(bbox) + "\ ORDER BY\ height DESC\ "; sql.query(query, function(err, res) { sql.end();
function runCommand(filename, databaseOptions) { const conn = new pg.Client({ user: databaseOptions.username, password: databaseOptions.password || '', database: databaseOptions.database, host: databaseOptions.host, port: databaseOptions.port, }); conn.connect((err) => { if (err) { error(err.message); process.exit(1); } manifest.readFromFile(filename) .catch((err) => { error(err.message); process.exit(1); }) .then((manifest) => manifest.validateScriptsExist() .catch((err) => { error(err.message); process.exit(1); }) .then(() => manifest)) .then((manifest) => { const status = new EventEmitter(); let progress = null; status.on('versionStart', (data) => { console.log(`${data.manifest.product}@${data.version.name}`); }); status.on('taskStart', (data) => { progress = ora(data.task.name).start(); }); status.on('taskEnd', (data) => { if (data.result === 'success') { progress.succeed(); } else { progress.fail(); console.error(); console.error(`${chalk.bgRed.white.bold(' ERROR ')} running task ${chalk.bold(data.task.name)} from version ${chalk.bold(data.version.name)}`); console.error(); console.error(' ' + data.error.message); console.error(); } }); status.on('versionEnd', (data) => { if (data.result === 'success') { console.log(); } }); return versiondb.upgrade(conn, manifest, status) .then((result) => { if (result.initialVersion === result.currentVersion.name) { console.log(`👍 ${chalk.bgGreen.black(' CURRENT ')} ${result.manifest.product} is already at ${result.currentVersion.name}`); } else { console.log(`👍 ${chalk.bgGreen.black(' UPDATED ')} ${result.manifest.product} is now at version ${result.currentVersion.name}`); } process.exit(); }) .catch(() => process.exit(1)); }) .catch((err) => console.log(err)); }); }
// Get PG config'd // // var pgConString = ''; var pgConConfig = { database: "cinemaplate_dev", host: "localhost", port: 5432 } // if (process.env.NODE_ENV !== 'production') { // // If trying to connect to DB remotely (ie, dev environment) // // we need to add the ssl flag. // pgConString = process.env.DATABASE_URL + '?ssl=true'; // } else { // pgConString = process.env.DATABASE_URL; // } var pgClient = new pg.Client(pgConConfig); pgClient.connect(function(err) { if (err) { return console.log('could not connect to postgres', err); } }) // // START Movie insert // reddit.getMovies() .then(function(res) { return movie.getMovieDB(res) }) .then(function(movieData) {
var pg = require('pg') var assert = require('assert') var gonna = require('gonna') var _ = require('lodash') var concat = require('concat-stream') var through = require('through') var QueryStream = require('../') var client = new pg.Client() var connected = gonna('connect', 100, function() { var stream = new QueryStream('SELECT * FROM asdf num', []) var query = client.query(stream) query.on('error', gonna('emit error', 100, function(err) { assert(err) assert.equal(err.code, '42P01') })) var done = gonna('keep connetion alive', 100) client.query('SELECT NOW()', done) }) client.connect(connected) client.on('drain', client.end.bind(client))
client.query(insert_users_table, function(err, result) { if(err){ console.log(err); } console.log("Done"); client.end(); process.exit(code=0); });
var pg = require('pg'); var conString = 'tcp://*****:*****@localhost:5432/timetrack'; var client = new pg.Client(conString); client.connect(); client.query( 'INSERT INTO users ' + "(name) VALUES ($1) RETURNING name", ['Bao Trung'], function(err, result) { if (err) { throw err; } console.log(result.rows[0].name); } ); console.log("Finished");
var http = require("http"); var pg = require("pg"); var server = http.createServer(function(request,response){ response.writeHead(200,{"content-type":"text/html"}); response.write("hello cool world "); response.end(); }); // http.set('port', (process.env.PORT || 5000)); // postgres://fkfzvpdebvbzdh:qICgOMOWPnx9IPifa2@*HOST*:*PORT:/*DATABASE* var PORT = (process.env.PORT || 5000); var connectionString = "postgres://*****:*****@ec2-54-243-249-165.compute-1.amazonaws.com:5432/dei9lujiig8mcj"; server.listen(PORT,function(){ console.log("Server listening on: http://localhost:%s %s", PORT , connectionString); }); console.log('hello world' + process.env.PORT + connectionString + pg); var client = new pg.Client(connectionString); client.connect(); var query1 = client.query('CREATE TABLE IF NOT EXISTS menu_table(day int not null, time int not null, menu text, PRIMARY KEY(day, menu))'); query1.on('end', function() { console.log("Menu today table successfully created"); });
const input = process.argv[2]; const pg = require("pg"); const settings = require("./settings"); // settings.json const client = new pg.Client({ user : settings.user, password : settings.password, database : settings.database, host : settings.hostname, port : settings.port, ssl : settings.ssl }); console.log('searching...'); client.connect((err) => { if (err) { return console.error("Connection Error", err); } client.query('SELECT first_name, last_name, birthdate FROM famous_people WHERE last_name = $1',[input], (err, result) => { if (err) { return console.error("error running query", err); } console.log(`Found ${result.rows.length} person(s) by the name ${input}:`) let i = 1; for (const entry of result.rows) { // find out how to acccess index in for/of loop to add numerator console.log(`${i++} - ${entry.first_name} ${entry.last_name}, born ${entry.birthdate}`) } client.end(); });
var pg = require('pg'); var connectionString = process.env.DATABASE_URL || 'postgres://localhost:5432/topshelf'; var client = new pg.Client(connectionString); client.connect(); var query = client.query('CREATE TABLE people(id SERIAL PRIMARY KEY, text VARCHAR(40) not null, complete BOOLEAN)'); query.on('end', function() { client.end(); });
function updateKlijent(req, res) { var data = JSON.parse(req.swagger.params.data.value); var id = req.swagger.params.id.value; var client = new pg.Client(conString); client.connect(function(err) { if (err) { return console.error('could not connect to postgres', err); client.end(); } else { var query = `update sadrzaj set name = '` + data.name + `', izmjenjen_ts = NOW() where id = ` + id + ` `; console.log(query); client.query(query, function(err, result) { if (err) { return console.error('error running query', err); client.end(); } else { for (var prop in data) { if (data[prop] === '') { if (prop === 'oib' || prop === 'postanskibroj' || prop === 'pravnioblik' || prop === 'edkategorija' || prop === 'vrstapaketa' || prop === 'broj_stanovnika' || prop === 'zupanija') { data[prop] = null; } } } query = `update klijenti set id = ` + id + `, name = '` + data.name + `', pravnioblik = ` + data.pravnioblik + `, oib = ` + data.oib + `, ulica = '` + data.ulica + `', postanskibroj = ` + data.postanskibroj + ` , grad = '` + data.grad + `', zupanija = ` + data.zupanija + `, broj_stanovnika = ` + data.broj_stanovnika + `, zadnjiKontakt = '` + data.zadnjikontakt + `', edkategorija = ` + data.edkategorija + `, vrstapaketa = ` + data.vrstapaketa + `, objaveKlijent = '` + data.objaveklijent + `', telefon = '` + data.telefon + `', fax = '` + data.fax + `', email = '` + data.email + `', web = '` + data.web + `', datumUkljucenja = '` + data.datumukljucenja + `', datumIsteka = '` + data.datumisteka + `', napomena = '` + data.napomena + `' where id = ` + id + ` `; console.log(query); client.query(query, function(err, result) { if (err) { return console.error('error running query', err); client.end(); } else { res.json(data); client.end(); } }) } }) } }) }
var pg = require('pg'); var postgres = new pg.Client('tcp://*****:*****@127.0.0.1/gisdb'); postgres.connect(); var http = require('http').createServer(); var io = require('socket.io').listen(http); http.listen('8088'); String.prototype.format = function() { var s = this, i = arguments.length; while (i--) { s = s.replace(new RegExp('\\{' + i + '\\}', 'gm'), arguments[i]); } return s; }; io.sockets.on('connection', function (socket) { socket.on('getHoles', function (data) { postgres.query("SELECT ST_AsGeoJSON(hole) FROM holes;", function (err, result) { if (err) { console.log(err); } else { var holes = []; result.rows.forEach(function(row) { var point = JSON.parse(row.st_asgeojson); holes.push({ lat: point.coordinates[0], lon: point.coordinates[1]
function saveNoviKlijent(req, res) { var data = JSON.parse(req.swagger.params.data.value); //console.log(data); //console.log(data.name); var client = new pg.Client(conString); var sadrzajId = client.connect(function(err) { if (err) { return console.error('could not connect to postgres', err); client.end(); } else { //console.log(podaci); var query = `insert into sadrzaj (name, kreiran_ts, vrsta_id ) values ('` + data.name + `',NOW(),1) RETURNING id`; //console.log(query); client.query(query, function(err, result) { if (err) { return console.error('error running query', err); client.end(); } else { var id = result.rows[0].id; data['id'] = id; console.log(data); for (var prop in data) { if (data[prop] === '') { if (prop === 'oib' || prop === 'postanskibroj' || prop === 'pravnioblik' || prop === 'edkategorija' || prop === 'vrstapaketa' || prop === 'broj_stanovnika' || prop === 'zupanija') { data[prop] = null; } } } query = `insert into klijenti (id, name, pravnioblik, oib, ulica, postanskibroj, grad, zupanija, zadnjiKontakt, broj_stanovnika, edkategorija, vrstapaketa, objaveKlijent, telefon, fax, email, web, datumUkljucenja, datumIsteka, napomena ) values(` + id + `, '` + data.name + `', ` + data.pravnioblik + `, ` + data.oib + `, '` + data.ulica + `', ` + data.postanskibroj + ` , '` + data.grad + `', ` + data.zupanija + `, '` + data.zadnjikontakt + `', ` + data.broj_stanovnika + `, ` + data.edkategorija + `, ` + data.vrstapaketa + `, '` + data.objaveklijent + `', '` + data.telefon + `', '` + data.fax + `', '` + data.email + `', '` + data.web + `', '` + data.datumukljucenja + `', '` + data.datumisteka + `', '` + data.napomena + `' )`; console.log(query); client.query(query, function(err, result) { if (err) { return console.error('error running query', err); client.end(); } else { } }) res.json('Uspjesno spremljeno'); client.end(); } }) } }) }
var pg = require('pg'); var connectionString = require('../config.js').db.databaseUrl; // Opens a new database connection var client = new pg.Client(connectionString); client.connect( function(error) { if (error) { throw error; } }); // Handles any database errors module.exports.handleError = function(error, callback) { if(error) { callback(error); return; } }; // Handles any database queries module.exports.makeQuery = function(queryString, parameters, callback) { client.query(queryString, parameters, function(error, result) { if(error) { callback(error, null); } else { callback(null, result); } }); };
constructor () { client = new pg.Client(settings.connectString); client.connect(); }