MySQL Forums - Connector/Node.js

'Column name is required in ColumnIdentifier.' (2 replies)

Brian Boyer — Fri, 10 May 2024 09:41:58 +0000

Team,

Please help.
I am having trouble with an update() function on a MYSQL 8.4 DB on OCI.

Getting the following error:
info: {
severity: 0,
code: 5152,
sqlState: 'HY000',
msg: 'Column name is required in ColumnIdentifier.'
}
Top Lines of Error: at BaseHandler.(/nmsv7.2/api/node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:121:17)
at Array.entry (/nmsv7.2/api/node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:92:29)
at WorkQueue.process (/nmsv7.2/api/node_modules/@mysql/xdevapi/lib/WorkQueue.js:77:19)

I dumbed down the update to the following:
var acts_info = session.getSchema('schema).getTable('acts_info')

acts_info.update().where('customer_number like :customer_number')
.bind('customer_number', data.customer_number)
.set('contact_via_mail', data.contact_via_mail)
.execute()

Let me know if you have a solution.

Thanks,

Brian

Introducing JSON/Relational Duality for MySQL REST Service (no replies)

Edwin Desouza — Thu, 19 Oct 2023 19:11:15 +0000

Introducing JSON/Relational Duality for MySQL REST Service
- https://blogs.oracle.com/mysql/post/introducing-jsonrelational-duality-for-mysql-rest-service

Node X DevAPI can't getSession() from table (1 reply)

Michael Dayah — Fri, 17 Nov 2023 19:50:41 +0000

According to https://dev.mysql.com/doc/dev/connector-j/latest/?com/mysql/cj/xdevapi/Session.html

Interface Table inherits from DatabaseObject the getSession() method.

Calling this method on table from Node.js v20.7.0 with @mysql/xdevapi 8.0.33 in the following way

const session = await mysqlx.getSession(config);
const schema = session.getSchema(config.schema);
const table = schema.getTable('property');
const session_from_table_test = await table.getSession();

returns the following error:
/node_modules/@mysql/xdevapi/lib/DevAPI/DatabaseObject.js:56
return session(connection);
^

TypeError: session is not a function

Undefined text (no replies)

aBout aBout — Fri, 15 Sep 2023 10:47:14 +0000

Hello, i have text in SQL can i get it in JavaScript?

Like that :

Ø§Ù„Ø£Ù…Ù† Ø§Ù„Ø¹Ø§Ù… - Ø¬Ù†Ø¯ÙŠ Ù…ØªØ¯Ø±Ø¨

Reconnect logic on connection close in X DevAPI of MySql using @mysql/xdevapi module in Node.js (1 reply)

Alok Singh — Mon, 04 Sep 2023 09:55:29 +0000

I am using X DevAPI of MySql v8.0.33 using @mysql/xdevapi v8.0.33.

I have reconnect logic added but that is not working. Here is that code.

const mysqlx = require('@mysql/xdevapi')

async function createSession() {
try {
const session = await mysqlx.getSession({
user: 'your_user',
password: 'your_password',
host: 'your_host',
port: 33060, // default MySQL X Protocol port
});

// Handle connection close by reconnecting the session
session['_client']['_stream'].on('close', () => {
console.log('Connection closed. Reconnecting...');
createSession(); // Re-establish the session
});

// Use the session for your database operations
// ...

} catch (error) {
console.error('Error connecting to MySQL:', error);
}
}

// Create the initial session
createSession();
I am getting error

TypeError: Cannot read properties of undefined (reading '_stream')
This error means that session['_client'] is undefined.

What is wrong with my reconnect logic?

Understanding SSL connection with MySQL protocol (1 reply)

austin cheney — Wed, 15 Feb 2023 17:04:21 +0000

I am writing a MySQL connection utility from scratch in Node.js in order to better understand the details of the protocol and extend my knowledge of Node TLS. The problem I am having is how to execute the upgrade from a Net socket to a TLS socket.

Following the MySQL documentation I can create a socket and connect to MySQL, get back the greeting packet and parse it. I believe I am formulating the SSL Request packet properly:

I cannot seem to get the SSL connection correct though.

I am performing the following:

```javascript
let secureSocket = null;
const context = {
ca: "ca cert text",
cert: "cert text",
minVersion: "TLSv1.3"
};

socket.write(sslRequest);

secureSocket = new TLSSocket(socket, {
rejectUnauthorized : true,
requestCert : true,
secureContext : context,
isServer : false
});

connectSecure({
host: env[environment].host,
port: env[environment].port,
secureContext: sslContext,
socket: secureSocket
});
```

I am getting this error:

node:events:491
throw er; // Unhandled 'error' event
^

Error: Client network socket disconnected before secure TLS connection was established
at connResetException (node:internal/errors:717:14)
at TLSSocket.onConnectEnd (node:_tls_wrap:1600:19)
at TLSSocket.emit (node:events:525:35)
at endReadableNT (node:internal/streams/readable:1359:12)
at process.processTicksAndRejections (node:internal/process/task_queues:82:21)
Emitted 'error' event on TLSSocket instance at:
at emitErrorNT (node:internal/streams/destroy:151:8)
at emitErrorCloseNT (node:internal/streams/destroy:116:3)
at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
code: 'ECONNRESET',
path: undefined,
host: 'host location address',
port: 3306,
localAddress: undefined
}

Node.js v19.4.0

Any guidance or suggestions?

SqlResult does not have getLastInsertId() (3 replies)

Seongjoo Lee — Thu, 26 Jan 2023 00:21:38 +0000

API Version: @mysql/xdevapi 8.0.32

User guide shows that SqlResult has getLastInserId()

https://dev.mysql.com/doc/x-devapi-userguide/en/result-set-classes.html

But it does not.

For now, it seems that executing sql query as string is required to get the last insert id.

Why is Node.js Single-threaded? (5 replies)

Hie HQ — Wed, 22 May 2024 09:38:06 +0000

Node.js is single-threaded for async processing

Error: ER_EMPTY_QUERY: Query was empty (1 reply)

Thijmen De Groote — Mon, 02 Jan 2023 18:42:08 +0000

I'm working on a log in form with node.js and mysql as the database. When I run the programm and try to register a new user I get this error when I press submit.

When I press submit I get the folowing error in the console in the inspect page in chrome: error and I get in the terminal inside vs code the following error:

node:internal/process/promises:288
triggerUncaughtException(err, true /* fromPromise */);
^

Error: ER_EMPTY_QUERY: Query was empty
at Sequence._packetToError (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Query.ErrorPacket (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
at Protocol._parsePacket (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\protocol\Parser.js:433:10)
at Parser.write (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\protocol\Parser.js:43:10)
at Protocol.write (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket. (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\Connection.js:88:28)
at Socket. (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\Connection.js:526:10)
at Socket.emit (node:events:513:28)
at addChunk (node:internal/streams/readable:324:12)
--------------------
at Protocol._enqueue (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Connection.query (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\mysql\lib\Connection.js:198:25)
at register (D:\programmeer stuff\javascript\informatica site\New folder\controllers\register.js:9:12)
at Layer.handle [as handle_request] (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\express\lib\router\layer.js:95:5)
at next (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\express\lib\router\route.js:144:13)
at Route.dispatch (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\express\lib\router\route.js:114:3)
at Layer.handle [as handle_request] (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\express\lib\router\layer.js:95:5)
at D:\programmeer stuff\javascript\informatica site\New folder\node_modules\express\lib\router\index.js:284:15
at Function.process_params (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\express\lib\router\index.js:346:12)
at next (D:\programmeer stuff\javascript\informatica site\New folder\node_modules\express\lib\router\index.js:280:10) {
code: 'ER_EMPTY_QUERY',
errno: 1065,
sqlMessage: 'Query was empty',
sqlState: '42000',
index: 0,
sql: undefined
}
The code I try to run is:

controllers/register.js

const db = require("../routes/db-config");
const bcrypt =require("bcryptjs")

const register = async (req, res) => {
const {email, password: Npassword} = req.body
if(!email || !Npassword) return res.json({satus:'error', error:"Please enter your email and password"});
else{
console.log(email);
db.query('SELECT email FROM users WHERE email = ?' , async(err, result) => { if(err) throw err; if(result[0])return res.json({satus:'error', error:"email already in use"}) else { const password = bcrypt.hash(Npassword, 8); console.log(password) db.query('INSERT INTO users SET ?', {email: email, password: password}, (error, results)=>{ if (error) throw error; return res.json({ satus:"success", success:"account registred succesfully" }) }) } }) } } module.exports = register; I think either db.query('INSERT INTO users SET ?', {email: email, password: password}, (error, results)=> or `db.query('SELECT email FROM users WHERE email = ?'

Authentication method ‘caching_sha2_password’ not supported by any of the available plugins (1 reply)

Edwin Eu — Mon, 12 Dec 2022 15:52:55 +0000

Hello
I have XYZ application run into errors of “Authentication method ‘caching_sha2_password’ not supported by any of the available plugins” when trying to connect MySQL 8 databases.
• After I migrated MySQL 5 databases into MySQL 8 in Windows Server 2019.
• I make sure the logins to use “mysql_native_password” plugin.
• Then, I changed the INI parameter to use:
o default_authentication_plugin=mysql_native_password
Then, I checked and verified the logins plugin and it showed “mysql_native_password”
SELECT user, authentication_string, plugin, host from mysql_user;
• User name = Login_1
• Authentication_string = xxxxxx
• Plugin = mysql_native_password
• Host = %
Would someone please help and advise.
Thank you in advance for your help.
-Edwin

xdev api cloud (Azure, AWS, Google Cloud) support? (3 replies)

Peter Huszar — Mon, 02 Jan 2023 18:54:09 +0000

The XDev API was published years ago. Still, in 2022 there is no support from any big cloud providers to use MySQL with XDEV API on their infrastructure. What is the reason? Does MySQL/Oracle have any plan/roadmap to 'enforce' these platforms to support the XDev API? Or will MySQL abandon the XDev API?

(I've put this question here as I have a nodejs app with MySQL DB (and doc tables) that I want to move to the cloud).

Is there any npm library available for mysql which is synchronous? (2 replies)

Prithwiraj Bose — Mon, 19 Sep 2022 20:06:04 +0000

I am using MySQL for years but recently started working on NodeJS. I have seem several MySQL libraries available on npm repository. However almost all of the working ones are using async connection and query functions. Is there any library available which doesn't work asynchronously and can return query results in a sync flow? Something like this:

const mysqllib = require('mysqllib);
$conn = mysqllib.connect(...);
$res = mysqllib.query($conn, ...);

Thanks in advance for your responses.

Regards,
Prithwiraj Bose
https://sribasu.com

Node JS Http response streaming handling (1 reply)

veera ravala — Mon, 02 Jan 2023 18:47:35 +0000

I am working on the sensortag devices, I need to get the sensor values continuously from the device. Here I am using the Angular JS & node JS. from the Angular JS I am requesting the node JS to response back the sensor values. Because the sensor is running with the help of Node JS. My requesting code is

$http.get('http://node js url/sensortag device id')
.success(function(response) {
console.log('message from node',response);
})
.error(function() {
});

and from the node js, I am using the response.write() but is not useful to me. It will send the data at the time stopping/closing the connection. I need the continuous responses back.

Please help me out. I got stuck here.

Is http steaming helpful ? and how to use that one.

Other wise let me know if any other way to request the node to keep sending the data

Cannot use caching_sha2_password using XDevAPI, unless using mysqlsh first (7 replies)

Geert Vanderkelen — Fri, 18 Mar 2022 09:13:09 +0000

Hi,

I am writing some code that uses the XDevAPI (using Go, but that doesn't matter
really). But the authentication, though working, is giving me headaches, the
caching part of it.

I understand the caching of the password in (server) memory, and that it has
to be first done over TLS. Next time, until server restart (or FLUSH PRIVILEGES).

What I am doing:
1. Negotiate and set capability tls=1
2. Loop over methods MYSQL41 and SHA256_MEMORY
1) write AuthenticateStart
2) read AuthenticateContinue
3) Scramble password using challange got from (2)
4) write AuthenticateContinue
5) read message, should be SessionStateChanged
6) read AuthenticateOk

So it works with SHA256_MEMORY, but only when I first used `mysqlsh`
using the same user. If I do `FLUSH PRIVILEGES`, auth fails again with my code.

Giving me a "ERROR 1045 (HY000): Access denied for user",

TLS handshake worked, that's in place. Status var Mysqlx_ssl_accepts
increases. In mysqlsh, status shows the ciphers...

Looking at other Connectors (J/Python/.Net/C), I cannot figure it out
what the trick is so that the connectors first authentication is
cached. I follow the UMLs, the documentation, .. no avail.

What is the trick? What setting? What message has to be sent so that
my code tells server to "cache password!"? What am I missing??
What is the magical sauce mysqlsh uses to make it happen?

Cheers,
Geert

xDev API Java example for using an Array in where clause (1 reply)

Subramanyam Vummethala — Tue, 15 Mar 2022 15:12:35 +0000

xDev API Java example for using an Array in where clause.
We are using Java Spring boot connecting to MySQL xDev API.
In NOSQL document, one of the attribute is a JSON Array.
From Java code, we are trying to fetch the data matching to one of the element in the array. For this we are using "in" operator.
Can someone help us with the syntax of this query in Java language.

.find("(Role like: ABC "
+ "Role2 IN : role2)"
+ "AND active like : isActive")
.bind(ABC, ABC)
.bind("role2", ["PQR"])
.bind("isActive", TRUE)
.execute();
Here I am trying with a binding parameter role2 for matching my input value PQR with any of the elements in that array (eg ;"PQR","XYZ", "MNO", "EFG"].
My expectation is to fetch data matching with below criteria:
(Role = ABC or Role2 = PQR ) AND isActive = TRUE
Please note the using OR above.

Overwriting _id column in nosql document store a good idea? (1 reply)

David Godfrey — Tue, 21 Dec 2021 12:02:01 +0000

We are using the xdevapi and node.js to build a new application. We will be using the latest MySQL version. I am a DBA, not a node.js developer.

I wonder if any applications use the _id value for anything outside of a look up? Wouldn't application-specific id values be more appropriate for day to day operations of a general application?

A developer is asking for unintended consequences of overriding the default _id value generated when creating a new document. Aside from performance issues and possible PK constraint violations, I wonder if I shouldn't just tell him to create his own internal application IDs and stop using the generated _id values the database is creating?

I pointed him to this statement from (https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html ) where it states

"It is possible to override the automatic generation of document IDs by manually including an ID in an inserted document.

Important
X Plugin is not aware of the data inserted into the collection, including any manual document IDs you use. When using manual document IDs, you must ensure that they do not clash with any IDs that might ever be generated automatically by the server (see Document ID Generation for details), in order to avoid any errors due to primary key duplication.

Whenever an _id field value is not present in an inserted document, the server generates an _id value. The generated _id value used for a document is returned to the client as part of the Result (Result for Connector/J) object of the add() operation. If you are using X DevAPI on an InnoDB Cluster, the automatically generated _id must be unique within the whole cluster. By setting the mysqlx_document_id_unique_prefix to a unique value per cluster instance, you can ensure document IDs are unique across all the instances.

The _id field must be sequential (always incrementing) for optimal InnoDB insertion performance (at least within a single server). The sequential nature of _id values is maintained across server restarts."

Any opinions on this matter would be appreciated!

mysql xdevapi (1 reply)

Houcem BENMANSER — Fri, 26 Nov 2021 09:10:48 +0000

I am trying to work with Mysql as a document store using xdevapi, but it is written in JavaScript and I am using TypeScript,so I found mysqlx a type definition for xdevapi and I don't know if it is a good one. Is there any better alternative

No Node.js Access To MySQL Community Database . . . (3 replies)

Kon Tiki — Fri, 26 Nov 2021 18:28:03 +0000

My machine runs:
Ubuntu Linux 20.04.2,
Node 14.15.1,
MySQL Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

I am having major problems accessing MySQL database from a simple Node.js web app. I am trying to use a development version of the web app on the local machine to connect to a local instance of the web app's database.
I have successfully used MySQL Workbench to make a connection to the local MySQL database and this works fine - I use it to edit the database.

The first action by web app users is basic authentication, e.g. registration/login using a small form on the home page of the website.

All modules in the web app execute as intended until the query to check a user name in the app's database is sent to a module which queries the database, mysqldb.js.

This module requires an npm module called mysql which makes the actual connection to the MySQL database, runs the query through to it and returns three parameters: error (JS error object), result (table of DB query results) and fields (the column heads of the result table).

I attach the source code of the module below:

const mysql = require('mysql');

/** Queries the app's MySQL database
* @param {Object} query - the query presented to the MySQL DB
* @param {Object} dbcallback - the callback function applied to the query response
* */
const queryNodeAppData = (query, dbcallback) =>
{
console.log("In MySQL Query script now ...")
console.log("Query: " + query);

const conn = mysql.createConnection(
{
socketPath: "/var/run/mysqld/mysqld.sock",
database: "nodeapp",
user: "root",
password: "MY.$qu3@13r"
});

console.log("Connection user: " + conn.user);

conn.connect(query, (errconn, result, fields) =>
{
if (errconn)
{
console.error('Error connecting: ' + errconn.stack);
dbCallback(errconn, null, null);
}
else
{
console.log('Connected as id ' + connection.threadId);
dbCallback(null, result, fields);
}
});

};

module.exports = { queryNodeAppData };

The console output for this app is below.
The app does NOT execute any of the console logs within the MySQL query statement so we don't even know if any connection has been made - I suspect not.

$ nodemon app
[nodemon] 2.0.14
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json
[nodemon] starting `node app.js`
HTTP Server for NodeApp started at port 3000
HTTPS Server for NodeApp started at port 3001
We're in to the back end of NodeApp !
Someone has POSTed data to the Node server ...
URL trimmed endpoint: reg-user
Input Value: tamjk
In router.js now ...
Endpoint: reg-user Data: tamjk
Handler: authen.js
In handler authen.js now ...
Mode: reg Field: user Value: tamjk
In format validation now ...
Format Validation Message: Valid format
Connecting to user-data database checking user ...
In MySQL Query script now ...
Query: SELECT user FROM users WHERE user = 'tamjk'
Connection user: undefined
Router returned message undefined
Callback Message: undefined
Message: undefined
res: [object Object]
Sent back response: undefined
_http_outgoing.js:696
throw new ERR_INVALID_ARG_TYPE('first argument',
^

TypeError [ERR_INVALID_ARG_TYPE]: The first argument must be of type string or an instance of Buffer or Uint8Array. Received undefined
at write_ (_http_outgoing.js:696:11)
at ServerResponse.write (_http_outgoing.js:661:15)
at reqCallback (/home/sandbar/Desktop/nodeapp-local/app.js:113:6)
at IncomingMessage. (/home/sandbar/Desktop/nodeapp-local/app.js:81:10)
at IncomingMessage.emit (events.js:327:22)
at endReadableNT (_stream_readable.js:1327:12)
at processTicksAndRejections (internal/process/task_queues.js:80:21) {
code: 'ERR_INVALID_ARG_TYPE'
}
[nodemon] app crashed - waiting for file changes before starting...

How to connect my database to Qlikview Software (2 replies)

Upali Wijeratne — Tue, 12 Oct 2021 23:49:28 +0000

Hi

I am new here. I have created connection through mysql workbench. But I am unable link my database to Qlikview Software installed in my local machine. Kindly help with step by step procedure of how to it

MySQL Connector/NodeJS 8.0.26 is released (no replies)

Edwin Desouza — Wed, 21 Jul 2021 19:03:26 +0000

MySQL Connector/NodeJS 8.0.26 is released
https://dev.mysql.com/doc/relnotes/connector-nodejs/en/news-8-0.html

MySQL Connector/Node.js 8.0.25 has been released (no replies)

Edwin Desouza — Tue, 11 May 2021 16:24:38 +0000

https://insidemysql.com/mysql-connector-node-js-8-0-25-has-been-released/

Buffer() is deprecated (2 replies)

ALEKSANDAR MAKEDONSKI — Thu, 22 Apr 2021 16:20:55 +0000

I've got a warning for deprecation, please reconsider to change Buffer().

DeprecationWarning: Buffer() is deprecated due to security and usability issues. Please use the Buffer.alloc(), Buffer.allocUnsafe(), or Buffer.from() methods instead.
at showFlaggedDeprecation (node:buffer:184:11)
at new Buffer (node:buffer:271:3)
at Object.toBuffer (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/Protocol/Wrappers/ScalarValues/bytes.js:57:1)
at Object.serialize (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/Protocol/Wrappers/Traits/Serializable.js:50:1)
at Object.exports.encodeCapabilitiesSet (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/Protocol/OutboundHandlers/Connection.js:70:1)
at Client.capabilitiesSet (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/Protocol/Client.js:292:1)
at Client.enableTLS (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/Protocol/Client.js:101:1)
at Session._createConnection (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/DevAPI/Session.js:264:1)
at Session.connect_ (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/DevAPI/Session.js:310:1)
at Socket. (~/dist/webpack:/application-backend/node_modules/@mysql/xdevapi/lib/DevAPI/Session.js:351:1)

MySQL Connector/Node.js 8.0.24 has been released (no replies)

Edwin Desouza — Tue, 20 Apr 2021 14:42:27 +0000

https://insidemysql.com/mysql-connector-node-js-8-0-24-has-been-released/

insert performance issue (2 replies)

Eric Davies — Wed, 31 Mar 2021 14:43:04 +0000

I've got an node.js application that needs to log stuff to the mysql database.
I'm using: @mysql/xdevapi@8.0.23, community edition of mysql on a i5 ubuntu box (i5-8400cpu [@2.8Ghz x 6], 32GB of ram)

Currently my insert code looks like this:
// start of stuff for each insert //
let command = dbconn.sql('INSERT INTO cellvalues(field1,field2,field3,field4,field5,field6,field7,field8) VALUES(?,?,?,?,?,?,?,?);');
let args = [value1, value2, value3, value4, value5, value6, value7, value8];
let query = command.bind(args);
if( query && query.execute ) {
this.totalInsertsStarted++;
let self = this;
query.execute().then( () => {
self.totalInsertsFinished++;
}).catch( err=>{console.error("error in insert: ",err)});
// end of stuff done for each insert

What I'm seeing while I run this is that I'm starting about 1584 inserts a second, and I'm only seeing somewhere between 123 and 145 finish a second, so a big backlog builds up very quickly. I'd actually like to do more like 20000 or 30000 inserts a second.

If I was coding this in another language, I'd be using prepared statements, however, the docs for the connector say that prepared statements aren't supported explicitly, and the list of statements done implicitly doesn't include inserts. Factoring the dbconn.sql(sqltext) out of the repeated block of code result in failure.

Is there a better way to code the insert or should I look to writing this part of the code in language where the connector supports prepared statements?

My table definition looks like:
CREATE TABLE `cellvalues` (
`field1` bigint NOT NULL,
`field2` tinyint NOT NULL,
`field3` tinyint NOT NULL,
`field4` tinyint NOT NULL,
`field5` tinyint NOT NULL,
`field6` smallint NOT NULL,
`field7` int NOT NULL,
`field8` tinyint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Thank you.

Unexpected rounding value for updating Decimal field (1 reply)

Kevin Lau — Fri, 26 Mar 2021 11:24:47 +0000

create table (
'ID' INT AUTO_INCREMENT,
`Total_Profit` DECIMAL(16,2) NULL);

Expected behavior
Mysql statement
update table set Total_Profit = '-56565656.56' where ID = '1';
Result will become -56565656.56 on the table.

Unexpected behavior in Nodejs connector

table.update()
.where(
`...`
)
.set(Total_Profit, -56565656.56)

The result will become -56565700.00 on the Mysql database

Try updating smaller negative values -56556.56, resulting in -56556.60
Try updating smaller positive values 56556.56, resulting in 56556.60

Why the unexpected number rounding? Thanks.

Which One Is the Best Node.js Framework? (2 replies)

Declan Lawton — Tue, 23 Mar 2021 08:54:48 +0000

Hi,

I am planning to use Express.js for my upcoming web app project. Is there anyone who can suggest to me which is the best node.js framework?

Note: I saw one blog and then after I decided to use Express.js.

Looking forward to suggestions.

Can we get typescript support for the Node.js connector? (2 replies)

Markus Uusitalo — Thu, 29 Jul 2021 16:36:38 +0000

I would very much like to use the Node.js connector with typescript!

Unexpected NULL Fields errors when updating or inserting record - MySQL XDevAPI NodeJs Connector 8.0.23 (1 reply)

Kevin Lau — Mon, 08 Mar 2021 10:58:42 +0000

In the older versions, statement like below was good if passing a null value to it.

`First_Name` VARCHAR(60) NULL,
After upgrading to 8.0.23. If not passing any value or null to update or insert a nullable field, it will have errors such as this.

The error statement: "Error: Column name is required in ColumnIdentifier."
Occurred when passing a null value during update operations.

The error statement: "Error: Wrong number of fields in row being inserted"
Occurred when passing a null value during insert operations. The number of field is correct. The error will go away when given non-null value to a column.

It isn't inconsistent to the specification of Mysql Schema. Is this the expected behavior? What's the workaround without major version refactoring? Thanks.

Value expression strings on relational table update (2 replies)

Lianto Lim — Thu, 04 Feb 2021 14:41:14 +0000

I read in the documentation that "Value expression strings are used to compute a value which can then be assigned to a given field or column", but I can't get it working with relational table update function using nodejs @mysql/xdevapi@8.0.23.

Portion of code in node.js:
---
return customerTable.update()
.set("age", mysqlx.expr("age+1"))
.where("name = :name")
.bind('name', 'Andy')
.execute()
---
return an error: Unknown column 'doc' in 'field list'
and the log file shows the following query:
UPDATE `customers` SET `age`=(JSON_EXTRACT(doc,'$.age') + 1) WHERE (`name` = 'Andy')

Meanwhile, I test it on MySQL Shell 8.0, with the following portion of code:
---
customerTable.update().
set("age", mysqlx.expr("age + 1")).
where('name = :name').
bind('name', 'Andy').
execute();
---
it runs fine and show the following in the log file:
UPDATE `test`.`customers` SET `age`=(`age` + 1) WHERE (`name` = 'Andy')

Can someone help to point out the mistake?
Thanks

MySQL Connector/Node.js 8.0.23 has been released (no replies)

Edwin Desouza — Mon, 18 Jan 2021 18:23:51 +0000

https://insidemysql.com/mysql-connector-node-js-8-0-23-has-been-released/