How to bind binary column in bulk insert in forge SQL?

Forge
,
1

I have a Table1(id, name) and i am inserting values in to id of Table1 using UUID_TO_BIN(UUID(), true).

I created another table Table2(id, title, table1_id)

here table1_id is actually refering to id of Table1.

I am preparing a bulk insert statement.

const uuid1_bin = select id from Table1;
const uuid2_bin = select id from Table1;
values = ['a', uuid1_bin, 'b', uuid2_bin];
const query = 'insert into Table2(title, table1_id) values(?,?),(?,?)';
const results = await sql
                  .prepare(query)
                  .bindParams(...values)
                  .execute();

it’s giving me error

debug: {
message: ‘stmt unknown field type 245’,
errno: 8051, sqlState: ‘HY000’,
sqlMessage: ‘stmt unknown field type 245’
},
queryType: ‘insert’

How can i bind value to binary column using ?

Thanks

Shiv

How to get inserted row id in forge sql with AUTO_RANDOM?
2

So based on our two threads, i’ve had to make some assumptions, so please clarify.

I have 2 tables, based on your posts.

  • CREATE TABLE IF NOT EXISTS table1 ( id BINARY(16) DEFAULT (UUID_TO_BIN(UUID())) NOT NULL PRIMARY KEY, name VARCHAR(256), status VARCHAR(20) )
  • CREATE TABLE IF NOT EXISTS table2 ( id BINARY(16) DEFAULT (UUID_TO_BIN(UUID())) NOT NULL PRIMARY KEY, title VARCHAR(256), table1_id BINARY(16) )

I insert two entries in Table 1: insert into table1(name, status) values ('a','pending'), ('b', 'pending');

Now the issue I can see looks like it comes from getting the id property in a binary format from our API (using SELECT id from table1 WHERE name='?';). and sending it back as binary.

What I recommend here is to coerce the primary key between UUID formats, to ensure it remains consistent, so your select statement becomes SELECT BIN_TO_UUID(id) from table1 WHERE name='?'; then on insert, it becomes insert into table2 (title, table1_id) VALUES (?, UUID_TO_BIN(?))

This is the simplest way to handle the conversion, otherwise you can look at using SELECT statements from your insert, if the name column is meant to be unique from table1

2 Likes
3

Thanks this worked for me.