Re: [sqlite] Possible bug
Hello. The EzTools office is closed for 1 week holidays. Will be open again around 17 April. If you have purchase a product, we will process your order then. Sorry for any inconvenience. EzTools Support ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite 2
Hello. The EzTools office is closed for 1 week holidays. Will be open again around 17 April. If you have purchase a product, we will process your order then. Sorry for any inconvenience. EzTools Support ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite, vb6 and date time field
Hello. The EzTools office is closed for 1 week holidays. Will be open again around 17 April. If you have purchase a product, we will process your order then. Sorry for any inconvenience. EzTools Support ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hello I am a newbie : for SQLite : Create db : VB6
Hello. I happened to see this thread (I'm EzTools suppport). The Sqlite open function automatically creates a new file if the file doens't exist. SqlitePlus does the same thing (since it just passes through to Sqlite). However, I have added an additional, optional parameter that will cause the SqliteDb.Open method to fail if the file doesn't exist. But it is false by default, so calling open with a filename that doesn't exist should create the file. Maybe its being created in a different folder than your .exe? Its possible its in a place you don't expect if you aren't passing a full path. Anyway, here is a good way to call SqliteDb.Open from a VB app: db.Open App.Path & "my_database.db", 0, false Let me know via my eddress if you still have problems ([EMAIL PROTECTED]). cheers -brett Quoting palmer ristevski <[EMAIL PROTECTED]>: > > That is Great OLAF > That is all that I am looking for. > I just want to be able to create new files, > then create tables and populate them and > do simple queries. > If I can do that with your stuff, that would be amazing > for me. I have had great great difficulty trying to find > wrapper for VB6! > Thanks once again. > I will try it out, and if I run into problems i will email you. > > Thanks once again. > > Pablopico > >> To: sqlite-users@sqlite.org >> From: [EMAIL PROTECTED] >> Date: Wed, 30 Apr 2008 03:17:30 + >> Subject: Re: [sqlite] Hello I am a newbie : for SQLite : Create db : VB6 >> >> palmer ristevski <[EMAIL PROTECTED]> writes: >> >> Hi Palmer, >> >> > I am new to this type of Forum.Here is my question : >> > My development platform is VB6. I am using "SQLitePlus >> > COM-DLL" from ez-tools.com.They have code to access >> > and query an existing ".db" file, but I wish to know >> > how to make a function call to create new SQLite >> > database files on harddisk.How would you do this using >> > VB6?What is the function call that you would have to make. >> > I know how to do this with SQLite at the command >> > line, and I could use VB6 to execute these commands >> > at the command line, but I want a more direct way to create >> > new database files.Hope someone can help me out. >> >> Sorry, no experience with the SQLitePlus-COM-wrapper >> (maybe you should ask their technical support). >> >> In case you want to try out something, working >> similar to "ADO/DAO-style"... >> The following example is Code for my COM-wrapper, >> which is available here: >> www.datenhaus.de/Downloads/dhRichClientDemo.zip >> It consists of three Binaries, placed in the >> Public Domain: >> dhRichClient.dll (COM-Dll - ADO-like WrapperClasses) >> sqlite35_engine.dll (StdCall-Dll, based on SQLite 3.5.7) >> DirectCOM.dll (Std-Dll, allows regfree COM-instancing) >> >> Small example how to use it, including the >> creation of a new DB, in case the file doesn't >> exists yet: >> >> Dim Cnn As cConnection, Cmd As cCommand, Rs As cRecordset >> Dim i As Long, FileName As String >> FileName = "c:MyFile.db" >> >> Set Cnn = New cConnection 'create a Cnn-Object >> >> On Error Resume Next >> Cnn.OpenDB FileName 'attempt, to open a DB-file >> If Err Then 'DB-File doesn't exists... >> Cnn.CreateNewDB FileName '...so we create one >> End If >> >> 'Ok, let's create a table >> Cnn.Execute "Create Table If Not Exists " & _ >> "Tbl(ID Integer Primary Key, Txt Text)" >> >> 'now we insert a few records over a Command-Object >> Set Cmd = Cnn.CreateCommand("Insert Into Tbl Values(?,?)") >> For i = 1 To 5 >> Cmd.SetText 2, "SomeText_" & i >> Cmd.Execute >> Next i >> >> 'and finally we request a Resultset... >> Set Rs = Cnn.OpenRecordset("Select * From Tbl") >> >> Do Until Rs.EOF 'loop over it... >> Debug.Print Rs!ID, Rs!Txt '...and print its contents >> Rs.MoveNext >> Loop >> >> >> Olaf Schmidt >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _ > Express yourself wherever you are. Mobilize! > http://www.gowindowslive.com/Mobile/Landing/Messenger/Default.aspx?Locale=en-US?ocid=TAG_APRIL > ___ > 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] garbage mail messages
OK Dan, you have the solution. The count was including the terminating NULL char. Making it not include the NULL char fixed the problem. Another question: For an empty result, should I return 0 or -1? And should the string be NULL or "" ? Bear in mind that its an empty result - not a NULL result. Thanks a million -brett Quoting Dan Kennedy > > > 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. 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
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
[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
[sqlite] User defined functions and SQL Expressions
Hello. I've written a UDF named ENCRYPT. I want to do something like this: UPDATE Employees SET EncryptedName = ENCRYPT(LastName + ', ' + FirstName) The UDF gets called, but not with my data - rather something like "0.0". Kind of weird. Does Sqlite handle SQL expressions inside the parentheses? I also tried this but without success: UPDATE Employees SET EncryptedName = ENCRYPT(LastName) + ', ' + ENCRYPT(FirstName) Thx -brett This message was sent using IMP, the Internet Messaging Program. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Semantic Database Design
FYI, here is a good article describing how to serialize .NET data (as BLOB) to Sqlite database: http://www.windevtools.com/show_article.asp?pg=dotNET%20Serialization%20to%20SQLite%20Database/howto.html cheers This message was sent using IMP, the Internet Messaging Program. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is it a bug?
I have a user-defined function named DECRYPT, which decrypts column data encrypted by my other UDF named ENCRYPT. The UDF callback function (which does the decrypting) calls sqlite3_result_blob after decrypting the data. Sqlite does return the data OK...BUT! It doesn't provide the data type for the column when I call sqlite3_column_decltype for the column. The return value is NULL. I must have the column data type, else my code can't properly interpret the value. This must be a bug, right? -brettg This message was sent using IMP, the Internet Messaging Program. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory DB to disk DB
Very good Keith. I think this will work fine for me. thanks Quoting Keith Herold <[EMAIL PROTECTED]>: > I am/was doing this in application, with 2.8.15 . I simply attached > the on-disk database to the memory, and then wrote a bunch of dump > queries to drop the memory data to disk (from the memory db > connection): > > ATTACH 'C:\my_database_on_disk.sqlitedb' AS diskdb ; > > Unfortunately, I don't think you can execute transactions on the > attached database in 2.8.15, and I couldn't think of a way to attach > to the memory database from the disk database. I think you can > execute transactions on the attached db in 3.x, though. > > It works ok, although since I was using the in-memory database as a > preliminary db (multithreaded app), my dump queries got ugly, and > quite slow on large sets, because I had to do the checks to ensure > that the in-memory db wasn't dumping data that already existed in the > database (I know, triggers, etc., but I hadn't tried those yet, and > INSERT OR IGNORE scares me). I eventually substituted writing text > sql scripts to disk, and then executing those directly into the disk > database, which saved me 25-30% of the insert time. > > --Keith > > On Fri, 18 Feb 2005 04:01:32 +0100, chorlya <[EMAIL PROTECTED]> wrote: > > I gues you could attach in-memory db to a newly created disk db and > > then do something like > > > > CREATE TABLE newDiskTbl AS SELECT * FROM memoryTbl > > > > Take a look at http://www.sqlite.org/lang_createtable.html for more details > > > > Regards, > > chorlya > > > > On Fri, 18 Feb 2005 11:13:40 +1100, [EMAIL PROTECTED] > > <[EMAIL PROTECTED]> wrote: > > > > > > I have a situation where I start with an in-memory DB, then need to save > the > > > entire thing to a new disk DB. Does anyone know the best way to do this? > > > Would I attach the memory DB to a newly created disk DB? Is this even > > > possible? I notice the COPY command is not supported in 3.x according to > the > > > documentation on the website, so even if I could attach it I would still > need a > > > way to copy the tables. Any suggestions would be greatly appreciated. > > > > > > TIA > > > -brett > > > > > > > > > This message was sent using IMP, the Internet Messaging Program. > > > > > > > > > > > -- > ** > - Ever notice how 'big' isn't, compared to 'small'? > > - Sounds like a Wookie; acts like mad cow. > > - I'm not a professional; I just get paid to do this. > > - Rules for programming: >1. Get it working, right? >2. Get it working right. > > - Things I've learned about multithreaded programming: > > 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt > rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt > iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb > .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il > m^Ne from two or more threads > ** > This message was sent using IMP, the Internet Messaging Program.
[sqlite] Memory DB to disk DB
I have a situation where I start with an in-memory DB, then need to save the entire thing to a new disk DB. Does anyone know the best way to do this? Would I attach the memory DB to a newly created disk DB? Is this even possible? I notice the COPY command is not supported in 3.x according to the documentation on the website, so even if I could attach it I would still need a way to copy the tables. Any suggestions would be greatly appreciated. TIA -brett This message was sent using IMP, the Internet Messaging Program.
Re: [sqlite] db admin tool
Hello Sten. Please see www.sqliteplus.com for an excellent tool, and also a COM DLL wrapper that you can use from VB, C++ and .NET. Quoting Sten Larsson <[EMAIL PROTECTED]>: > Is there any free or commercial DB admin tool that works with sqlite 3.08 and > runs under Windows. > > (No webbased tool.) > > thanks! > -sten This message was sent using IMP, the Internet Messaging Program.
Re: [sqlite] Whole word searches
Thanks Kurt. I think glob looks promising. best regards -brett Quoting Kurt Welgehausen <[EMAIL PROTECTED]>: > > How do I search on whole words ... > > find ... "main" and don't want ... "maintain" > > I tried using brackets as specified in the SQL spec > > There are no brackets in the SQL spec; that's a Microsoft extension. > SQLite's glob operator has similar syntax; it's an extension also. > > If your word separators are always spaces, you can use > > col like '% main %' or col like 'main %' or col like '% main' or > col = 'main' > > If there can be other characters between words, you can use > something like > > col glob '*[ ,.]main[ ,.]*' or col glob 'main[ ,.]*' or > col glob '*[ ,.]main' or col = 'main' > > Regards > This message was sent using IMP, the Internet Messaging Program.
[sqlite] Whole word search
I asked this question yesterday but got no answer, so here goes my second try. How do I search on whole words within a text field. For example, if I want to find the whole word "main" and don't want records with "maintain", how can I do that with Sqlite? I tried using brackets as specified in the SQL spec but it doesn't work - I get errors. Here is some documentation from MS SQL Server: [ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. This message was sent using IMP, the Internet Messaging Program.
[sqlite] Whole word searches
How do I search on whole words within a text field. For example, if I want to find the whole word "main" and don't want records with "maintain", how can I do that with Sqlite? I tried using brackets as specified in the SQL spec but I get errors. Here is some documentation from MS SQL Server: [ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. This message was sent using IMP, the Internet Messaging Program.
Re: [sqlite] SQLite manager for Windows
SqlitePlus - www.sqliteplus.com Quoting Edovia Technologies <[EMAIL PROTECTED]>: > Hi, > > > > Anyone knows about a SQLite 3 manager for Windows? The only ones I've found > so far seems to only be compatible with SQLite 2. > > > > Thanks! > > > > Luc Vandal > Edovia Technologies Inc. > [EMAIL PROTECTED] > www.edovia.com > > > > > > > > This message was sent using IMP, the Internet Messaging Program.