Main Site Documentation

SQLLite Update/delete


#1

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 http://www.sqlite.org/compile.html)

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


#2

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


#3

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();
        }
    }
}





#4

one last thing:

  1. what is your firmware version?
  2. can you try it in memory http://www.sqlite.org/inmemorydb.html

#5

Firmware: 4.1.5.1. Ok will try memory now.

Thank you.


#6

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


#7

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


#8

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?


#9

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

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


#10

No rest for the wicked :smiley: 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.


#11
  1. Please update your SDK and double check the release notes file to double check the version
  2. update your device and check the version
  3. 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.


#12

Ok will triple check. and let you know.


#13

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…


#14

Ok we will try here


#15

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


#16

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


#17

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


#18

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

Thanks for your help guys.

Nathan


#19

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.


#20

No lost as to what the problem is.