Re: [sqlite] Can't get concat operator to work
On Mar 11, 2008, at 7:36 AM, [EMAIL PROTECTED] wrote: > > > I'm trying to get the concat operator to work with my user-defined > function. This works fine: > > SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees > > But this doesn't work: > > SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) > FROM Sessions > > I get only the formatted date - missing the formatted time. > FORMAT_DATE is my own user-defined function that returns text data > type. When you call sqlite3_result_text() to return the result, does your result string include a nul-terminator character? If so, that byte should not be included in the "number of bytes" parameter passed to result_text(). i.e. if you were doing: sqlite3_result_text(pContext, "abc", 4, ...) you might get the result you are describing. Dan. > > Can someone *please* check into this. I must get this working. > > Thank you > -brett > > > > > This message was sent using IMP, the Internet Messaging Program. > ___ > 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] Can't get concat operator to work
Yes, this works fine. I get three columns: login date, empty column, login time Quoting Stephen Oberholtzer : > On Mon, Mar 10, 2008 at 7:36 PM, wrote: >> >> >> I'm trying to get the concat operator to work with my user-defined >> function. This works fine: >> >> SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees >> >> But this doesn't work: >> >> SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) >> FROM Sessions >> >> I get only the formatted date - missing the formatted time. >> FORMAT_DATE is my own user-defined function that returns text data >> type. >> >> Can someone *please* check into this. I must get this working. >> >> Thank you >> -brett > > What about this? > > SELECT FORMAT_DATE(login_time), ' ', FORMAT_TIME(login_time) FROM Sessions > > > That will make sure that FORMAT_DATE(login_time) is working properly > > -- > -- Stevie-O > Real programmers use COPY CON PROGRAM.EXE > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > This message was sent using IMP, the Internet Messaging Program. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
Sorry, I forgot to answer your question about using other Sqlite built-in functions for date and time. I need my own custom version because my database dates can be stored as either a double value (OLE Date scheme) or as text representation. My custom functions can also accept a format template allowing great flexibility as to the formatted output. But aside from that, if this is a Sqlite bug, we need to get it fixed anyway. cheers -brett Quoting BareFeet : > Hi Brett, > >> I'm trying to get the concat operator to work with my user-defined >> function. This works fine: >> >> SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees >> >> But this doesn't work: >> >> SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) >> FROM Sessions >> >> I get only the formatted date - missing the formatted time. >> FORMAT_DATE is my own user-defined function that returns text data >> type. > > I know I'm stating the obvious, but have you tried?: > > SELECT FORMAT_TIME(login_time) FROM Sessions > > since that's the part that does seem to be working. > > If it's giving you a blank then it's your function, not the concat > operator. > > What do your custom functions do? Have you looked at the date and time > functions built into SQLite? I expect that they'll cater for what > you're after directly. > > Tom > BareFeet > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > This message was sent using IMP, the Internet Messaging Program. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
Here is what the code does. I set up the UDF after I open a file, like this: � sqlite3_create_function( pDb, "format_time", -1, SQLITE_UTF16, 0, format_time, 0, 0 ); The format_time function formats the time according to a template and calls sqlite3_result_text16, like this: sqlite3_result_text16( context, pMem, nLen, UdfCleanupCallback ); The memory is allocated to pMem and the text data copied into it. Then the function returns. That's all. One thing I noticed is that the nLen must be the number of bytes allocated in pMem - not the number of wide chars in pMem. This is unexpected, since we're calling the wide char (*16) version of sqlite3_result_text. Thanks for the help. Quoting John Stanton : The problem appears to be in your function. Post the code for it. [EMAIL PROTECTED] wrote: I'm trying to get the concat operator to work with my user-defined function. This works fine: SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees But this doesn't work: SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) FROM Sessions I get only the formatted date - missing the formatted time. FORMAT_DATE is my own user-defined function that returns text data type. Can someone *please* check into this. I must get this working. Thank you -brett This message was sent using IMP, the Internet Messaging Program. ___ 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 This message was sent using IMP, the Internet Messaging Program. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
Thanks Tom. Yes, I have tried that. The function definitely returns a valid string. Quoting BareFeet : > Hi Brett, > >> I'm trying to get the concat operator to work with my user-defined >> function. This works fine: >> >> SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees >> >> But this doesn't work: >> >> SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) >> FROM Sessions >> >> I get only the formatted date - missing the formatted time. >> FORMAT_DATE is my own user-defined function that returns text data >> type. > > I know I'm stating the obvious, but have you tried?: > > SELECT FORMAT_TIME(login_time) FROM Sessions > > since that's the part that does seem to be working. > > If it's giving you a blank then it's your function, not the concat > operator. > > What do your custom functions do? Have you looked at the date and time > functions built into SQLite? I expect that they'll cater for what > you're after directly. > > Tom > BareFeet > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > This message was sent using IMP, the Internet Messaging Program. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
On Mon, Mar 10, 2008 at 7:36 PM, <[EMAIL PROTECTED]> wrote: > > > I'm trying to get the concat operator to work with my user-defined > function. This works fine: > > SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees > > But this doesn't work: > > SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) > FROM Sessions > > I get only the formatted date - missing the formatted time. > FORMAT_DATE is my own user-defined function that returns text data > type. > > Can someone *please* check into this. I must get this working. > > Thank you > -brett What about this? SELECT FORMAT_DATE(login_time), ' ', FORMAT_TIME(login_time) FROM Sessions That will make sure that FORMAT_DATE(login_time) is working properly -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
Hi Brett, > I'm trying to get the concat operator to work with my user-defined > function. This works fine: > > SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees > > But this doesn't work: > > SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) > FROM Sessions > > I get only the formatted date - missing the formatted time. > FORMAT_DATE is my own user-defined function that returns text data > type. I know I'm stating the obvious, but have you tried?: SELECT FORMAT_TIME(login_time) FROM Sessions since that's the part that does seem to be working. If it's giving you a blank then it's your function, not the concat operator. What do your custom functions do? Have you looked at the date and time functions built into SQLite? I expect that they'll cater for what you're after directly. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm trying to get the concat operator to work with my user-defined > function. This works fine: > > SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees > > But this doesn't work: > > SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) > FROM Sessions > > I get only the formatted date - missing the formatted time. Does this work: SELECT '!' || FORMAT_TIME(login_time) || '!' FROM Sessions; In other words, are you sure FORMAT_TIME doesn't return an empty string, or a string consisting only of whitespace? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
The problem appears to be in your function. Post the code for it. [EMAIL PROTECTED] wrote: > > I'm trying to get the concat operator to work with my user-defined > function. This works fine: > > SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees > > But this doesn't work: > > SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) > FROM Sessions > > I get only the formatted date - missing the formatted time. > FORMAT_DATE is my own user-defined function that returns text data > type. > > Can someone *please* check into this. I must get this working. > > Thank you > -brett > > > > > This message was sent using IMP, the Internet Messaging Program. > ___ > 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] Can't get concat operator to work
I'm trying to get the concat operator to work with my user-defined function. This works fine: SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees But this doesn't work: SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) FROM Sessions I get only the formatted date - missing the formatted time. FORMAT_DATE is my own user-defined function that returns text data type. Can someone *please* check into this. I must get this working. Thank you -brett This message was sent using IMP, the Internet Messaging Program. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
