ODBC access

[Sarah Reichelt]

>  > On Sun, Mar 23, 2008 at 6:10 PM, Harry <pub at ...> wrote:
>  > > I would like to use ODBC but cannot figure out how connect to the database
>  > >  or how to extract records. This is the code I'm attempting;
>  > >
>  > >  put revOpenDatabase("ODBC","",testdb,"","") into myDB
>  > >  revQueryDatabase(myDB,"SELECT * FROM Item","tItemList")
>  > >  put tItemList into field "Item List"
>  >
>  > Hi Harry,
>  >
>  > I have bever used ODBC so I can't comment on your database open script
>  > or the permission problems.
>  >
>  > However, the 2nd line above is incorrect. Try this:
>  >    put revQueryDatabase(myDB,"SELECT * FROM Item") into tItemList
>  >
>  > As revQueryDatabase is a function, you must tell it where to put the
>  > result. Otherwise, Rev thinks it is a handler, which gives you the
>  > "can't find handler" error.
>  >
>  > Cheers,
>  > Sarah
>
>
>  Thanks Sarah that was a useful tip. Seems there are many errors and typo's in
>  the Revolution documentation. After much (unnecessary!) experimentation I've
>  arrived at code that does the job. No doubt less that optimal so I'm not
>  thinking kind thoughts about the documentation.
>
>  For future reference here is ODBC code that works;
>
>  on mouseUp
>   put revOpenDatabase("ODBC","testdb",,,) into myDB
>   put revQueryDatabase(myDB,"SELECT * FROM Item") into tRecordSetId
>   repeat until revCurrentRecordIsLast(tRecordSetId)
>    put revDatabaseColumnNamed(tRecordSetId,"Item Id") into tItemId
>    if the result contains "revdberr" then exit repeat
>    put revDatabaseColumnNamed(tRecordSetId,"Item Title") into tItemTitle
>    if the result contains "revdberr" then exit repeat
>    put tItemId & tab & tItemTitle & return after tItemList
>    revMoveToNextRecord tRecordSetId
>    if the result contains "revdberr" then exit repeat
>   end repeat
>   put tItemList into field "Item List"
>  end mouseUp
>

Check out the docs for the revDataFromQuery function which I think
will do what you want in a lot less code.

I think your script can be condensed to:

put revOpenDatabase("ODBC","testdb",,,) into myDB
put "SELECT Item Id, Item Title from Item" into tSQL
revDataFromQuery(tab,return,myDB,tSQL) into fld "Item List"

Cheers,
Sarah