[sqlite] ALTER TABLE ADD COLUMN - strange behaviour....Mac OSX
Folks. I had been struggling to implement ALTER TABLE ADD COLUMN in my app to change the database structure. However I would continuously face a locked database error. After much experimentation I have come to a very very strange conclusion. In my app if I do this: -start app -open database -read version table -close database -CHECK VERSION, then if needed -open database -ALTER TABLE ADD COLUMN -close database it works FINE> However if I do this: -start app -open database -read version table -close database -CHECK VERSION, then if needed *** WARN USER WITH ALERT *** iErr = Alert(alertID, 0); -open database -ALTER TABLE ADD COLUMN -close database The ALTER TABLE ADD COLUMN stage FAILS with Database is locked. The only thing I can think is that the Alert function is accessing the Application's resource fork, which involves the OS opening the resource fork. Note that the SQLite database is NOT open at the time. it subsequently opens OK, but the ALTER TABLE ADD COLUMN fails. Anyone have *any* idea what is happening ? Cheers mark -- [EMAIL PROTECTED] Tel: +44 208 340 5677 fax: +44 870 055 7790 http://www.gallery.co.uk - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ALTER TABLE ADD COLUMN - strange behaviour....Mac OSX
On 6/3/07, Mark Gilbert <[EMAIL PROTECTED]> wrote: Anyone have *any* idea what is happening ? I don't know nothing about MacOS, but you may want to check the result of sqlite3_close. It's possible it's not closing the database [1]. Regards, ~Nuno Lucas [1] http://www.sqlite.org/capi3ref.html#sqlite3_close Cheers mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Age calculation on literal
This function works and despite the convoluted construction it is pretty
fast, takes about 0.5 sec to convert one field in about 25000 rows.
I thought I can speed it up by replacing all the SQLite functions that
produce a literal by a VB variable. Indeed with this I can get it down to
about 0.3 secs. Does this mean that SQLite recalculates these functions
multiple times for every row? I suppose it does and in a way it makes sense
as 'now' changes over time.
In case any VB user is interested in this:
Function CurrentDateISO8601() As String
Dim lCY As Long
Dim lCM As Long
Dim lCD As Long
Dim strZeroMonth As String
Dim strZeroDay As String
lCY = Year(Date)
lCM = Month(Date)
lCD = Day(Date)
If lCM < 10 Then
strZeroMonth = "0"
End If
If lCD < 10 Then
strZeroDay = "0"
End If
CurrentDateISO8601 = "'" & lCY & "-" & _
strZeroMonth & lCM & "-" & _
strZeroDay & lCD & "'"
End Function
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
Dim strCDate As String
Dim lCM As Long
Dim lCD As Long
Dim strCY As String
Dim strCM As String
Dim strCD As String
lCM = Month(Date)
lCD = Day(Date)
strCY = "'" & CStr(Year(Date)) & "'"
If lCM < 10 Then
strCM = "'0" & CStr(lCM) & "'"
Else
strCM = "'" & CStr(lCM) & "'"
End If
If lCD < 10 Then
strCD = "'0" & CStr(lCD) & "'"
Else
strCD = "'" & CStr(lCD) & "'"
End If
strCDate = CurrentDateISO8601()
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = _
"case when " & strCDate & " >= " & _
"date(" & strField & ", '+' || " & strCY & " - " & _
"strftime('%Y', " & strField & ") || ' years') then " & _
"case when " & strCD & " < strftime('%d', " & strField & ") then " & _
"((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
"(" & strCM & " - strftime('%m', " & strField & "))) - 1 " & _
"else " & _
"((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
"(" & strCM & " - strftime('%m', " & strField & "))) - 0 " & _
"end " & _
"else " & _
"case when " & strCD & " < strftime('%d', " & strField & ") then " & _
"((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
"(" & strCM & " + (12 - strftime('%m', " & strField & " - 1 " & _
"else " & _
"((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
"(" & strCM & " + (12 - strftime('%m', " & strField & " - 0 " & _
"End " & _
"End" & strAS & strAlias
End Function
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 23:45
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal
Got the syntax right, but not the logic.
I believe this (VB) function will now get the right SQL to get the age in
months:
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = "case when date('now') >= " & _
"date(" & strField & ", '+' || (strftime('%Y',
'now') - " & _
"strftime('%Y', " & strField & ")) || ' years')
then " & _
"case when strftime('%d', 'now') <
strftime('%d', " & strField & ") then " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 0 " & _
"end " & _
"else " & _
"case when " & _
"strftime('%d', 'now') < strftime('%d', " &
strField & ") " & _
"then " & _
"(strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & " - 0 " & _
"End " & _
"End" & strAS & strAlias
End Function
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 21:46
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal
Got this now, after correcting the brackets:
SELECT
case when
[sqlite] Tomcat crashes with SQLite
Hi All My project is working with Tomcat, SQLite and javasqlite. http://www.ch-werner.de/javasqlite/ http://www.ysalaya.org Since a few weeks Tomcat server crashes very often: error 505 and I need to restart it manually. It is installed on FreeBSD 5.4. Please see the Tomcat log file bellow. It seems to be an error in SQLite outsite the Java Virtual Machine. ANY IDEAR ? An unexpected exception has been detected in native code outside the VM. Unexpected Signal : 11 occurred at PC=0x34D8493F Function=sqlite3VdbeExec+0x10B Library=/usr/local/lib/libsqlite3.so.8 Current Java thread: at SQLite.Vm.step(Native Method) at SQLite.Database.get_table(Database.java:175) at SQLite.JDBC2x.DatabaseX.get_table(JDBCConnection.java:437) at SQLite.JDBC2x.JDBCStatement.executeQuery(JDBCStatement.java:94) at SQLite.JDBC2x.JDBCPreparedStatement.executeQuery( JDBCPreparedStatement.java:71) at com.ysalaya.db.SQLite.execQuery(SQLite.java:73) at com.ysalaya.data.BotAnswerData.getAnswers(BotAnswerData.java:473) at com.ysalaya.dialog.Dialog.getBotMemDirectAnswer(Dialog.java:521) at com.ysalaya.dialog.Dialog.setAnswer(Dialog.java:331) at com.ysalaya.dialog.Dialog.(Dialog.java:102) at com.ysalaya.control.DialogControl.say(DialogControl.java:299) at com.ysalaya.control.DialogControl.doGet(DialogControl.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter( ApplicationFilterChain.java:173) at org.apache.catalina.core.ApplicationDispatcher.invoke( ApplicationDispatcher.java:672) at org.apache.catalina.core.ApplicationDispatcher.processRequest( ApplicationDispatcher.java:463) at org.apache.catalina.core.ApplicationDispatcher.doForward( ApplicationDispatcher.java:398) at org.apache.catalina.core.ApplicationDispatcher.forward( ApplicationDispatcher.java:301) at com.ysalaya.control.Control.dialog(Control.java:67) at com.ysalaya.control.Control.doGet(Control.java:30) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter( ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke( StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke( StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke( ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke( StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service( CoyoteAdapter.java:148) at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199) at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767) at org.apache.jk.common.ChannelSocket.processConnection( ChannelSocket.java:697) at org.apache.jk.common.ChannelSocket$SocketConnection.runIt( ChannelSocket.java:889) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run( ThreadPool.java:684) at java.lang.Thread.run(Thread.java:534) Dynamic libraries: 0x8048000 /usr/local/jdk1.4.2/bin/java 0x2807b000 /usr/lib/libpthread.so.1 0x2809f000 /lib/libc.so.5 0x2817a000 /usr/local/jdk1.4.2/jre/lib/i386/client/libjvm.so 0x285ad000 /usr/lib/libstdc++.so.4 0x2867f000 /lib/libm.so.3 0x2869a000 /usr/local/jdk1.4.2/jre/lib/i386/native_threads/libhpi.so 0x286a8000 /usr/local/jdk1.4.2/jre/lib/i386/libverify.so 0x286bd000 /usr/local/jdk1.4.2/jre/lib/i386/libjava.so 0x286db000 /usr/local/jdk1.4.2/jre/lib/i386/libzip.so 0x286e7000 /lib/libz.so.2 0x3475f000 /usr/local/jdk1.4.2/jre/lib/i386/libnet.so 0x34d49000 /usr/local/lib/libsqlite_jni.so.0 0x34d4f000 /usr/local/lib/libsqlite3.so.8 0x2804e000 /libexec/ld-elf.so.1 Heap at VM Abort: Heap def new generation total 1792K, used 413K [0x2c5d, 0x2c7c, 0x2cab) eden space 1600K, 13% used [0x2c5d, 0x2c607418, 0x2c76) from space 192K, 99% used [0x2c79, 0x2c7bfff8, 0x2c7c) to space 192K, 0% used [0x2c76, 0x2c76, 0x2c79) tenured generation total 23484K, used 19025K [0x2cab, 0x2e19f000, 0x305d) the space 23484K, 81% used [0x2cab, 0x2dd44798, 0x2dd44800, 0x2e19f000) compacting perm gen total 9216K, used 9002K [0x305d, 0x30ed, 0x345d) the space 9216K, 97% used [0x305d, 0x30e9a908, 0x30e9aa00, 0x30ed) Local Time = Sat Jun 2 07:13:46 2007 Elapsed Time = 30289 # # The e
Re: [sqlite] Tomcat crashes with SQLite
> My project is working with Tomcat, SQLite and javasqlite. > http://www.ch-werner.de/javasqlite/ > > http://www.ysalaya.org > > Since a few weeks Tomcat server crashes very often: error 505 > and I need to restart it manually. It is installed on FreeBSD 5.4. > > Please see the Tomcat log file bellow. > It seems to be an error in SQLite outsite the Java Virtual Machine. > > ANY IDEAR ? I see 2 possible explanations: 1. You use the same connection in different threads at the same time. 2. There are bugs in JDBC driver. If this is the case, try driver from http://www.zentus.com/sqlitejdbc. It for sure has bugs, but may be different and you won't even notice them. You may use pure java version - it will be probably slower than JNI based, but should never crash VM. -- Wicie, rozumicie Zobacz >>> http://link.interia.pl/f1a74 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite 3 Crash on OSX
--- Dan Kennedy <[EMAIL PROTECTED]> wrote: > On Fri, 2007-06-01 at 10:51 +0100, Mark Gilbert wrote: > > Folks. > > > > My app just crashed in the field randomly after some time running fine. > > > > Thread 12 Crashed: > > 0 libsqlite3.0.dylib 0x9406e587 sqlite3pager_get + 390 > > 1 libsqlite3.0.dylib 0x94054275 sqlite3BtreeCopyFile + 381 > > 2 libsqlite3.0.dylib 0x940542dd sqlite3BtreeCopyFile + 485 > > 3 libsqlite3.0.dylib 0x940545b6 sqlite3BtreeLast + 134 > > 4 libsqlite3.0.dylib 0x940830c1 sqlite3VdbeExec + 16021 > > 5 libsqlite3.0.dylib 0x94084c73 sqlite3_step + 270 > > 6 libsqlite3.0.dylib 0x9408b343 sqlite3_exec + 260 > > 7 libsqlite3.0.dylib 0x9407a53d sqlite3_get_table + 189 > > > > Anyone have anything specific to suggest ? > > Only that that stack trace looks corrupted to me. > sqliteBtreeLast() does not call sqlite3BtreeCopyFile(), > indirectly or otherwise. It might not necessarily be a corrupted stack trace. I've seen similarly odd traces for other programs on other platforms when the code was optimized and not compiled with -g. The symbols cited might just be the closest ones available since some functions were inlined. Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
