SQLIte insertion looping trough text

Hi I am trying to import some pieces of a long text into SQLite table but I have the following problem.

I use the bellow code in order to read the long text stored in a test DB.

var mycollectoridlength = 0, mybill, line =  0;

notesdb.transaction(function(t) {
      t.executeSql('SELECT * FROM billtest;', [], function(t, bulkdata){
           mybill = bulkdata.rows.item(0);

and then loop through long text and try to insert some pieces of it into another DB.

for (i = 1; i <= ((mybill.testtext.length)/126); i += 1) {

     alert(line) // alerts 1, 126, 252 ... 

     t.executeSql('INSERT OR IGNORE into buildings (buildingcode, buildingaddress, buildingcollect, buildingpay) VALUES (?,?,?,?);',
          [mybill.testtext.substr((line+0), 5).trim(), mybill.testtext.substr((line+44), 40).trim(), 0, 0], function(){

          alert(line) // alerts 10962 (87 *  126)
     });   


     line = i * 126;
}

but the line inside executeSql is not sync with the loop. The SQLite insertion executed after the loop. How can I solve it???

SQLite Alternatives

I am looking for an alternative for sqlite. I have an application that has lighttpd built into it and serves php scripts to interact with multiple instances of another application. All architectural questions aside, for some reason, the person who wrote this chose to use sqlite as the database for the php scripts -- to store and retrieve information for the scripts themselves. As such, it appears we have locking issues. To deal with that the programmer used stuff like this:

$executed = $storeDB->exec($updateSQL);
while($executed === FALSE && $i<10) {
    $storeDB->close();
    $storeDB = new SQLite3('C:/Path/path2/LightTPD/store.db');
    $pragmaDB = "PRAGMA journal_mode=WAL";
    ($storeDB->exec($pragmaDB));
    $executed = $storeDB->exec($updateSQL);
    $i++;
}       

I understand why sqlite was chosen as it can be copied over and over, does not need to installed as a server, and is very small. However, with multiple instances of an application hitting those php scripts, sometimes concurrently, I can see that it is causing some problems: some information retrieval operations fail and some data is not updated. I get Warnings in the log like this.

An UPDATE statement

[07-Mar-2015 21:03:44 UTC] PHP Warning:  SQLite3::exec(): database is locked in C:\Path\path2\LightTPD\htdocs\ProcessRequest.php on line 194

and

A SELECT statement

[07-Mar-2015 21:22:25 UTC] PHP Warning:  SQLite3::query(): Unable to prepare statement: 5, database is locked in C:\Path\path2\LightTPD\htdocs\ProcessRequest.php on line 151

I say all of that to ask, does anyone have any comparable alternatives to sqlite for this situation? I listed the reasons it was chosen to begin with, but the primary need is that it be able to be copied, though I can get around that if necessary.

Java config storage choice

First of all, I'm new here as writer, but not as reader. I used stackoverflow for years to find a lot of useful information, but now I decided to join the community.

Now my question about the Java config storage choice.

The application

Currently I'm working on a Java (FX) project for myself. The Java application will be used on a few desktop machines. The goal of the application is to power on/off Linux devices over SSH (for example a server).

I want to make it possible for the user to add 5/10 devices with information of the device (name and IP), SSH credentials for the power on command and SSH credentials for the power off command (user, host, key, port, timeout, command).

There will be a JavaFX GUI, this GUI will have a menu bar with a menu called Devices to select, add, edit or delete a device. In the center of the GUI there will be 2 buttons, they will be used for the power on/off commands (or a switch).

Design

• The programming language is Java.


• The GUI is based on JavaFX.


• For the power on/off commands I use the Command Design Pattern.

Storage

I tried different storage options (SQLite, Properties and XML) for storing information of the devices. But I didnt found out which option will fits the best in this situation.

The SQLite storage felt too big for this.

If I will go for seperated properties/XML storage, than there wille be around 5/10 different properties/XML files. Also the application has to switch between this files. A file will look like this:

device.name = Server
device.host = host.com

power_on.key = /path/to/rsa
power_on.user = user
power_on.host = host.com
power_on.port = 22
power_on.timeout = 10000
power_on.command = execute power on command

power_off.key = /path/to/rsa
power_off.user = user
power_off.host = host.com
power_off.port = 22
power_off.timeout = 10000
power_off.command = execute power off command

If I will go for 1 properties/XML file, than the file will look like this:

# Device 1
server.device.name = Server
server.device.host = host.com

server.power_on.key = /path/to/rsa
server.power_on.user = user
server.power_on.host = host.com
server.power_on.port = 22
server.power_on.timeout = 10000
server.power_on.command = execute power on command

server.power_off.key = /path/to/rsa
server.power_off.user = user
server.power_off.host = host.com
server.power_off.port = 22
server.power_off.timeout = 10000
server.power_off.command = execute power off command

# Device 2
pc.device.name = PC
pc.device.host = host.com

pc.power_on.key = /path/to/rsa
pc.power_on.user = user
pc.power_on.host = host.com
pc.power_on.port = 22
pc.power_on.timeout = 10000
pc.power_on.command = execute power on command

pc.power_off.key = /path/to/rsa
pc.power_off.user = user
pc.power_off.host = host.com
pc.power_off.port = 22
pc.power_off.timeout = 10000
pc.power_off.command = execute power off command

Question

My question is; which storage choice (and/or design pattern) will fits the best in this situation? It makes me really confused because I tried different things without any good solution yet.

I hope you guys can help me out with this. All feedback, suggestions etc are welcome. Thank you in advance!

Optimize the SQLite select statement to run a lot faster

This the Query I want to optimize, it seems to take 30 seconds or so to run on a database around 1.7GB with about 1.5million Rows in the PepTargetEntity and the TargetMatchEntity and only thousands in the Customer Table and SearchEntity Table. I expect such a simple statement to take milliseconds to complete.

There are indexes on :

• Customer(SearchId)


• PepTargetEntity(CustomerId)


• TargetMatchEntity(CustomerId)

If anyone has any ideas it would be much appreciated, Thanks in advance.

SELECT  CustomerId, Rank, PepId, TargetId, FalsePositive   
FROM (SELECT  T.CustomerId, T.Rank, T.Id AS PepId, NULL AS TargetId, T.FalsePositive, C.Surname, C.Firstname FROM PepTargetEntity T      
INNER JOIN Customer C ON T.CustomerId= C.Id         
WHERE c.SearchId = 3265 OR 3270 = 0

UNION

SELECT  T.CustomerId, T.Rank, Null AS PepId, T.Id AS TargetId, T.FalsePositive, C.Surname, C.Firstname 
FROM TargetmATCHEntity T
INNER JOIN Customer C ON T.CustomerId= C.Id  
WHERE c.SearchId = 3265 OR 3270 = 0)     
ORDER BY Surname ASC,FirstName ASC,Rank DESC   
LIMIT 50   
OFFSET 0

SQLite Query statement - Objective C

My problem is that i cant get the values from the select query as it executes 101 error.

I think that my problem is that it has to be with the quotes in select statement

sqlite3 * database ;

NSString * path = [[[ NSBundle mainBundle ]resourcePath ] stringByAppendingPathComponent : @"cars.sqlite"];
int returnCode = sqlite3_open ([ path cStringUsingEncoding : NSUTF8StringEncoding], &database) ;

sqlite3_stmt * statement ;
char * st ;
NSString *querySQL = [NSString stringWithFormat: @"select brand,model from cars where brand=%@;", tmpbrand];

const char *query_stmt = [querySQL UTF8String];
st = sqlite3_mprintf (query_stmt);

if(sqlite3_prepare_v2 ( database , query_stmt , -1 ,& statement ,NULL )==SQLITE_OK){
returnCode = sqlite3_step ( statement ) ; // this statement returns a record

while ( returnCode == SQLITE_ROW ) // while there are rows
{
    models*tmp=[[models alloc]init];

    char* n=(char*)sqlite3_column_text(statement,0);
    tmp=[NSString stringWithCString:n encoding:NSUTF8StringEncoding];
    char* model=(char*)sqlite3_column_text(statement, 1);
    tmp.model=[NSString stringWithCString:model encoding:NSUTF8StringEncoding];


    [data addObject:tmp];

    returnCode = sqlite3_step ( statement ) ; // this statement returns a record

}
    sqlite3_finalize(statement);
}

}

Cannot insert multiple lines in Sqlite using C#

I know this is a very simple question but I cannot find any answer that can help me. I'm using C# in order to connect to a SQLITE database and insert some data. Here is the code :

private void OpenDb()
        {
            this.m_handle = new SQLiteConnection("Data Source=" + m_filename + ".sqlite3;Version=3;");
            this.m_handle.Open();
        }

        private void CloseDb()
        {
            this.m_handle.Close();
        }

        private void CreateDb()
        {
            this.OpenDb();
            string query = "CREATE TABLE StreamingUrl ([url] varchar(255) NOT NULL,  [DateRequest] TEXT NOT NULL);";
            SQLiteCommand command = new SQLiteCommand(query, this.m_handle);
            command.ExecuteNonQuery();
            this.CloseDb();
        }

        public void AddStreamingUrl(string url)
        {
            this.OpenDb();
            string query = "INSERT INTO StreamingUrl (url, DateRequest) VALUES ('" + url + "', datetime('now', 'localtime'));";
            SQLiteCommand command = new SQLiteCommand(query, this.m_handle);
            command.ExecuteNonQuery();
            this.CloseDb();
        }

I can create the database as well as the table. When I try to insert one data, it works good. However, when I try to add a second row it does not work, sqlite does an update on it (DateRequest is updated, so I think the row is updated). My database file is not added to the project as reference or things like that.

When I use SQLiteAdministrator with this insert query, it works. So I think that my code is wrong but I cannot figure it out. I don't have any exception or errors.

EDIT : It may be important but I have two projects in my VS solution : one dll and one exe. The code is in the dll and I run the exe project> I don't know if that changes something.

EDIT2 : Added the code that calls `CreateDb. It is in my class constructor constructor:

this.m_filename = filename;
            if (System.IO.File.Exists(this.m_filename) == false)
            {
                SQLiteConnection.CreateFile(this.m_filename + ".sqlite3");
                this.CreateDb();
            }

AddStreamingUrl is called by a event so I cannot show you the entire code. Here is the part I think it's interesting :

this.NetworkManager.StreamingUrl += Passerelle_StreamingUrl;

And after the event is raised :

void Passerelle_StreamingUrl(object sender, EventArgs e)
        {
            log.AddStreamingUrl((e as StreamingUrl).Url);
            Console.WriteLine("Done");
        }

How to UPDATE multiple columns using a correlated subquery in SQLite?

I want to update multiple columns in a table using a correlated subquery. Updating a single column is straightforward:

UPDATE route
SET temperature = (SELECT amb_temp.temperature
                   FROM amb_temp.temperature
                   WHERE amb_temp.location = route.location)

However, I'd like to update several columns of the route table. As the subquery is much more complex in reality (JOIN with a nested subquery using SpatiaLite functions), I want to avoid repeating it like this:

UPDATE route
SET
  temperature = (SELECT amb_temp.temperature
                 FROM amb_temp.temperature
                 WHERE amb_temp.location = route.location),
  error = (SELECT amb_temp.error
           FROM amb_temp.temperature
           WHERE amb_temp.location = route.location),

Ideally, SQLite would let me do something like this:

UPDATE route
SET (temperature, error) = (SELECT amb_temp.temperature, amb_temp.error
                            FROM amb_temp.temperature
                            WHERE amb_temp.location = route.location)

Alas, that is not possible. Can this be solved in another way?

Here's what I've been considering so far:

• use INSERT OR REPLACE as proposed in this answer. It seems it's not possible to refer to the route table in the subquery.


• prepend the UPDATE query with a WITH clause, but I don't think that is useful in this case.