SQLLite Update/delete

Techlover · August 25, 2011, 7:34pm

Hi,

Does anyone know how to update or delete a row in sqllite. Mine hangs and then crashes my board.

I’ve seen this (from Compile-time Options)

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

[italic] This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

If this option is defined, then it must also be defined when using the ‘lemon’ tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website.
[/italic]

Not sure if this might have something to do with it. Any ideas,

Many thanks
Nathan

Gus_Issa · August 25, 2011, 9:01pm

Please provide a small but complete demo that we cam run to test on our end.

Techlover · August 26, 2011, 5:44pm

Ok, i’ve put my code on a diet (below). Now I cant even create a table (using sql create table) it just hangs then timesout. I’ve created a new project used the code below.

Not sure what is going on as I’ve created a table before.


using System;
using System.IO;
using Microsoft.SPOT;
using Microsoft.SPOT.Hardware;
using Microsoft.SPOT.IO;

using GHIElectronics.NETMF.Hardware;
using GHIElectronics.NETMF.SQLite;
using GHIElectronics.NETMF.IO;
using GHIElectronics.NETMF.System;
using System.Threading;

namespace JustDatabase
{
    public class Program
    {
        private static Database db;

        public static void Main()
        {
            // Mount Nand //
            PersistentStorage nand = new PersistentStorage("NAND");
            nand.MountFileSystem();

            // check for database create if need be //
            CreateDatabase(GetDriveVol("NAND"));
            
            // close up //
            Closedb(nand);
        }

        public static VolumeInfo GetDriveVol(string Drive)
        {
            VolumeInfo NandVolume = null;

            VolumeInfo[] AllConnectVol = VolumeInfo.GetVolumes();
            foreach (var vol in AllConnectVol)
            {
                if (vol.Name.ToUpper() == Drive.ToUpper())
                {
                    NandVolume = vol;
                    break;
                }
            }
            AllConnectVol = null;
            return NandVolume;
        }

        public static void CreateDatabase(VolumeInfo NANDvolume)
        {

            // Check database loaction //
            string DatabaseName = NANDvolume.RootDirectory + "\\database\\Demo.dbs";

            // check directory & db exisits //
            if (!Directory.Exists(NANDvolume.RootDirectory + "\\database"))
            {
                Directory.CreateDirectory(NANDvolume.RootDirectory + "\\database");

            }

            // Wait..//
            Thread.Sleep(100);
 

            if (!File.Exists(DatabaseName))
            {
                File.Create(DatabaseName);

                // Wait..//
                Thread.Sleep(100);

                // create table //
                db = new Database();
                db.Open(DatabaseName);
                db.ExecuteNonQuery("CREATE Table DemoSettings (AddrID TEXT,Name TEXT,Address TEXT,ExpireDate TEXT)");

                // Add some data //
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('1','Fusion','Address 1','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('2','Fusion','Address 2','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('3','Fusion','Address 3','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('4','Fusion','Address 4','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('5','Fusion','Address 5','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('6','Fusion','Address 6','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('7','Fusion','Address 7','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('8','Fusion','Address 8','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('9','Fusion','Address 9','26 August 2011')");
                db.ExecuteNonQuery("INSERT INTO DemoSettings (AddrID,Name,Address,ExpireDate) VALUES ('10','Fusion','Address 10','26 August 2011')");
            }
        }

        public static void Closedb(PersistentStorage nand)
        {
            // Close db then nand //
            db.Close();
            nand.UnmountFileSystem();
        }
    }
}

Gus_Issa · August 26, 2011, 5:58pm

one last thing:

what is your firmware version?
can you try it in memory In-Memory Databases

Techlover · August 26, 2011, 6:10pm

Firmware: 4.1.5.1. Ok will try memory now.

Thank you.

Techlover · August 26, 2011, 6:43pm

No memory doesnt work either. I’ve had to reinstall firmware again ;D Did my code work for you?

Gus_Issa · August 26, 2011, 9:04pm

Release notes says there were fixes for sqlite but you are not using the latest firmware

Techlover · August 27, 2011, 7:01pm

Just upgraded to firmware 4.16.0 had to revert back as board in just display “waiting for commands” even after deploying program.

Did my script work on your board GUS? or anyone. Are you on the latest firmware?

Gus_Issa · August 27, 2011, 9:11pm

No I didn’t try it. It is the weekend

Please update your firmware and start new project. The fixes in latest firmware should cover it.

Techlover · August 29, 2011, 2:55pm

No rest for the wicked but thank you.

None of my code works now, new or old. The new code only works in debug mode with a break point lol

I can’t win.

Gus_Issa · August 29, 2011, 3:00pm

Please update your SDK and double check the release notes file to double check the version
update your device and check the version
start new project and try to make a simple example suing memory, no file system

I do not want to pass this on to our team to check and it turns out it is in firmware so please let us double check first.

Techlover · August 29, 2011, 3:11pm

Ok will triple check. and let you know.

Techlover · August 29, 2011, 5:43pm

Hi Gus,

I did as you requested (copied the log results from firmware updates below) I’ve checked that the lastest firmware version match those on SDK (June 2011) 4.1.6.0/4.1.5.1.

I did noticed when loading the tiny boot log an entry [italic]Cannot find any entrypoint![/italic] Is that correct?

Also tried a simple “memory” program and still having to debug with a break point.

Thanks
Nathan

TinyBooter Updater Script

-I- Waiting …
-I- TCL platform : Windows NT
-I- SAM-BA CDC 2.9 on : windows
-I- Retrieved arguments from command line :
-I- argv 0 : COM13
-I- argv 1 : chipworkx
-I- argv 2 : TinyBooterLoader.tcl
-I- Connection : COM13 (target(comType) = 1)
-I- Board : chipworkx
-I- Traces Level : 0
-I- target(handle) : file21d01e0
Read device Chip ID at 0xfffff240 — get 0x019703a0
-I- Found processor : chipworkx (Chip ID : 0x019703a0)
-I- External RAM Settings : extRamVdd=1, extRamType=0, extRamDataBusWidth=32, extDDRamModel=0
-I- Loading applet isp-extram-ChipworkX.bin at address 0x301000
-I- Memory Size : 0x4000000 bytes
-I- Buffer address : 0x302254
-I- Buffer size: 0x0 bytes
-I- Applet initialization done
-I- External RAM initialized
-I- Command line mode : Execute script file : TinyBooterLoader.tcl
-I---------------------------------
-I- GHI Electronics, ChipworkX -
-I- TinyBooter Updater Script -
-I---------------------------------
-I Enable DataFlash
-I- DATAFLASH::Init 0 (trace level : 0)
-I- Loading applet isp-dataflash-ChipworkX.bin at address 0x20000000
-I- Memory Size : 0x420000 bytes
-I- Buffer address : 0x20002A40
-I- Buffer size: 0x40740 bytes
-I- Applet initialization done
-I- Send File ./TinyBooterLoader.bin at address 0x00000
GENERIC::SendFile ./TinyBooterLoader.bin at address 0x0
-I- File size : 0x26AC0 byte(s)
-I- Writing: 0x26AC0 bytes at 0x0 (buffer addr : 0x20002A40)
-I- 0x26AC0 bytes written by applet
-I- Temp file : C:/Users/Nathan/.sam-ba.testCompareFile
-I- Compare File : ./TinyBooterLoader.bin with memory at address : 0x00000 , for 158400 byte(s)
-I- Read File C:/Users/fusion/.sam-ba.testCompareFile at address 0x00000
GENERIC::ReceiveFile C:/Users/fusion/.sam-ba.testCompareFile : 0x26AC0 bytes from address 0x0
-I- Reading: 0x26AC0 bytes at 0x0 (buffer addr : 0x20002A40)
Sent file & Memory area content (address: 0x00000, size: 158400 bytes) match exactly !
-I---------------------------------
-I- Script Completed -
-I- Please Reset the Device -
-I---------------------------------

Tiny boot log

Chk signature
Signature PASS
Chk signature
Signature PASS
Created EE.
Started Hardware.
Found debugger!
Create TS.
Loading start at 201b9204, end 201c2d50
Attaching file.
Assembly: mscorlib (4.1.2821.0) (3880 RAM - 33236 ROM - 19134 METADATA)
AssemblyRef = 0 bytes ( 0 elements)
TypeRef = 0 bytes ( 0 elements)
FieldRef = 0 bytes ( 0 elements)
MethodRef = 0 bytes ( 0 elements)
TypeDef = 1112 bytes ( 139 elements)
FieldDef = 272 bytes ( 135 elements)
MethodDef = 1572 bytes ( 786 elements)

Attributes = 0 bytes ( 0 elements)
TypeSpec = 16 bytes ( 4 elements)
Resources = 232 bytes ( 29 elements)
Resources Files = 16 bytes ( 2 elements)
Resources Data = 437 bytes
Strings = 1551 bytes
Signatures = 2126 bytes
ByteCode = 11702 bytes

Attaching file.
Assembly: Microsoft.SPOT.Native (4.1.2821.0) (1144 RAM - 6516 ROM - 4479 METADATA)
AssemblyRef = 4 bytes ( 1 elements)
TypeRef = 80 bytes ( 20 elements)
FieldRef = 0 bytes ( 0 elements)
MethodRef = 60 bytes ( 15 elements)
TypeDef = 336 bytes ( 42 elements)
FieldDef = 192 bytes ( 95 elements)
MethodDef = 224 bytes ( 111 elements)

Attributes = 48 bytes ( 6 elements)
TypeSpec = 0 bytes ( 0 elements)
Resources = 72 bytes ( 9 elements)
Resources Files = 8 bytes ( 1 elements)
Resources Data = 747 bytes
Strings = 648 bytes
Signatures = 595 bytes
ByteCode = 418 bytes

Loading Deployment Assemblies.
Resolving.

Total: (4124 RAM - 39752 ROM - 23613 METADATA)
AssemblyRef = 4 bytes ( 1 elements)
TypeRef = 80 bytes ( 20 elements)
FieldRef = 0 bytes ( 0 elements)
MethodRef = 60 bytes ( 15 elements)
TypeDef = 1448 bytes ( 181 elements)
FieldDef = 464 bytes ( 230 elements)
MethodDef = 1796 bytes ( 897 elements)

DebuggingInfo = 900 bytes

Attributes = 48 bytes ( 6 elements)
TypeSpec = 16 bytes ( 4 elements)
Resources Files = 72 bytes ( 3 elements)
Resources = 304 bytes ( 38 elements)
Resources Data = 1184 bytes
Strings = 2199 bytes
Signatures = 2721 bytes
ByteCode = 12120 bytes

GC: 1msec 5136 bytes used, 59760924 bytes available
Type 0F (STRING ): 24 bytes
Type 15 (FREEBLOCK ): 59760924 bytes
Type 17 (ASSEMBLY ): 5040 bytes
Type 34 (APPDOMAIN_HEAD ): 72 bytes

Total: (4124 RAM - 39752 ROM - 23613 METADATA)
AssemblyRef = 4 bytes ( 1 elements)
TypeRef = 80 bytes ( 20 elements)
FieldRef = 0 bytes ( 0 elements)
MethodRef = 60 bytes ( 15 elements)
TypeDef = 1448 bytes ( 181 elements)
FieldDef = 464 bytes ( 230 elements)
MethodDef = 1796 bytes ( 897 elements)

DebuggingInfo = 900 bytes

Attributes = 48 bytes ( 6 elements)
TypeSpec = 16 bytes ( 4 elements)
Resources Files = 72 bytes ( 3 elements)
Resources = 304 bytes ( 38 elements)
Resources Data = 1184 bytes
Strings = 2199 bytes
Signatures = 2721 bytes
ByteCode = 12120 bytes

Ready.
Cannot find any entrypoint!
Done.
Waiting for debug commands…

Gus_Issa · August 29, 2011, 5:57pm

Ok we will try here

User_12 · August 29, 2011, 11:40pm

It should be fine.
Please rebuild the project. Even better if you create a new project and add assemblies and the code.

Techlover · August 30, 2011, 5:01am

Hi Joe,

Thanks, Yeah I have done this numerous times as requested by Gus. I’ve erased and followed the firmware instructions and confirmed the version installed.

I already have project (big one) does this mean I am going to have recreate it again to work with the updated firmware once we get it working?

Thanks
Nathan

Gus_Issa · August 30, 2011, 7:13am

No, you only need to remove all dlls then add them back

Techlover · August 30, 2011, 4:18pm

lost now… looks like i’m going to have to revert back minus sqlite!! sh***t!!

Thanks for your help guys.

Nathan

Brett · August 30, 2011, 10:36pm

you’re lost about how to remove the DLLs and re-add? If so, all you need to do is remove all your references in your project, then re-add them manually. The VS project “caches” the dlls into your project from the GHI SDK version you had when you created the project; the rip-and-replace means you get the ones from your current SDK version.

Techlover · August 31, 2011, 5:13am

No lost as to what the problem is.