Skip to content

Reemplazar cadenas de caracteres en MySql

DataBase


 

 

 

Para reemplazar una cadena de texto por otra, sin necesidad de estar tupla a tupla:

UPDATE `tabla` SET campo = REPLACE(campo,'texto a buscar','nuevo texto');

Y si por ejemplo queremos actualizar un determinado campo comprendido entre una fecha:

UPDATE `tabla` SET campo = REPLACE(campo,'texto a buscar','nuevo texto') WHERE fecha BETWEEN '2012-06-01' AND '2012-06-31';

L

Continue reading "Reemplazar cadenas de caracteres en MySql"

MySQL string functions

DataBase

SUBSTR() function

MySQL SUBSTR() returns the specified number of characters from a particular position of a given string. SUBSTR() is a synonym for SUBSTRING().

Syntax:

SUBSTR(str, pos, len)

Arguments

Name Description
str A string from which a substring is to be returned.
pos An integer indicating a string position within the string str.
len

An integer indicating a number of characters to be returned.

LEFT() function

MySQL LEFT() returns a specified number of characters from the left of the string. Both the number and the string are supplied as arguments of the function.

Syntax:

LEFT (string, length)

Arguments

Name Description
string The string from which a number of characters from the left are to be returned.
length An integer which indicates the number of characters to be returned starting from the left of the string in the first argument.

RIGHT() function

MySQL RIGHT() extracts a specified number of characters from the right side of a string.

Syntax:

RIGHT(str, len)

Arguments

Name Description
str A string from whose right side a number of characters are to be extracted.
len An integer indicating the number of characters to be extracted from str.

 

Continue reading "MySQL string functions"

Adding Dynamic Data with the SQLite Plugin

DataBase

Add this to our init function to create the table as soon as our database is created.

function init() {
    app.openDb();
    app.createTable();
}

We have a database, we have a table, but we don't have any data. Lets look at inserting some new records:

Inserting a New Record

app.insertRecord = function(t) {
    app.db.transaction(function(tx) {
        var cDate = new Date();
        tx.executeSql("INSERT INTO MyTable(text_sample, date_sample) VALUES (?,?)",
                      [t, cDate],
                      app.onSuccess,
                      app.onError);
    });
}

This example shows us how we can pass a parameterized query to the SQLite engine. If you remember, we created the id field as a primary key integer, so that column will be auto-incremented each time a new record is inserted. We will pass the variable t into the function which will get inserted in the text_sample column. Finally we create a new JavaScript date object and pass that in as the current date/time to the datesample column.

You'll also notice that we have two other functions called, app.onSuccess and app.onError. You can probably guess that those are called when the result of the insert was successful or if it failed. For now you can just log those to the console like this:

app.onSuccess = function(tx, r) {
    console.log("Your SQLite query was successful!");
}

app.onError = function(tx, e) {
    console.log("SQLite Error: " + e.message);
}

Updating an Existing Record

Once we have data in there, we're probably going to want to update it at some point.

app.updateRecord = function(id, t) {
    app.db.transaction(function(tx) {
        var mDate = new Date();
        tx.executeSql("UPDATE MyTable SET text_sample = ?, date_sample = ? WHERE id = ?",
                      [t, mDate, id],
                      app.onSuccess,
                      app.onError);
    });
}

If you understood the insertRecord function, this one should make sense as well. We're simple updating the record with data we are passing, and we are identifying the record by passing the id number. Again we are calling the app.onSuccess and app.onError functions.

Deleting an Existing Record

You should be getting the hang of it by now (especially if you are at all familiar with SQL!). Deleting a record is just as easy - and oh so powerful:

app.deleteRecord = function(id) {
    app.db.transaction(function(tx) {
        tx.executeSql("DELETE FROM MyTable WHERE id = ?",
                      [id],
                      app.onSuccess,
                      app.onError);
    });
}

Selecting Records/Querying the Database

We've gone over how to get data into the database, updated, and removed. Lets look at how we can select data from the database to use in our app. In this example we'll query our table and take that recordset to convert it to JSON, as that is usually easier for us to deal with.

app.selectAllRecords = function(fn) {
    app.db.transaction(function(tx) {
        tx.executeSql("SELECT * FROM MyTable ORDER BY id", [],
                      fn,
                      app.onError);
    });
}

This is a little different, as we have to send a callback function (the variable fn) along to do something with the data after the query is executed. Here is an example of how you could call the above query and do something with the data:

function getAllTheData() {
    var render = function (tx, rs) {
        // rs contains our SQLite recordset, at this point you can do anything with it
        // in this case we'll just loop through it and output the results to the console
        for (var i = 0; i < rs.rows.length; i++) {
            console.log(rs.rows.item(i));
        }
    }

    app.selectAllRecords(render);
}
Continue reading "Adding Dynamic Data with the SQLite Plugin"

Populating Cordova SQLite storage with the JQuery API

DataBaseJQuery

For review, you should have the following code in the the onDeviceReady function in www/js/index.js:

    var db = window.sqlitePlugin.openDatabase({name: "test.db"});
    db.executeSql("DROP TABLE IF EXISTS tt");
    db.executeSql("CREATE TABLE tt (data)");

    $.ajax({
      url: 'https://api.github.com/users/litehelpers/repos',
      dataType: 'json',
      success: function(res) {
        console.log('Got AJAX response: ' + JSON.stringify(res));
        $.each(res, function(i, item) {
          $.each(data, function(i, item) {
            console.log('item: ' + JSON.stringify(item));
            tx.executeSql("INSERT INTO tt values (?)", JSON.stringify(item));
          });
        }, function(e) {
          console.log('Transaction error: ' + e.message);
          alert('Transaction error: ' + e.message);
        }, function() {
          db.executeSql('SELECT COUNT(*) FROM tt', [], function(res) {
            console.log('Check SELECT result: ' + JSON.stringify(res.rows.item(0)));
            alert('Transaction finished, check record count: ' + JSON.stringify(res.rows.item(0)));
          });
        });
      },
      error: function(e) {
        console.log('Got ERROR: ' + JSON.stringify(e));
        alert('Got ERROR: ' + JSON.stringify(e));
      }
    });
Continue reading "Populating Cordova SQLite storage with the JQuery API"