Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4
On Tue, 4 Jun 2019 11:26:12 -0700
"Doug" wrote:
> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename like "Waltz";
What Shawn Wagner's answer shows you is that
'Waltz'
is a string and
"Waltz"
is a column name, because in SQL double-quotes denote identifiers.
They don't denote strings, unlike as in, say, C.
The double-quote escape syntax let's you have odd columns names with
spaces and such:
create table "The Blue Danube" (
"Waltzing Matilda" text not NULL primary key
);
If there's no column name "Waltz" in songfiletable, that's a bug IMO.
As a matter of style, what is songfiletable? A set of songs, or a
file, or a table? Why not just "songs"?
create table songs {
id integer not null primary key, -- probably not needed
dance ... ,
dance_group ... , -- or just "group", but see next
);
If songs have names and dances, and dances have groups, then
dancegroupname belongs in another table, "dances".
HTH.
--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4
Throwing a wild idea out: Can you try using single-quotes (e.g. "...where dancename = 'Waltz'..."). Single-quotes are meant to be used for text-literals; double-quotes are meant to "protect" field/table names where they clash with keywords (or contain "odd" characters). Additionally, do you by any chance have a field called "Waltz" in your table? Tuesday, June 04, 2019, 7:26:12 PM, Doug wrote: > Sqlite version is 3.27.1. > I have no indexes defined. > Here is a query I just ran from bash: > select songfile_id,dancename,dancegroupname from songfiletable where > songfile_id=377; > 377|Waltz|American Smooth > ... and another: > select songfile_id,dancename,dancegroupname from songfiletable where > dancename like "Waltz"; > 377|Waltz|American Smooth > 388|Waltz|American Smooth [snip] > ... and yet another: > select songfile_id,dancename,dancegroupname from songfiletable where > dancename = "Waltz"; sqlite>> > Nothing selected using "=". ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4
What is typeof(dancename)? I bet it's not TEXT.
sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY, dancename TEXT);
sqlite> INSERT INTO test(dancename) VALUES ('Waltz'), (cast('Waltz' AS
BLOB));
sqlite> SELECT *, typeof(dancename) FROM test WHERE dancename = 'Waltz';
id dancename typeof(dancename)
-- -- -
1 Waltz text
sqlite> SELECT *, typeof(dancename) FROM test WHERE dancename LIKE 'Waltz';
id dancename typeof(dancename)
-- -- -
1 Waltz text
2 Waltz blob
On Tue, Jun 4, 2019 at 11:26 AM Doug wrote:
> Sqlite version is 3.27.1.
>
> I have no indexes defined.
>
> Here is a query I just ran from bash:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> songfile_id=377;
> 377|Waltz|American Smooth
>
> ... and another:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename like "Waltz";
> 377|Waltz|American Smooth
> 388|Waltz|American Smooth
> 459|Waltz|American Smooth
> 647|Waltz|American Smooth
> 827|Waltz|American Smooth
> 873|Waltz|American Smooth
> 896|Waltz|American Smooth
> 1156|Waltz|American Smooth
> 1157|Waltz|American Smooth
> 1158|Waltz|American Smooth
> 1159|Waltz|American Smooth
> 1160|Waltz|American Smooth
> 1161|Waltz|American Smooth
> 1162|Waltz|American Smooth
> 1164|Waltz|American Smooth
> 1167|Waltz|American Smooth
>
> ... and yet another:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename = "Waltz";
> sqlite>
>
> Nothing selected using "=".
>
> Also a hex dump:
>
> select songfile_id,dancename,hex(dancename),dancegroupname from
> songfiletable where songfile_id=377;
> 377|Waltz|57616C747A|American Smooth
>
> Clearly what I think is in the database is there. Is there supposed to be
> a trailing null in the database for text fields that is/is not in play here?
>
> Doug
>
> > -Original Message-
> > From: sqlite-users
> > On Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
> > Sent: Tuesday, June 04, 2019 5:00 AM
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: sqlite-users Digest, Vol 138, Issue 4
> >
> > Send sqlite-users mailing list submissions to
> > sqlite-users@mailinglists.sqlite.org
> >
> > To subscribe or unsubscribe via the World Wide Web, visit
> > http://mailinglists.sqlite.org/cgi-
> > bin/mailman/listinfo/sqlite-users
> > or, via email, send a message with subject or body 'help' to
> > sqlite-users-requ...@mailinglists.sqlite.org
> >
> > You can reach the person managing the list at
> > sqlite-users-ow...@mailinglists.sqlite.org
> >
> > When replying, please edit your Subject line so it is more
> > specific
> > than "Re: Contents of sqlite-users digest..."
> >
> >
> > Today's Topics:
> >
> >1. Re: SQLite build on Risc-V (Carlos Eduardo de Paula)
> >2. select * where abc like "xxx" works,...where abc='xxx'
> > fails
> > (Doug)
> >3. Re: select * where abc like "xxx" works, ...where abc='xxx'
> > fails (Simon Slavin)
> >4. Re: select * where abc like "xxx" works,...where abc='xxx'
> > fails (Keith Medcalf)
> >5. Re: Bug in sqlite3.c (bhandari_nikhil)
> >
> >
> > --
> >
> >
> > Message: 1
> > Date: Sun, 2 Jun 2019 11:15:35 -0300
> > From: Carlos Eduardo de Paula
> > To: dcla...@blastwave.org, "sqlite-users@mailinglists.sqlite.org"
> >
> > Subject: Re: [sqlite] SQLite build on Risc-V
> > Message-ID:
> >> il.com>
> > Content-Type: text/plain; charset="UTF-8"
> >
> > I'm on a SiFive Unleashed board running Debian Linux on Kernel
> > 4.19.
> > --
> > Sent from IPhone
> >
> >
> > --
> >
> > Message: 2
> > Date: Mon, 3 Jun 2019 16:46:47 -0700
> > From: "Doug"
> > To:
> > Subject: [sqlite] select * where abc like "xxx" works,
> > ...where
> > abc='xxx' fails
> > Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> > Content-Type: text/plain; charset="us-ascii"
> >
> > I am using Sqlite under QT 5.12.0.
> >
> >
> >
> > Why does the "=" query fail and the "like" query work? There are
> > no
> > wildcards involved.
> >
> >
> >
> > I create the table this way:
> >
> >
> >
> > QString sqlcreate = QLatin1String(
> >
> > "CREATE TABLE songfiletable ("
> >
> > "songfile_id INTEGER PRIMARY KEY,"
> >
> > "dancename TEXT"
> >
> > ");" );
> >
> >
> >
> > I populated the table this way with
> > dancename=QLatin1String("Waltz"):
> >
> >
> >
> > QString sqlinsert = QLatin1String(
> >
> > "INSERT INTO songfiletable ("
> >
> > "dancename"
> >
> > ") VALUES(?);");
> >
> > queryinsert.prepare(sqlinsert);
> >
> > queryinsert.addBindValue(dancename.toUtf8());
> >
> >
> >
> > The query that works for dancename="Waltz" is this (with 'like'):
> >
> > QByteArray dn = d->dance_name.toUtf8();
> >
> > QByteArray filt
Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4
I tried a few more functions:
select songfile_id, dancename,hex(dancename), hex("Waltz"),
length(dancename),length("Waltz"),
nullif(dancename,"Waltz"),instr(dancename,"Waltz"),dancegroupname from
songfiletable where songfile_id=377;
377|Waltz|57616C747A|57616C747A|5|5|Waltz|1|American Smooth
Notice that nullif() failed to find the field and the literal equal. That is
consistent with the failure.
Doug
> -Original Message-
> From: sqlite-users
> On Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
> Sent: Tuesday, June 04, 2019 5:00 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: sqlite-users Digest, Vol 138, Issue 4
>
> Send sqlite-users mailing list submissions to
> sqlite-users@mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://mailinglists.sqlite.org/cgi-
> bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-ow...@mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more
> specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>1. Re: SQLite build on Risc-V (Carlos Eduardo de Paula)
>2. select * where abc like "xxx" works,...where abc='xxx'
> fails
> (Doug)
>3. Re: select * where abc like "xxx" works, ...where abc='xxx'
> fails (Simon Slavin)
>4. Re: select * where abc like "xxx" works,...where abc='xxx'
> fails (Keith Medcalf)
>5. Re: Bug in sqlite3.c (bhandari_nikhil)
>
>
> --
>
>
> Message: 1
> Date: Sun, 2 Jun 2019 11:15:35 -0300
> From: Carlos Eduardo de Paula
> To: dcla...@blastwave.org, "sqlite-users@mailinglists.sqlite.org"
>
> Subject: Re: [sqlite] SQLite build on Risc-V
> Message-ID:
>il.com>
> Content-Type: text/plain; charset="UTF-8"
>
> I'm on a SiFive Unleashed board running Debian Linux on Kernel
> 4.19.
> --
> Sent from IPhone
>
>
> --
>
> Message: 2
> Date: Mon, 3 Jun 2019 16:46:47 -0700
> From: "Doug"
> To:
> Subject: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> Content-Type: text/plain; charset="us-ascii"
>
> I am using Sqlite under QT 5.12.0.
>
>
>
> Why does the "=" query fail and the "like" query work? There are
> no
> wildcards involved.
>
>
>
> I create the table this way:
>
>
>
> QString sqlcreate = QLatin1String(
>
> "CREATE TABLE songfiletable ("
>
> "songfile_id INTEGER PRIMARY KEY,"
>
> "dancename TEXT"
>
> ");" );
>
>
>
> I populated the table this way with
> dancename=QLatin1String("Waltz"):
>
>
>
> QString sqlinsert = QLatin1String(
>
> "INSERT INTO songfiletable ("
>
> "dancename"
>
> ") VALUES(?);");
>
> queryinsert.prepare(sqlinsert);
>
> queryinsert.addBindValue(dancename.toUtf8());
>
>
>
> The query that works for dancename="Waltz" is this (with 'like'):
>
> QByteArray dn = d->dance_name.toUtf8();
>
> QByteArray filter = "(dancename like '" + dn + "')";
>
>
>
> The query that fails for dancename="Waltz" is this:
>
> QByteArray dn = d->dance_name.toUtf8();
>
> QByteArray filter = "(dancename='" + dn + "')";
>
>
>
> The behavior is the same in Sqlite command line. There are no
> wildcards involved.
>
>
>
> I have simplified the table and the queries to a single column for
> this message. The actual table has 22 columns.
>
>
>
> Why does the "=" query fail and the "like" query work?
>
>
>
> Regards, Doug
>
>
>
>
>
>
>
> --
>
> Message: 3
> Date: Tue, 4 Jun 2019 00:55:45 +0100
> From: Simon Slavin
> To: SQLite mailing list
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <73ebbe3c-dbc8-4ea2-a055-e4c160899...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
> On 4 Jun 2019, at 12:46am, Doug wrote:
>
> > Why does the "=" query fail and the "like" query work?
>
> To help us investigate ...
>
> Which version of SQLite is this ? You can use
>
> SELECT sqlite_version();
>
> to find out.
>
> What do you mean by 'work' and 'fail' ? Are you referring to an
> error code ?
>
> Can you add a debug line to your code so that just before applying
> the filter it outputs the same text to a log ? Just to check
> whether '.toUtf8()' does what you think it's doing.
>
> --
>
> Message: 4
> Date: Mon, 03 Jun 2019 18:33:17 -0600
> From: "Keith Medcalf"
> To: "SQLite mailing list"
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <7cf3a816ceb73047bbb53a18c9906...@mail.dessus.co
Re: [sqlite] sqlite-users Digest, Vol 138, Issue 4
Sqlite version is 3.27.1.
I have no indexes defined.
Here is a query I just ran from bash:
select songfile_id,dancename,dancegroupname from songfiletable where
songfile_id=377;
377|Waltz|American Smooth
... and another:
select songfile_id,dancename,dancegroupname from songfiletable where dancename
like "Waltz";
377|Waltz|American Smooth
388|Waltz|American Smooth
459|Waltz|American Smooth
647|Waltz|American Smooth
827|Waltz|American Smooth
873|Waltz|American Smooth
896|Waltz|American Smooth
1156|Waltz|American Smooth
1157|Waltz|American Smooth
1158|Waltz|American Smooth
1159|Waltz|American Smooth
1160|Waltz|American Smooth
1161|Waltz|American Smooth
1162|Waltz|American Smooth
1164|Waltz|American Smooth
1167|Waltz|American Smooth
... and yet another:
select songfile_id,dancename,dancegroupname from songfiletable where dancename
= "Waltz";
sqlite>
Nothing selected using "=".
Also a hex dump:
select songfile_id,dancename,hex(dancename),dancegroupname from songfiletable
where songfile_id=377;
377|Waltz|57616C747A|American Smooth
Clearly what I think is in the database is there. Is there supposed to be a
trailing null in the database for text fields that is/is not in play here?
Doug
> -Original Message-
> From: sqlite-users
> On Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
> Sent: Tuesday, June 04, 2019 5:00 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: sqlite-users Digest, Vol 138, Issue 4
>
> Send sqlite-users mailing list submissions to
> sqlite-users@mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://mailinglists.sqlite.org/cgi-
> bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-ow...@mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more
> specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>1. Re: SQLite build on Risc-V (Carlos Eduardo de Paula)
>2. select * where abc like "xxx" works,...where abc='xxx'
> fails
> (Doug)
>3. Re: select * where abc like "xxx" works, ...where abc='xxx'
> fails (Simon Slavin)
>4. Re: select * where abc like "xxx" works,...where abc='xxx'
> fails (Keith Medcalf)
>5. Re: Bug in sqlite3.c (bhandari_nikhil)
>
>
> --
>
>
> Message: 1
> Date: Sun, 2 Jun 2019 11:15:35 -0300
> From: Carlos Eduardo de Paula
> To: dcla...@blastwave.org, "sqlite-users@mailinglists.sqlite.org"
>
> Subject: Re: [sqlite] SQLite build on Risc-V
> Message-ID:
>il.com>
> Content-Type: text/plain; charset="UTF-8"
>
> I'm on a SiFive Unleashed board running Debian Linux on Kernel
> 4.19.
> --
> Sent from IPhone
>
>
> --
>
> Message: 2
> Date: Mon, 3 Jun 2019 16:46:47 -0700
> From: "Doug"
> To:
> Subject: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> Content-Type: text/plain; charset="us-ascii"
>
> I am using Sqlite under QT 5.12.0.
>
>
>
> Why does the "=" query fail and the "like" query work? There are
> no
> wildcards involved.
>
>
>
> I create the table this way:
>
>
>
> QString sqlcreate = QLatin1String(
>
> "CREATE TABLE songfiletable ("
>
> "songfile_id INTEGER PRIMARY KEY,"
>
> "dancename TEXT"
>
> ");" );
>
>
>
> I populated the table this way with
> dancename=QLatin1String("Waltz"):
>
>
>
> QString sqlinsert = QLatin1String(
>
> "INSERT INTO songfiletable ("
>
> "dancename"
>
> ") VALUES(?);");
>
> queryinsert.prepare(sqlinsert);
>
> queryinsert.addBindValue(dancename.toUtf8());
>
>
>
> The query that works for dancename="Waltz" is this (with 'like'):
>
> QByteArray dn = d->dance_name.toUtf8();
>
> QByteArray filter = "(dancename like '" + dn + "')";
>
>
>
> The query that fails for dancename="Waltz" is this:
>
> QByteArray dn = d->dance_name.toUtf8();
>
> QByteArray filter = "(dancename='" + dn + "')";
>
>
>
> The behavior is the same in Sqlite command line. There are no
> wildcards involved.
>
>
>
> I have simplified the table and the queries to a single column for
> this message. The actual table has 22 columns.
>
>
>
> Why does the "=" query fail and the "like" query work?
>
>
>
> Regards, Doug
>
>
>
>
>
>
>
> --
>
> Message: 3
> Date: Tue, 4 Jun 2019 00:55:45 +0100
> From: Simon Slavin
> To: SQLite mailing list
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <73ebbe3c-dbc8-4ea2-a055-e4c160899...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
> On 4 Jun 2019, at 12
