Heroku postgres error: query must be array when selecting data

Question

0.00/5 (No votes)

See more:

, +

so im making a dscord bot and i used sqlite, but becous emy host (heroku) i could no use that. thay said i needed to use postgres so i did im updating my code to use but i cant get it to work for creating my table i use this code

client.query('CREATE TABLE users(name text, money bigint);', (err, res) => {
  if (err) throw err;
  for (let row of res.rows) {
    console.log(JSON.stringify(row));
  }
  client.end();
});

that works fine the table is created.
then for insertinf data i use this:

client.query(`INSERT INTO users(name,money) SELECT ${message.author.id},0 WHERE NOT EXISTS(SELECT 1 FROM users WHERE name = '${message.author.id}'); `, (err, res) => {
        if (err) throw err;
        message.reply(`inserted data`);

    });

that also works fin i think.
but then when i wanted to get data with:

client.query(`SELECT * FROM users WHERE name = ?`, id,(err, row) => {
			if(err) throw err;
			console.log(id);
  			if (!row) return message.reply(`${name} has no money`);
  			
 	 		message.reply(`${name} has ${row.money} money`);
  		
		});

i get this error:

G:\dolphin casino\cmds\money.js:17
                        if(err) throw err;
                                ^

Error: Query values must be an array
    at Query.submit (G:\dolphin casino\node_modules\pg\lib\query.js:156:17)
    at Client._pulseQueryQueue (G:\dolphin casino\node_modules\pg\lib\client.js:356:24)
    at Client.query (G:\dolphin casino\node_modules\pg\lib\client.js:393:8)
    at Object.module.exports.run (G:\dolphin casino\cmds\money.js:16:10)
    at Client.bot.on.message (G:\dolphin casino\bot.js:80:14)
    at emitOne (events.js:96:13)
    at Client.emit (events.js:188:7)
    at MessageCreateHandler.handle (G:\dolphin casino\node_modules\discord.js\src\client\websocket\packets\handlers\MessageCreate.js:9:34)
    at WebSocketPacketManager.handle (G:\dolphin casino\node_modules\discord.js\src\client\websocket\packets\WebSocketPacketManager.js:103:65)
    at WebSocketConnection.onPacket (G:\dolphin casino\node_modules\discord.js\src\client\websocket\WebSocketConnection.js:333:35)

wich is weird becouse the name column is text not an array.

does someone know whats going on ?

What I have tried:

searching the internet. but i did not found an solution.

Posted 5-Apr-18 2:02am

dolfijn3000

Updated 5-Apr-18 2:17am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2018-04-05T02:18:00

Solution 1

When using placeholders as in your SELECT command you have to pass the values as array (enclosed by square brackets):

javascrip

client.query('SELECT * FROM users WHERE name = ?', [id], (err, row) => {

[EDIT]

Quote:
now i get an syntax error

The result object returned in row is an array of row objects. Assuming you do not have multiple recordsets with the same name, you can access the first result:

JavaScript

if (row.rowCount == 1) {
    message.reply(`${id} has ${row.rows[0]['money']} money`);
}
else if (row.rowCount == 0) {
    message.reply(`${id} not found`);
}

[/EDIT]

Posted 5-Apr-18 2:18am

Jochen Arndt

Updated 5-Apr-18 3:20am

v2

Comments

dolfijn3000 5-Apr-18 8:19am

now i get an syntax error

G:\dolphin casino\cmds\money.js:17
if(err) throw err;
^

error: syntax error at end of input
at Connection.parseE (G:\dolphin casino\node_modules\pg\lib\connection.js:545:11)
at Connection.parseMessage (G:\dolphin casino\node_modules\pg\lib\connection.js:370:19)
at TLSSocket.<anonymous> (G:\dolphin casino\node_modules\pg\lib\connection.js:113:22)
at emitOne (events.js:96:13)
at TLSSocket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at TLSSocket.Readable.push (_stream_readable.js:134:10)
at TLSWrap.onread (net.js:551:20)

Jochen Arndt 5-Apr-18 8:41am

I can only guess that ${name} should be id or ${row.name}.

If that fails too check the documentation of the used MySQL client on how to access query result data.

dolfijn3000 5-Apr-18 8:45am

the weird thing is that it did work with sqlite

Jochen Arndt 5-Apr-18 9:19am

See my updated answer

dolfijn3000 5-Apr-18 10:12am

but i dont need the first row i need the row where name is equal to id

Jochen Arndt 5-Apr-18 10:21am

A SELECT query can return any number of rows. It may be none, one (e.g. when using a WHERE clause for a unique field), or even all rows when using no WHERE clause.

Therefore, the result is always returned as an array of rows where the rowCount member tells you how many matching rows has been returned.

If the name is a unique field (that is: can't exist in multiple rows), you will get a rowCount of zero or one returned (not found / found).

But you still have to access the row by index to get the values of the fields.

To nail it down: When the id has been found (and is unique), the first row is the one you want.

dolfijn3000 5-Apr-18 10:24am

ok but it does not get me past the syntax error

Jochen Arndt 5-Apr-18 10:36am

It would be good to find out where the syntax error occurs.

Comment out all lines using the row variable.
When the error is still there it is the query syntax.

If not, it is probably accessing the row variable. Then try first if row.rowCount works. If so, it is an array.

dolfijn3000 5-Apr-18 10:35am

ok got it past the error and it worked thx

dolfijn3000 5-Apr-18 10:37am

i needed the ? to chenge in a $1

Jochen Arndt 5-Apr-18 10:43am

OK. That differs from most SQL client implementations and shows again how important a proper documentation (and reading it) is.

dolfijn3000 5-Apr-18 10:45am

i did not get it from a documentation i just clicked a lot of links when i googled it and found someone who was almost trying the same as me and there they said that ? needs to be $1

dolfijn3000 5-Apr-18 8:49am

and there is no documentation of it that i can find

Jochen Arndt 5-Apr-18 8:56am

From where did you got the client module or what is the exact name?

dolfijn3000 5-Apr-18 8:59am

i got it from the article at heroku and used the node.js instalation.
https://devcenter.heroku.com/articles/heroku-postgresql