DataBase Cursor

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: DataBase Cursor

Post by Klaus » Wed Jun 17, 2015 4:42 pm

Hi folks,

creating some functions like these is a MUST when using database scripts:

Code: Select all

## Double quotes "
function q tString
   return QUOTE & tString & QUOTE
end q

## Single quotes '
function q2 tString
   return "'" & tString & "'"
end q
You get the picture! :D


Best

Klaus

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataBase Cursor

Post by phaworth » Wed Jun 17, 2015 8:45 pm

What flavor of SQL are you using - sqlite, mySQL, etc? It makes a difference to what characters you can use around table and column names.

The SQL standard is that SQL entities like table names and column names are enclosed in double quotes and text strings are enclosed in single quotes. Some implementations will recognize other characters like square brackets and some will also recognize double quotes around entity names depending on the context but best to stick with the SQL standard whenever possible.

The code posted by MaxV should work and also adheres to the SQL standard.

Pete

mcbroh
Posts: 15
Joined: Wed Nov 19, 2014 5:05 pm
Location: Sweden

Re: DataBase Cursor

Post by mcbroh » Mon Aug 03, 2015 4:45 pm

hello,

i want to make a data grid form which should get get multiple datas from database and set them in a form. i tried a lesson on runrev website but keep getting a function error.
I would be glad if someone could look into the script an help me out.

on mouseUp
## Connect to the database
put "mysql" into theDBType
put "www.runrev.com" into theDBHost
put "runrev_test" into theDBName
put "runrev_example" into theDBUser
put "example" into theDBPassword
put revOpenDatabase( theDBType, theDBHost, theDBName, theDBUser, theDBPassword ) into theConnectionID

if theConnectionID is an integer then
## Query the database for data
put revQueryDatabase( theConnectionID, "SELECT * FROM Table1") into theCursor

if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
put the result into theError

if theError is empty then
## The cursor was successfully converted to an array.
## Assign it to the data grid. The 'firstname' and 'lastname' columns
## from the database cursor will appear in the matching columns
## in the data grid.
set the dgData of group "DataGrid 1" to theDataGridArray
end if

## Close the database cursor
revCloseCursor theCursor
end if

## Close the database connection
revCloseDatabase theConnectionID
else
answer "Error connecting to the database:" && theConnectionID & "."
end if
end mouseUp
command ConvertSQLCursorToArray pCursor, @pOutArrayA
local i
local theFields
local theError

## Get the names of all the columns in the database cursor
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
## Loop through all rows in cursor
repeat until revQueryIsAtEnd(pCursor) ########## error function error revQueryisAtEnd
add 1 to i

## Move all fields in row into next dimension of the array
repeat for each item theField in theFields
put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[ theField ]
end repeat

revMoveToNextRecord pCursor
end repeat
end if

return theError
end ConvertSQLCursorToArray

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataBase Cursor

Post by phaworth » Mon Aug 03, 2015 5:55 pm

Do you get the error the first time through the repeat loop or later? Even if there was no error, you will miss processing the last record. revQueryIsAtEnd returns true when the current record in the cursor is the last one so your revMoveToNextRecord will eventually get to the last record then your repeat loop will exit before processing it.

I usually use something like "repeat revNumberOfRecords(tCursor) times" when reading through a cursor. I also like to revMoveToFirstRecord before the loop, not sure if it's strictly necessary but rather be safe than sorry.

Pete

mcbroh
Posts: 15
Joined: Wed Nov 19, 2014 5:05 pm
Location: Sweden

Re: DataBase Cursor

Post by mcbroh » Tue Aug 04, 2015 7:42 am

Hi Pete,

Thanks aload, so far so good. the records are displayed on the data grid as they should. just trying to figure out how to get them into a form now.

Regards

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: DataBase Cursor

Post by Klaus » Tue Aug 04, 2015 3:48 pm

Hi mcbroh,
mcbroh wrote:the records are displayed on the data grid as they should. just trying to figure out how to get them into a form now.
I thought your datagrid IS of type FORM? So setting its "dgdata" should do the trick, not?
What exactly does not work yet? Or do you mean something different with "form"?


Best

Klaus

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataBase Cursor

Post by phaworth » Tue Aug 04, 2015 3:54 pm

You have to use the FillInData handler of the row behavior script to load the controls in a row from the array which is a parameter to to the handler. Check out the datagrid manual for an example.
Pete

mcbroh
Posts: 15
Joined: Wed Nov 19, 2014 5:05 pm
Location: Sweden

Re: DataBase Cursor

Post by mcbroh » Wed Aug 05, 2015 2:12 pm

Hi Klaus and Pete,

The problem with the data grid form is

i am trying to display 3 record(Columns) from dbCursor but then the data grid becomes blank. when i turn one of the record to a comment, the two record appear on the data grid.

set the text of field "joketext" of me to pDataArray["joketext"]
set the text of field "jokedate" of me to pDataArray["jokedate"]
--set the text of field "jokeid" of me to pDataArray["jokeid"] ## now marked as comment, then data grid form shows.
end FillInData


on LayoutControl pControlRect
local theFieldRect

-- This message is sent when you should layout your template's controls.
-- This is where you resize the 'Background' graphic, resize fields and
-- position objects.
-- For fixed height data grid forms you can use items 1 through 4 of pControlRect as
-- boundaries for laying out your controls.
-- For variable height data grid forms you can use items 1 through 3 of pControlRect as
-- boundaries, expanding the height of your control as needed.

-- Example:
put the rect of field "joketext" of me into theFieldRect
put item 1 of pControlRect -5 into item 1 of theFieldRect
set the rect of field "joketext" of me to theFieldRect

set the rect of graphic "Background" of me to pControlRect
end LayoutControl

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: DataBase Cursor

Post by phaworth » Wed Aug 05, 2015 10:45 pm

Check the contents of pdataarray["jokeid"] to make sure it doesn't have any non-ascii or other strange characters in it.

mcbroh
Posts: 15
Joined: Wed Nov 19, 2014 5:05 pm
Location: Sweden

Re: DataBase Cursor

Post by mcbroh » Thu Aug 06, 2015 6:41 pm

Thanks... got it fixed now :)

ipadzz
Posts: 3
Joined: Thu Nov 12, 2015 9:35 am
Location: Island of Gran Canaria, Canary Islands
Contact:

Re: DataBase Cursor

Post by ipadzz » Tue Jan 05, 2016 2:11 pm

Thanks to all of you in this topic. I have working for hours trying to work out the QUOTE function and yours works fantastic for me. THANKS.

Post Reply

Return to “Databases”