Instr Function In Sqlite

Solar2D General Questions/Discussion
#1

When I try to use INSTR function in my query, I get the error “no such function: instr”.

I have looked SQLite referance and I saw that it has built-in INSTR function.

http://www.sqlite.org/lang_corefunc.html

What is the problem with Corona, is there any solution for this issue?

#2

Hi there,

Could you post some code showing how you’re attempting to call the function?  That would help us debug the issue.  Also, do you have any issues attempting to use any other sqlite functions?

  • Andrew
#3

That’s interesting because if you do a google search on “sqlite instr” you come up with dozens of hits that say it doesn’t exist.   I wonder if it’s been included in a recent update and we may be using an older version with out it?  You can use the lua string function string.find to accomplish the same thing:

http://docs.coronalabs.com/api/library/string/find.html

#4

hello,

You can use the code below.

local sqlite3 = require("sqlite3") local function getResultTable(commandText)     local returnValue = {}     --local db = sqlite3.open("C:\\test.db")     local db = sqlite3.open\_memory()          for row in db:nrows(commandText) do         returnValue[#returnValue + 1] = row.word     end          db:close()          return returnValue end print( "version " .. sqlite3.version() ) --version 3.7.14.1 --print(getResultTable("SELECT instr('test', 't') as word")[1]) --Runtime error no such function: instr print(getResultTable("SELECT length('test') as word")[1]) --4
#5

@Rob Miracle

I can use instr function with SQLite console (version 3.7.15.1). I have googled it a lot and I read lots of message that said there is no native instr function in SQLite but they were long time ago.

I do not think INSTR function only for version 3.7.15 but I’m not sure about that.

Now I’m searching when it was implement in SQLite…

#6

Oh shit…

Release history of SQLite

2012-12-12 (3.7.15)

Added the instr() SQL function.

http://www.sqlite.org/changes.html

Do you know when does Corona update its SQLite version?

#7

I just checked the version on my Mac (Mtn Lion) and it’s 3.7.12.  I’m pretty sure the simulator uses the one built into the OS.  I’ll ask and see what versions we are using.

#8

For iOS we use the version of SQLite that ships with the OS which is older than this feature being added.  On Windows and Android we include  SQLite and are using 3.7.14.1.  Even though this function is in the latest SQLite, it’s not standard SQL and perhaps for portability,  you should find an alternate way to do what you want to do.

#9

Ok thanks, I will use the code below instead…

LIKE '%X%'
#10

Hi there,

Could you post some code showing how you’re attempting to call the function?  That would help us debug the issue.  Also, do you have any issues attempting to use any other sqlite functions?

  • Andrew
#11

That’s interesting because if you do a google search on “sqlite instr” you come up with dozens of hits that say it doesn’t exist.   I wonder if it’s been included in a recent update and we may be using an older version with out it?  You can use the lua string function string.find to accomplish the same thing:

http://docs.coronalabs.com/api/library/string/find.html

#12

hello,

You can use the code below.

local sqlite3 = require("sqlite3") local function getResultTable(commandText)     local returnValue = {}     --local db = sqlite3.open("C:\\test.db")     local db = sqlite3.open\_memory()          for row in db:nrows(commandText) do         returnValue[#returnValue + 1] = row.word     end          db:close()          return returnValue end print( "version " .. sqlite3.version() ) --version 3.7.14.1 --print(getResultTable("SELECT instr('test', 't') as word")[1]) --Runtime error no such function: instr print(getResultTable("SELECT length('test') as word")[1]) --4
#13

@Rob Miracle

I can use instr function with SQLite console (version 3.7.15.1). I have googled it a lot and I read lots of message that said there is no native instr function in SQLite but they were long time ago.

I do not think INSTR function only for version 3.7.15 but I’m not sure about that.

Now I’m searching when it was implement in SQLite…

#14

Oh shit…

Release history of SQLite

2012-12-12 (3.7.15)

Added the instr() SQL function.

http://www.sqlite.org/changes.html

Do you know when does Corona update its SQLite version?

#15

I just checked the version on my Mac (Mtn Lion) and it’s 3.7.12.  I’m pretty sure the simulator uses the one built into the OS.  I’ll ask and see what versions we are using.

#16

For iOS we use the version of SQLite that ships with the OS which is older than this feature being added.  On Windows and Android we include  SQLite and are using 3.7.14.1.  Even though this function is in the latest SQLite, it’s not standard SQL and perhaps for portability,  you should find an alternate way to do what you want to do.

#17

Ok thanks, I will use the code below instead…

LIKE '%X%'