Error connecting to SQLlite in iOS

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Error connecting to SQLlite in iOS

Rick Harrison via use-livecode
Hi All,

I have a simple project that uses an sql lite database. It works fine in LiveCode, but when I test it in the iOS simulator, i get an error.

I have copied to the SQL.lite database to the “Copy Files” in the standalone settings. I have also added the SqLite and MySQL inclusions.

Here is the link to the project and db: Here is a link to the stack and sql db:  https://www.dropbox.com/s/74rhx88dod6cs2k/CCSS%20App.zip?dl=0 <https://www.dropbox.com/s/74rhx88dod6cs2k/CCSS%20App.zip?dl=0>

Here is the script I am using.

local sDatabaseID

command databaseConnect
local tDatabasePath, tDatabaseID

     put specialFolderPath("Documents") &  "/ccssdb-ela.sqlite" into tDatabasePath

## Test if a database exists

if there is a file tDatabasePath then

answer "A database already exists" with "Okay"

end if

if there is a file tDatabasepath then
put  revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
setDatabaseID tDatabaseID

put cd fld "targetGrade" into tGrade

if tGrade is 10 then

put 9 into tGrade

end if

if tGrade is 12 then

put 11 into tGrade

end if

put revQueryDatabase(tDatabaseID,"SELECT * FROM ccss_data WHERE grade =:1","tGrade") into theCursor

if theCursor is an integer then

ConvertSQLCursorToArray theCursor, theDataGridArray

put the result into theError

if theError is empty then

set the dgData of group "ccss_data" to theDataGridArray

end if

revCloseCursor theCursor


revCloseDatabase tDatabaseID

else


Answer "Error connecting to the database:" && tDatabaseID & "."
end if


else

Answer "Having trouble locating database."

end if

end DatabaseConnect

command setDatabaseID pDatabaseID

  put pDatabaseID into sDatabaseID

end setDatabaseID


function getDatabaseID

  return sDatabaseID

end getDatabaseID


command ConvertSQLCursorToArray pCursor, @pOutArrayA

local i

local theFields

local theError


put revDatabaseColumnNames(pCursor) into theFields

if theFields begins with "revdberr," then

put item 2 to -1 of theFields into theError

end if

if theError is empty then

put 0 into i

repeat until revQueryIsAtEnd(pCursor)

add 1 to i


repeat for each item theField in theFields

put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA [i] [theField]

end repeat


revMoveToNextRecord pCursor

end repeat

end if

return theError

end ConvertSQLCursorToArray


Thank you!

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Error connecting to SQLlite in iOS

Rick Harrison via use-livecode
Unless things have changed, when you add files using the copy files pane of
the standalone settings, when sent to ios or android they end up in the
engine folder. (I think?  Or somewhere in that hierarchy)
You would need to check for the existence of the file in documents on app
startup and if it doesn't exist, copy it from engine to where you need it
to be in the documents folder.


   - "engine": The folder containing the built standalone engine (i.e. the
   bundle). This is useful for constructing paths to resources that have
   been copied into thebundle at build time

I don't recall if copied files appear in the toplevel or not, but it
shouldn't be too difficult to find where it is.

Another option would be to put the initial database into a property rather
than from copy files and place it into the documents folder from there.

On Thu, Apr 20, 2017 at 11:37 AM, JOHN PATTEN via use-livecode <
[hidden email]> wrote:

> Hi All,
>
> I have a simple project that uses an sql lite database. It works fine in
> LiveCode, but when I test it in the iOS simulator, i get an error.
>
> I have copied to the SQL.lite database to the “Copy Files” in the
> standalone settings. I have also added the SqLite and MySQL inclusions.
>
> Here is the link to the project and db: Here is a link to the stack and
> sql db:  https://www.dropbox.com/s/74rhx88dod6cs2k/CCSS%20App.zip?dl=0 <
> https://www.dropbox.com/s/74rhx88dod6cs2k/CCSS%20App.zip?dl=0>
>
> Here is the script I am using.
>
> local sDatabaseID
>
> command databaseConnect
> local tDatabasePath, tDatabaseID
>
>      put specialFolderPath("Documents") &  "/ccssdb-ela.sqlite" into
> tDatabasePath
>
> ## Test if a database exists
>
> if there is a file tDatabasePath then
>
> answer "A database already exists" with "Okay"
>
> end if
>
> if there is a file tDatabasepath then
> put  revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
> setDatabaseID tDatabaseID
>
> put cd fld "targetGrade" into tGrade
>
> if tGrade is 10 then
>
> put 9 into tGrade
>
> end if
>
> if tGrade is 12 then
>
> put 11 into tGrade
>
> end if
>
> put revQueryDatabase(tDatabaseID,"SELECT * FROM ccss_data WHERE grade
> =:1","tGrade") into theCursor
>
> if theCursor is an integer then
>
> ConvertSQLCursorToArray theCursor, theDataGridArray
>
> put the result into theError
>
> if theError is empty then
>
> set the dgData of group "ccss_data" to theDataGridArray
>
> end if
>
> revCloseCursor theCursor
>
>
> revCloseDatabase tDatabaseID
>
> else
>
>
> Answer "Error connecting to the database:" && tDatabaseID & "."
> end if
>
>
> else
>
> Answer "Having trouble locating database."
>
> end if
>
> end DatabaseConnect
>
> command setDatabaseID pDatabaseID
>
>   put pDatabaseID into sDatabaseID
>
> end setDatabaseID
>
>
> function getDatabaseID
>
>   return sDatabaseID
>
> end getDatabaseID
>
>
> command ConvertSQLCursorToArray pCursor, @pOutArrayA
>
> local i
>
> local theFields
>
> local theError
>
>
> put revDatabaseColumnNames(pCursor) into theFields
>
> if theFields begins with "revdberr," then
>
> put item 2 to -1 of theFields into theError
>
> end if
>
> if theError is empty then
>
> put 0 into i
>
> repeat until revQueryIsAtEnd(pCursor)
>
> add 1 to i
>
>
> repeat for each item theField in theFields
>
> put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA [i]
> [theField]
>
> end repeat
>
>
> revMoveToNextRecord pCursor
>
> end repeat
>
> end if
>
> return theError
>
> end ConvertSQLCursorToArray
>
>
> Thank you!
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

RE: Error connecting to SQLlite in iOS

Rick Harrison via use-livecode
In reply to this post by Rick Harrison via use-livecode
John,

The standalone "copy files" are in "the engine folder" in the mobile app.
Copy the DB from the engine folder to the documents folder.

Put url ("binfile:"&the engine folder&"/ccssdb-ela.sqlite") into url ("binfile:"&the documents folder&"/ccssdb-ela.sqlite")

You can't open the DB in the engine folder even if you don’t try to write to it as the engine folder is read-only.

Ralph DiMola
IT Director
Evergreen Information Services
[hidden email]


-----Original Message-----
From: use-livecode [mailto:[hidden email]] On Behalf Of JOHN PATTEN via use-livecode
Sent: Thursday, April 20, 2017 1:38 PM
To: How to use LiveCode
Cc: JOHN PATTEN
Subject: Error connecting to SQLlite in iOS

Hi All,

I have a simple project that uses an sql lite database. It works fine in LiveCode, but when I test it in the iOS simulator, i get an error.

I have copied to the SQL.lite database to the “Copy Files” in the standalone settings. I have also added the SqLite and MySQL inclusions.

Here is the link to the project and db: Here is a link to the stack and sql db:  https://www.dropbox.com/s/74rhx88dod6cs2k/CCSS%20App.zip?dl=0 <https://www.dropbox.com/s/74rhx88dod6cs2k/CCSS%20App.zip?dl=0>

Here is the script I am using.

local sDatabaseID

command databaseConnect
local tDatabasePath, tDatabaseID

     put specialFolderPath("Documents") &  "/ccssdb-ela.sqlite" into tDatabasePath

## Test if a database exists

if there is a file tDatabasePath then

answer "A database already exists" with "Okay"

end if

if there is a file tDatabasepath then
put  revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID setDatabaseID tDatabaseID

put cd fld "targetGrade" into tGrade

if tGrade is 10 then

put 9 into tGrade

end if

if tGrade is 12 then

put 11 into tGrade

end if

put revQueryDatabase(tDatabaseID,"SELECT * FROM ccss_data WHERE grade =:1","tGrade") into theCursor

if theCursor is an integer then

ConvertSQLCursorToArray theCursor, theDataGridArray

put the result into theError

if theError is empty then

set the dgData of group "ccss_data" to theDataGridArray

end if

revCloseCursor theCursor


revCloseDatabase tDatabaseID

else


Answer "Error connecting to the database:" && tDatabaseID & "."
end if


else

Answer "Having trouble locating database."

end if

end DatabaseConnect

command setDatabaseID pDatabaseID

  put pDatabaseID into sDatabaseID

end setDatabaseID


function getDatabaseID

  return sDatabaseID

end getDatabaseID


command ConvertSQLCursorToArray pCursor, @pOutArrayA

local i

local theFields

local theError


put revDatabaseColumnNames(pCursor) into theFields

if theFields begins with "revdberr," then

put item 2 to -1 of theFields into theError

end if

if theError is empty then

put 0 into i

repeat until revQueryIsAtEnd(pCursor)

add 1 to i


repeat for each item theField in theFields

put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA [i] [theField]

end repeat


revMoveToNextRecord pCursor

end repeat

end if

return theError

end ConvertSQLCursorToArray


Thank you!

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Error connecting to SQLlite in iOS

Rick Harrison via use-livecode
On 4/20/17 2:05 PM, Ralph DiMola via use-livecode wrote:
> The standalone "copy files" are in "the engine folder" in the mobile app.

Apple changed the rules and files go into specialFolderPath("resources")
now. But LC aliases them to the engine folder for backward compatibility.

--
Jacqueline Landman Gay         |     [hidden email]
HyperActive Software           |     http://www.hyperactivesw.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode