-
Notifications
You must be signed in to change notification settings - Fork 923
Named parameters for MySQL inserts / updates #3038
-
What do you want to change?
I'm trying to generate code based on queries like this
-- name: InsertPurchaseGuid :exec INSERT INTO purchase_guids (external_id, guid, created_at, updated_at, partner_id, payment_method_id) VALUES (:external_id, :guid, :created_at, :updated_at, :partner_id, :payment_method_id);
So that I can use the NamedExec function of the mysql driver. But whenever I run sqlc generate I get this error
db/queries/subs/insert_purchase_guid.sql:3:10: syntax error near ":external_id, :guid, :created_at, :updated_at, :partner_id, :payment_method_id);"
Is this not supported at all? Or am I doing it wrong?
Thank you!
What database engines need to be changed?
MySQL
What programming language backends need to be changed?
Go
Beta Was this translation helpful? Give feedback.
All reactions
The named parameter syntax you're using is not native to MySQL. It's an extension added by sqlx. To use named parameters with sqlc, you need to use the sqlc.arg function, like so:
/* name: InsertPurchaseGuid :exec */ INSERT INTO purchase_guids ( external_id, guid, created_at, updated_at, partner_id, payment_method_id ) VALUES ( sqlc.arg('external_id'), sqlc.arg('guid'), sqlc.arg('created_at'), sqlc.arg('updated_at'), sqlc.arg('partner_id'), sqlc.arg('payment_method_id') );
Replies: 3 comments 3 replies
-
MySQL does not support NamedParameter
Beta Was this translation helpful? Give feedback.
All reactions
-
😕 1
-
MySQL does not support NamedParameter
Hello, do you mean it's not supported in sqlc? Because MySQL itself does support named parameters just fine as far as my testing goes. I used the .NamedExec of the sql driver in go, and got the expected results
Beta Was this translation helpful? Give feedback.
All reactions
-
The named parameter syntax you're using is not native to MySQL. It's an extension added by sqlx. To use named parameters with sqlc, you need to use the sqlc.arg function, like so:
/* name: InsertPurchaseGuid :exec */ INSERT INTO purchase_guids ( external_id, guid, created_at, updated_at, partner_id, payment_method_id ) VALUES ( sqlc.arg('external_id'), sqlc.arg('guid'), sqlc.arg('created_at'), sqlc.arg('updated_at'), sqlc.arg('partner_id'), sqlc.arg('payment_method_id') );
Beta Was this translation helpful? Give feedback.
All reactions
-
Hi @kyleconroy thank you for the answer. I actually use sqlc only to generate the structs for the relevant tables / queries, but I don't use the generated function to run those queries, I have my own custom ones that include instrumentation and logging. Is there a way to force sqlc to still generate the structs with the sqlx extension syntax? Or absolutely no way?
Thanks for the feedback!
Beta Was this translation helpful? Give feedback.
All reactions
-
There isn't a way to generate the structs. The SQL isn't valid MySQL syntax, so sqlc can't parse the queries.
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 1
-
Alright thanks a lot @kyleconroy ! Appreciate it
Beta Was this translation helpful? Give feedback.