LiveCode Forums.

I am experimenting with the sqlite shell via Revolution with some success. I want to select files from a database. The command at the shell gives the desired result.

I am using 2 identical databases testxx & testxx2 so that I know which one I am referring to at what time. I know they are being accessed appropriately.
With trial & error and the use of the constant "quote" I can write the same string. The string in parentheses needs to be in quotes. For example answer shell("sqlite3 testxx2.db .database") works correctly.

The next step is to use the shell command- does not work. The error states "/bin/sh: line 1: sqlite3 testxx2.db select: command not found"
I have tried a variety of things around the * in the select statement, but the error is the same.
Any help most gratefully received. Thank you.

sm,

What are the two subsequent quotes for?

Out of curiousity: why are you using the shell to contact an sqLite server? Why don't you use the sqLite external?

Best,

Mark

Thank you Mark
As I understand it, the line will have to read
shell("sqlite3 testxx2.db "select * from test"")
and the extra quote constants are the only way I can get it to read correctly as a string.
Why am I doing it this way? I came across a good sqlite book and while working in the shell with some examples I thought I would try to do it from within Revolution as I could see that it may help me with a project I am still(!) working on. I need to get data from 4 related tables on one computer and transfer it to another computer for import into a database with an identical structure. This will be done frequently, use subsets of data so that the data on any 2 systems will not be the same etc. This is one part of the project that I have been struggling with for a long time. I have come up with different solutions, but I am unable to determine which is best because of my lack of knowledge in this area- I am not a computer person by training and I get only bits of time to work on things so I often forget where I am up to and how I have actually done something. Exporting (dumping) to a file and then importing/reading the file into a separate table is very easy with the shell and it may work well for what I want. Hence the attempt to get things to work.
Perhaps I am approaching things the hard way- it won't be the first time. I will gladly look at any suggestions as to another or the best way.
Thanks again,

Shane

Hi Shane,

I would figure out how to use the sqLite external. The commands it needs are the same as those you are trying to run from the shell. You just will have to tell the external where to find the files. It will take some reading in the docs. Just search for "datab" (without quotes) in the dictionary.

shell("sqlite3 testxx2.db "select * from test"")

A Revolution shell command starts end ends with a quote. These aren't part of the command but they delimit the string that is parsed by Revolution before it is executed.

So, your command is

sqlite3 testxx2.db "select * from test"

where sqlite3 is the executable, testxx2.db is a parameter containing a reference to a file and the remaining parameter contains a string that can be interpreted and executed by sqlite3.

Now we have to turn this into a string. First, replace the quotes with the constant "quote" (without quotes ;-) )

"sqlite3 testxx2.db" && quote & "select * from test" & quote

If you put this into the message box:

put "sqlite3 testxx2.db" && quote & "select * from test" & quote

you should get the original string, meaning that you did it right.

Now you can put the entire string into a variable and execute it using the shell command:
You could also have used
Note that you may have to replace "testxx2.db" with the complete path to that file.

I agree that using the shell may be easier, but using the external is more efficient. Dumping the data to a file before using it in Rev is very inefficient.

The book you've got still applies, you just execute the queries using revDataFromQuery or revQueryDatabase.

Best,

Mark

Hello Mark and thank you again . Of course your code works. I tried a number of combinations to no avail. Sometimes I start to follow a path and I then cannot see where I am wrong. I'll have to remember to force myself back to the beginning when I am struggling.

Your use of the external is essentially what I have been doing. Can I extend the discussion and ask about importing the data at the other computer? ie the relevant subset of data is gathered and sent to the other computer for importing. If the data is collected using the external as you suggest, then what do you recommend as the best and most efficient way to then insert the data? The second computer will obviously have the same database structure, but at least some of the data will be different. There would be complexities regarding duplicate data etc and I do not expect advice regarding the fine detail, just the general approach. I know that this is not really a question about Revolution, but I would be most interested to know your thoughts if possible.

Shane

Hi Shane,

Why don't you simply copy the db file over from the old to the new computer?

Mark

Hi Mark. I think I am in a completely different time zone hence the delay with my responses.

Because the data will be different at each computer. There will be some common records but it is likely that the majority will be different as the users will be different and the data will come from any number of different computers. It will be necessary to insert the data subsets that have been sent.

Shane