[sqlite] Can this be done in SQLite?

[Hody Crouch]

You should be able to concatenate the first and last name in a subquery and
then use group_concat to output the single column you describe.

For example:

select group_concat(clientname, ';') from (select FirstName || ',' ||
LastName from yourtable);

On Thu, Oct 8, 2015 at 4:54 PM, K. P.  wrote:

> Thanks for that.I'd need something along the lines of
>
> group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName,
>
>
> which in itself does not seem to be supported.
>
>
> c.LastName || ', ' || c.FirstName, ';') as FullName,
> group_concat(FullName, ';') as ClientName,
>
>
> also not :(
>
>
> Any way around this?
>
>
>
>
>
> > From: slavins at bigfraud.org
> > Date: Thu, 8 Oct 2015 17:01:06 +0100
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] Can this be done in SQLite?
> >
> >
> > On 8 Oct 2015, at 4:59pm, K. P.  wrote:
> >
> > > Given the following tables, is it possible to extract rows of class
> data along with all participating student names concatenated in one column
> in a single SQL query?
> >
> > See the 'group_concat()' function:
> >
> > 
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Trace callback is not called in a specific case

[Hody Crouch]

Well that's embarrassing.  There wasn't a binary in the current directory,
but I think the linker picked up the 3.8.5 binary from elsewhere (confirmed
by checking the sqlite3 version in the test app, which returned 3.8.5).

I retested (linking correctly this time) against both the 3.8.6
amalgamation and the 3.8.7 pre-release amalgamation.  The problem occurs in
3.8.6 and not in 3.8.7.  So the ticket I mentioned at the top of the thread
may have resolve this issue.

Thanks for the help.

On Sat, Sep 27, 2014 at 5:12 PM, Stephan Beal <sgb...@googlemail.com> wrote:

> On Sat, Sep 27, 2014 at 11:08 PM, Hody Crouch <hody.cro...@gmail.com>
> wrote:
>
> > $ gcc -g -I. dbtest.c -l sqlite3 -ldl -lpthread
> > $ ./a.out
> > TRACE: SELECT val1, val2 from t where val2 = 'A%'
> >
>
> What is -l sqlite3 supposed to do? It's not a valid linker flag (-l and its
> argument should have no spaces between them). It looks to me like you are
> linking the sqlite3 _binary_ (from the current directory) into your dbtest
> app (which is a usage error).
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trace callback is not called in a specific case

[Hody Crouch]

Thank you for looking into this.

In my test, I linked against sqlite3 using '-l sqlite3' in the gcc command
line.  I see that you compiled sqlite3 into an object file first.  If I
build using your command, the trace works as expected.  When I link against
the source directly, I see the unexpected behavior.

$ gcc -g -I. dbtest.c sqlite3.o -ldl -lpthread
$ ./a.out
TRACE: SELECT val1, val2 from t where val2 LIKE 'A%'
$ gcc -g -I. dbtest.c -l sqlite3 -ldl -lpthread
$ ./a.out
$

If I change the SQL query, I see the trace in both cases.
$ gcc -g -I. dbtest.c sqlite3.o -ldl -lpthread
$ ./a.out
TRACE: SELECT val1, val2 from t where val2 = 'A%'
$ gcc -g -I. dbtest.c -l sqlite3 -ldl -lpthread
$ ./a.out
TRACE: SELECT val1, val2 from t where val2 = 'A%'
$


On Fri, Sep 26, 2014 at 10:06 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Fri, Sep 26, 2014 at 6:44 PM, Hody Crouch <hody.cro...@gmail.com>
> wrote:
>
> > Test code is provided below.  If you change the sql query to include
> 'val2
> > = ?' rather than 'val2 LIKE ?', you will see trace output.
> >
>
> It works fine when I try it:
>
> drh@bella:~/sqlite/bld$ gcc -g -I. x1.c sqlite3.o -ldl -lpthread
> drh@bella:~/sqlite/bld$ ./a.out
> TRACE: SELECT val1, val2 from t where val2 LIKE 'A%'
> drh@bella:~/sqlite/bld$
>
>
>
> >
> > #include 
> > #include 
> > #include "sqlite3.h"
> >
> > static void trace_callback( void* udp, const char* sql ) {
> >   printf("TRACE: %s\n", sql);
> > };
> >
> > int main(int argc, char* argv[])
> > {
> >   sqlite3 *db;
> >   char *sql;
> >
> >   sqlite3_open("test.db", );
> >
> >   // Enable tracing
> >   sqlite3_trace(db, trace_callback, 0);
> >
> >   sql = "SELECT val1, val2 from t where val2 LIKE ?";
> >
> >   sqlite3_stmt* statement = NULL;
> >   sqlite3_prepare_v2(db, sql, -1, , NULL);
> >   sqlite3_bind_text(statement, 1, "A%", -1, NULL);
> >   sqlite3_step(statement);
> >   sqlite3_close(db);
> >   return 0;
> > }
> >
> > On Fri, Sep 26, 2014 at 6:20 PM, Richard Hipp <d...@sqlite.org> wrote:
> >
> > > On Thu, Sep 25, 2014 at 5:58 PM, Hody Crouch <hody.cro...@gmail.com>
> > > wrote:
> > >
> > > > While using sqlite3 with node, I used trace and found that a specific
> > > query
> > > > did not result in a callback invocation.  I have only seen this
> > behavior
> > > if
> > > > all of the following conditions are met:
> > > > - sql query includes 'LIKE ?'
> > > > - prepare the query
> > > > - bind a parameter
> > > > - execute the query
> > > >
> > >
> > > I am unable to reproduce the problem.  Please send more hints.  Perhaps
> > > send source code.
> > >
> > >
> > > >
> > > > If I change the query to use '=' instead of 'LIKE', the trace
> callback
> > is
> > > > invoked as expected.
> > > >
> > > > I looked at http://www.sqlite.org/src/info/11d5aa455e0d98f3c1e6a08
> in
> > > > hopes
> > > > that this issue might be resolved.  Using
> > > sqlite-amalgamation-201409200035
> > > > and a test app in c, the issue is still reproducible.
> > > >
> > > > Test table schema: CREATE TABLE t (val1 TEXT, val2 TEXT);
> > > >
> > > > Query to reproduce the issue:
> > > > SELECT val1, val2 from t where val2 LIKE ?
> > > >
> > > > The description of the ticket I mentioned seems similar, but I don't
> > know
> > > > enough about the sqlite3 inner workings to offer much more than the
> > above
> > > > report.  Let me know if you need to see the test app as well.
> > > >
> > > > Thanks.
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trace callback is not called in a specific case

[Hody Crouch]

Test code is provided below.  If you change the sql query to include 'val2
= ?' rather than 'val2 LIKE ?', you will see trace output.

#include 
#include 
#include "sqlite3.h"

static void trace_callback( void* udp, const char* sql ) {
  printf("TRACE: %s\n", sql);
};

int main(int argc, char* argv[])
{
  sqlite3 *db;
  char *sql;

  sqlite3_open("test.db", );

  // Enable tracing
  sqlite3_trace(db, trace_callback, 0);

  sql = "SELECT val1, val2 from t where val2 LIKE ?";

  sqlite3_stmt* statement = NULL;
  sqlite3_prepare_v2(db, sql, -1, , NULL);
  sqlite3_bind_text(statement, 1, "A%", -1, NULL);
  sqlite3_step(statement);
  sqlite3_close(db);
  return 0;
}

On Fri, Sep 26, 2014 at 6:20 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Thu, Sep 25, 2014 at 5:58 PM, Hody Crouch <hody.cro...@gmail.com>
> wrote:
>
> > While using sqlite3 with node, I used trace and found that a specific
> query
> > did not result in a callback invocation.  I have only seen this behavior
> if
> > all of the following conditions are met:
> > - sql query includes 'LIKE ?'
> > - prepare the query
> > - bind a parameter
> > - execute the query
> >
>
> I am unable to reproduce the problem.  Please send more hints.  Perhaps
> send source code.
>
>
> >
> > If I change the query to use '=' instead of 'LIKE', the trace callback is
> > invoked as expected.
> >
> > I looked at http://www.sqlite.org/src/info/11d5aa455e0d98f3c1e6a08 in
> > hopes
> > that this issue might be resolved.  Using
> sqlite-amalgamation-201409200035
> > and a test app in c, the issue is still reproducible.
> >
> > Test table schema: CREATE TABLE t (val1 TEXT, val2 TEXT);
> >
> > Query to reproduce the issue:
> > SELECT val1, val2 from t where val2 LIKE ?
> >
> > The description of the ticket I mentioned seems similar, but I don't know
> > enough about the sqlite3 inner workings to offer much more than the above
> > report.  Let me know if you need to see the test app as well.
> >
> > Thanks.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trace callback is not called in a specific case

[Hody Crouch]

While using sqlite3 with node, I used trace and found that a specific query
did not result in a callback invocation.  I have only seen this behavior if
all of the following conditions are met:
- sql query includes 'LIKE ?'
- prepare the query
- bind a parameter
- execute the query

If I change the query to use '=' instead of 'LIKE', the trace callback is
invoked as expected.

I looked at http://www.sqlite.org/src/info/11d5aa455e0d98f3c1e6a08 in hopes
that this issue might be resolved.  Using sqlite-amalgamation-201409200035
and a test app in c, the issue is still reproducible.

Test table schema: CREATE TABLE t (val1 TEXT, val2 TEXT);

Query to reproduce the issue:
SELECT val1, val2 from t where val2 LIKE ?

The description of the ticket I mentioned seems similar, but I don't know
enough about the sqlite3 inner workings to offer much more than the above
report.  Let me know if you need to see the test app as well.

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users