search

AlaSQL / alasql

AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.
http://alasql.org
MIT License
7.01k stars 653 forks source link

error with LIKE #592

Closed lgodard closed 8 years ago

lgodard commented 8 years ago

Hi using LIKE statement in a WHERE clause, i always experience an error, not the same depending on alasql version is it something known ? feel free to ask if more information needed Laurent

alasql 0.2.1 : error .search is not a function

SELECT CompteNum FROM ?  WHERE CompteNum LIKE "4%";
execute_requete.js:518 Does the file exists? there was an error: TypeError: (value || "").search is not a function
    at Object.utils.like (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:3911:24)
    at eval [as wherefn] (eval at <anonymous> (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:8273:11), <anonymous>:3:28)
    at doJoin (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6752:12)
    at doJoin (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6845:8)
    at queryfn3 (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6302:2)
    at queryfn2 (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6274:9)
    at Object.eval [as datafn] (eval at <anonymous> (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:7757:20), <anonymous>:3:57)
    at /home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6223:19
    at Array.forEach (native)
    at queryfn (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6219:16)
    at statement (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:7352:14)
    at Function.alasql.dexec (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:4240:27)
    at Function.alasql.exec (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:4190:17)
    at alasql (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:121:18)
    at /home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:4535:10
    at lib$es6$promise$$internal$$initializePromise (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/node_modules/es6-promise/dist/es6-promise.js:373:9)

alasql 0.2.3 : error .toUpperCase is not a function

SELECT CompteNum FROM ?  WHERE CompteNum LIKE "4%";
execute_requete.js:518 Does the file exists? there was an error: TypeError: (value || "").toUpperCase is not a function
    at Object.utils.like (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:3837:24)
    at eval [as wherefn] (eval at <anonymous> (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:7595:11), <anonymous>:3:28)
    at doJoin (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6313:12)
    at doJoin (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6385:8)
    at queryfn3 (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:5874:2)
    at queryfn2 (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:5849:9)
    at Object.eval [as datafn] (eval at <anonymous> (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:7183:20), <anonymous>:3:57)
    at /home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:5800:19
    at Array.forEach (native)
    at queryfn (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:5797:16)
    at statement (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:6819:14)
    at Function.alasql.dexec (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:4160:27)
    at Function.alasql.exec (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:4111:17)
    at alasql (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:120:18)
    at /home/lgodard/projets/nodejs/fec/src/node_modules/alasql/dist/alasql.js:4299:9
    at lib$es6$promise$$internal$$initializePromise (/home/lgodard/projets/nodejs/fec/src/node_modules/alasql/node_modules/es6-promise/dist/es6-promise.js:373:
mathiasrw commented 8 years ago

Interesting. It should work.

Could you please provide the code you use to create your SQL and interact with the alasql object?

lgodard commented 8 years ago

Hi

thanks for your answer !

here are some information update: as always, asking gives some ideas : would it be due to CompteNum being a number ?

feel free to ask for more if needed

Laurent

   const moteur_sql = require('alasql');
   sql = 'SELECT CompteNum FROM ? WHERE CompteNum LIKE "4%";';

    moteur_sql.promise(sql, [data])
          .then(function(res) {
              resultat_requete = res;
              return;
          }).catch(function(err) {
              console.log(sql);
              console.log('Does the file exists? there was an error:', err);
          });
}
data = [
{ JournalCode: 'AA',
    JournalLib: 'A AAAA',
    EcritureNum: 0,
    EcritureDate: 20140701,
    CompteNum: 411,
    CompteLib: 'client',
    CompAuxNum: 'CCCC',
    CompAuxLib: 'Clients',
    PieceRef: 'CB',
    PieceDate: 20140701,
    EcritureLib: 'VIREMENT',
    Debit: 0,
    Credit: 917.75,
    EcritureLet: '',
    DateLet: '',
    ValidDate: 20150630,
    Montantdevise: 0,
    Idevise: '' },
  { JournalCode: 'AA',
    JournalLib: 'A AAA',
    EcritureNum: 0,
    EcritureDate: 20140701,
    CompteNum: 411,
    CompteLib: ' client',
    CompAuxNum: 'ZZz',
    CompAuxLib: 'divers',
    PieceRef: 'REM CB',
    PieceDate: 20140701,
    EcritureLib: 'VIREMENT',
    Debit: 0,
    Credit: 634.8,
    EcritureLet: '',
    DateLet: '',
    ValidDate: 20150630,
    Montantdevise: 0,
    Idevise: '' },
  { JournalCode: 'AA',
    JournalLib: 'A AAA',
    EcritureNum: 0,
    EcritureDate: 20140701,
    CompteNum: 411,
    CompteLib: 'client',
    CompAuxNum: 'QQQ',
    CompAuxLib: 'divers',
    PieceRef: '000025',
    PieceDate: 20140701,
    EcritureLib: ' RECU',
    Debit: 0,
    Credit: 1398.6,
    EcritureLet: '',
    DateLet: '',
    ValidDate: 20150630,
    Montantdevise: 0,
    Idevise: '' }
]
mathiasrw commented 8 years ago

Yes - LIKE only works on string now.

@agershun - Can you think of any problem supporting LIKE on numbers by replacing

return (value||'').toUpperCase().search(RegExp(s.toUpperCase()))>-1;

with

return (''+value).toUpperCase().search(RegExp(s.toUpperCase()))>-1;

in

https://github.com/agershun/alasql/blob/c1f9d00aea9b021ecf25a0e753af7e0921778ca4/src/15utility.js#L960

and

https://github.com/agershun/alasql/blob/c1f9d00aea9b021ecf25a0e753af7e0921778ca4/src/15utility.js#L991

agershun commented 8 years ago

The line should be:

return (''+(value||'')).toUpperCase().search(RegExp(s.toUpperCase()))>-1;
mathiasrw commented 8 years ago

@lgodard Please try again with last build: https://github.com/agershun/alasql/blob/develop/dist/alasql.js

lgodard commented 8 years ago

Hi guys, You rock ! It works like a charm testing on last master checkout I attach here the unit test i used Thanks a lot !! Laurent test432.js.txt

agershun commented 8 years ago

:+1: