How to get data from a record pointed by a cursor?

[greg falda]

Assume I have a database cursor and I succesfully move to a next record issuing

Code: Select all

revMoveToNextRecord theCursor

i.e. the cursor points now to a record. Assume that the record has the value "John" in the column called first_name. I simply want to get this value using the cursor. I want to put the value into my variable theName, so that the code

Code: Select all

answer "The name is" && theName

will display a popup window with the text: "The name is John"
Please provide the missing code to put the value from the record pointed by the cursor into the variable.

Best
greg falda

[richmond62]

I have a database cursor

Well, I'll start off by telling you I don't know what you mean.

That's a Table Field

Just been reading a lovely book of Irish Folk Tales; hence Oenghus.

That's the script in the Table Field

CHEEKY.livecode.zip

Here's the stack.

(1.31 KiB) Downloaded 218 times

You can probably do the same sort of thing with a Data Grid . . . .

[greg falda]

Dear richmond62

Thank you for your response. Now I see that I should clarify my request.

1) First of all: My problem is not connected with any data grids. In fact it is not connected with any UI. My code should explicitely create the cursor. In fact, I have already created the corresponing function

Code: Select all

function dbQueryToCursor pConnectionID, pQuery
   local tCursor
   put revQueryDatabase( pConnectionID, pQuery ) into tCursor 
   if tCursor is an integer then
   else
      answer error "The cursor has not been created from query - message: " & tCursor & "..."
      answer error "The cursor has not been created from query - the result: " & the result & "..."
      answer error "The query was: " & pQuery
   end if
   return tCursor
end dbQueryToCursor

2) In general, I need to write what is sometimes called a job. It should be accomplished by code started by a pushbutton. The job operates on a couple of tables, does some computations and writes some data to some tables. An example could be a routine computing salary for all emplyees of a company according to some data stored in a couple of tables. It does not interact with the user.

3) I've tried to use SQLite for this. But I found out that SQLite lacks many features needed for this including:

a) variables (like ms sql declare @var ...)
http://stackoverflow.com/questions/7739 ... and-use-it
b) stored procedures (like ms sql exec dbo.Procedure ...)
http://stackoverflow.com/questions/3402 ... procedures
c) loops like while, for
http://stackoverflow.com/questions/1788 ... port-loops

5) Currently I'm trying to use the above cursors for SQLite.

MY NEW REQUEST: It would be helpful if I would know how to do the following using the described cursors:

I have a SQLite table People with following rows: FirstName, LastName and FullName. Assume that the first and last names are filled and the full names are empty. The job is to fill the full names.
I employ the cursor as above to loop through all the names. The code should:

1) For each record:

1a) read the first and last name and display a message like: "The full name is John Smith" using LiveCode answer keyword i.e.:

Code: Select all

answer "The full name is" && tFirst && tLast

(in fact I need this in order to have a place in code in which I can for instance find some data in other tables or so)

1b) Fill the Full name in the database with (in this case): "John Smith"

2) Finally, I would like to get the SQLite table with the entire FullName column filled.

Providing an imperative SQLite query for this is not the solution for me due to the SQLite limitation mentioned above. I need LiveCode (as a programming language with cursors) and SQLite to provide the queries for cursors.

Finally: If the LiveCode database cursors won't do the job, please state this. Then I would rather write the job for a server employing MySQL, MS-SQL or so.

Thanks in advance for any help
Best
greg falda

[AxWald]

Hi,

seems you want to lookup "revDatabaseColumnNamed" in the dictionary.
There may be more of this flavor (with cursor/ "recordset"), I don't know. Even if I do a lot of database work with LC (nearly all of my stacks connect to dbs) I don't trust these.

Besides revdb_connect/ _disconnect & revdb_querylist/ _execute I don't use any functions of the db library. But these do all that I want/ need - without having to have another layer between me & the database engine.

For your example I'd do (caution, untested!):

1a) read the first and last name and display a message:

Code: Select all

   put "SELECT first_Name, last_Name FROM personal where active = 1;" into StrSQL
   put revdb_querylist(tab,return,MyDBID,StrSQL) into myList
   set itemdel to tab
   put 1 into myCnt
   repeat for each line myLine in myList
      answer "The full name in record " & myCnt & " is: " & \
            item 1 of myLine && item 2 of myLine & "."
      add 1 to myCnt
   end repeat

1b) Fill the Full name in the database with (in this case): "John Smith"

Code: Select all

   put "REPLACE INTO personal (full_Name) VALUES " & CR into StrSQL
   replace comma with space in myList
   put "(" before myList
   put ");" after myList
   get revdb_execute(myDBID,(StrSQL & myList))

2) Finally, I would like to get the SQLite table with the entire FullName column filled.

Code: Select all

   put "SELECT * FROM personal where active = 1;" into StrSQL
   put revdb_querylist(tab,return,MyDBID,StrSQL) into myList

You see, I always construct a full SQL statement & send this to the db engine. This way it's very easy to write/ debug: Just try the statement in SQLite manager/ HeidiSQL/ Access, whatever. Or construct it there, and transfer it to LC. Quite easy.

Hope this helps, have fun!

[greg falda]

Dear AxWald

Yes! You're right: I needed

Code: Select all

revDatabaseColumnNamed(tCursor,"fieldname")

By currently, no good experience. When I loop with the cursor, then - within the loop - the test code

Code: Select all

   answer "revDatabaseColumnNamed(tCursor,'fieldname') = " & revDatabaseColumnNamed(tCursor,"fieldname")

simply does not display proper values! The number of records to loop on is OK, but the values are not always from the record pointed, but sometimes from a record which is "close" to the pointed record.

Thank you for your remark about the distrust to the DB library. The "job" I want to create is rather sophisticated and I'll rather implement it for the server using pure SQL - with an SQL dialect enabling more for cursors (as I wrote before): possibly MS SQL or MySQL and then connect to it via w WebService.

Any further comments or ideas are highly welcome.

Thanks a lot!
greg falda