Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Binding NULL parameter to parametrized query results in syntax error on execution #6678

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2019-01-15 17:35:56 +0100
From: jpastuszek
To: clients devs <>
Version: 11.31.11 (Aug2018-SP1)
CC: ferenc.sipos

Last updated: 2019-04-30 12:36:03 +0200

Comment 26794

Date: 2019-01-15 17:35:56 +0100
From: jpastuszek

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:65.0) Gecko/20100101 Firefox/65.0
Build Identifier:

When working with parametrized queries if I bind NULL to any parameter apart from the first one the generated statement is missing separating comma and SQLExecute fails with "syntax error, unexpected sqlNULL, expecting ')' or ','".

Reproducible: Always

Steps to Reproduce:

  1. Create prepared statement with more than one "?"
  2. Bind NULL value for parameter that is not the first one with SQLBindParameter (NULL ParameterValuePtr and SQL_NULL_DATA *StrLen_or_IndPtr)
  3. Call SQLExecute

Actual Results:

"syntax error" with missing separator before NULL value:

SQLPrepare: "INSERT INTO source_syslog.http_access_today (logentry_id, record_type) VALUES (?, ?)"
SQLBindParameter 0x7ff044f02d60 1 1 SQL_C_CHAR SQL_VARCHAR 0 0 0x0 0 0x108ea2208
SQLBindParameter 0x7ff044f02d60 2 1 SQL_C_CHAR SQL_VARCHAR 13 0 0x108e284a0 0 0x108ea2210
SQLExecute 0x7f84a0609940 execute 7 ('bax-176849470'NULL)
addStmtError 0x7f84a0609940 42000 syntax error, unexpected sqlNULL, expecting ')' or ',' in: "execute 7 ('bax-176849470'null" 0

Expected Results:

Success. Something like this:

SQLExecute 0x7ff044f02d60
SQLExecute 0x7ff044f02d60 execute 7 ('bax-176849470',NULL)
ODBCInitResult: querytype Q_UPDATE, rowcount 1

The problem looks to be in ODBCConvert.c where ODBCStore will handle NULL case before writing out separator:

https://github.com/MonetDB/MonetDB/blob/541b47c75f931e8749f69e6d02d056898a31eea9/clients/odbc/driver/ODBCConvert.cL2947
https://github.com/MonetDB/MonetDB/blob/master/clients/odbc/driver/ODBCConvert.cL3191

This way we can use NULL as long as it is the first parameter (no need for separator) but the driver generates wrong statements for any following NULL parameter.

Comment 26795

Date: 2019-01-16 10:04:37 +0100
From: MonetDB Mercurial Repository <>

Changeset 1b7efcbc9f7b made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1b7efcbc9f7b

Changeset description:

Always add separator before adding value.
This should fix bug #6678.

Comment 26796

Date: 2019-01-16 10:05:34 +0100
From: @sjoerdmullender

I'm pretty confident that this fixes the bug, so I'm closing it.
Feel free to reopen if the fix doesn't work.

Comment 26947

Date: 2019-04-03 20:37:50 +0200
From: @sjoerdmullender

*** Bug #6694 has been marked as a duplicate of this bug. ***

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant