Phonegap: SQLite issue

Question

I have a problem with a SQLite query. My database has a table names "tipo", it contains 3 columns: code, categorie and sousCategorie.

I fill this table by using a CSV file (CODE_LIT = code, All = sousCategorie, General = categorie): CSVFile

My problem is when I want to get the sousCategorie of "Coraux" for exemple. I use this query:

this.getSousCategorie = function(categorie){
        this.db.transaction(function(tx){
            tx.executeSql("SELECT distinct sousCategorie,categorie FROM tipo where categorie = 'Coraux' ", [], function(tx,results){
                var element = document.getElementById('sousCategorie');
                var s = "";
                for (var i = 0; i < results.rows.length; i++) {
                    s+="<option>"+results.rows.item(i).sousCategorie+";;;"+results.rows.item(i).categorie+"</option>";
                }
                element.innerHTML = s;
            }, this.errorCB);
        },this.errorCB);
    }

I don't have any results. But if I change the WHERE of this query by code='AA', it gives me the right result:

SELECT distinct sousCategorie,categorie FROM tipo where code = 'AA'

I don't understand why this query works with every columns except categorie. I hope someone will help me, thank you very much

EDIT: Here's the functions that I used to import the CSV file in my database

function importCategories(){
    ReadFileFromWithInProject("test.csv");
}
function ReadFileFromWithInProject(filename){
            
            var path = "file:///var/mobile/Containers/Data/Application/D0D4318C-7843-420A-9289-B34FA10F0DA8/Library/NoCloud/phonegapdevapp/"
            window.resolveLocalFileSystemURL(path, function(fs){
                fs.getFile("www/"+filename,null,function(fileEntry){
                    readFile(fileEntry);
                },function(e){
                    alert(e);
                });
            },function(e){
                alert(e);
            });
        }


        function readFile(fileEntry){
            fileEntry.file(function(file){
                var reader = new FileReader();
                reader.onloadend = function(){
                    PaintValues(this.result);
                };

                reader.readAsText(file);
            });
        }

        function PaintValues(strData){
            var splitArray = strData.split("\n");
            var strPaintData = "";
            var BdDonnees = new BdD();
            alert(splitArray.length-1);
            for (var i = 1; i < splitArray.length-1; i++) {
                var rowSplit = splitArray[i].split(",");
                BdDonnees.newTipo(rowSplit); //add to the Database
            }
            BdDonnees.getCategories();
        }

And here's my database:

function BdD(){
 this.db= window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
this.setupTable = function(){
  this.db.transaction(function(tx){
   tx.executeSql('DROP TABLE IF EXISTS entrees');
   tx.executeSql("CREATE TABLE IF NOT EXISTS entrees(id INTEGER PRIMARY KEY,photo,latitude,longitude,code,date)");
   tx.executeSql('DROP TABLE IF EXISTS tipo');
   tx.executeSql("CREATE TABLE IF NOT EXISTS tipo(id INTEGER PRIMARY KEY,code ,categorie ,sousCategorie )");
   alert("Reset réalisé")
  },this.errorCB);
  
 }

this.newTipo = function(tipo){
  this.db.transaction(function(tx){
    tx.executeSql('INSERT INTO tipo (code,categorie,sousCategorie) VALUES (?,?,?)',[tipo[0],tipo[2],tipo[1]]);
   },this.errorCB);
 }
this.getCategories = function(){
  this.db.transaction(function(tx){
   tx.executeSql('SELECT distinct categorie FROM tipo  ORDER BY categorie', [], function(tx,results){
    var element = document.getElementById('categorie');
    var s = '<option value="" selected disabled hidden>Choisissez une catégorie</option>';
    for (var i = 0; i < results.rows.length; i++) {
     s+="<option>"+results.rows.item(i).categorie+"</option>";
    } 
    element.innerHTML = s;
   }, this.errorCB);
  },this.errorCB);
 }

I'd guess your CSV files uses wrong end-of-line characters, which end up in the database. — CL., Nov 02 '17 at 13:48
Hi, you're right this is the problem, but I don't know how to remove the end (I think it's the \n) — Couldosh, Nov 03 '17 at 04:14
@CL To import the file in my database I use the functions that I wrote in my edit — Couldosh, Nov 05 '17 at 21:16

score 0 · Answer 1 · answered Nov 06 '17 at 07:39

0

Notepad generates Windows line endings, which are not valid in any Unix-y operating system.

Use a better editor, or run some tool to convert the file.

answered Nov 06 '17 at 07:39

CL.

173,858
17
217
259

Phonegap: SQLite issue

1 Answers1