ODBC database does nothing...also doesn't return erros

[edgore]

I am trying to query a database through an ODBC connection and I am continuing to have no luck.

I know I can connect to the database, and I have the right port info and version of the connector, because I am able to connect using tools other than Livecode (I can browse the database with QTODBC 6.1, for example - more about that later)

I am creating my connection to the database and getting a databse ID back, without any error.

I then try to do the following:

Code: Select all

 put revDataFromQuery(tab, return, gSCDBID, the text of field "query") into field "testing"

and the following as the contents of field "query"

Code: Select all

SELECT * FROM cm3rm1 where number="CH1094900"

I have also tried

Code: Select all

SELECT * FROM cm3rm1

just to see what it would give me.

Both return nothing, not even an error.

The name of the table is correct (cm3rm1), and I know there should be a single record where number contains CH1094900

However, I am seeing lots of weirdness when I browse the database using QTODBC 6.1 - for example if I have it show me the contents of number for a single record I see "CH" If I ask for it to list the contents of number for all records in the database I get results like

CH
H1
10
09
94
49
90
00

Like what should be a single field in a single record is spread across several rcords.

Clearly there is something strange going on. I wanted to validate that what I am doing in Livecode seems right though, since I have never tried writing my own SQL before and using the Livecode commands - used the query builder the one time I needed to do something like this years ago (with a different database)

Thanks for any help.

[bangkok]

and the following as the contents of field "query"

Code: Select all

SELECT * FROM cm3rm1 where number="CH1094900"

It can't work... because of the quotes.


You have to use single quotes.

Code: Select all

SELECT * FROM cm3rm1 where number='CH1094900'

In any case, you need to post your whole code, so we can help you, not just parts of it.

[edgore]

I have actually tried it both ways - single quotes and double quotes. Same (lack of) results with both and no error message. Good catch though - I should have posted the first try, which had single quotes.

[edgore]

And here is all the code - things are a bit spread out at the moment since I am just trying to test things out before I actually start development

The database connect button:

Code: Select all

global gSCDBID
on mouseUp
   repeat forever
      ask "Enter your Service Center User ID:" titled "Service Center Log In"
      put the toUpper of it into tUserName
      ask password clear "Enter your Service Center Password:" titled "Service Center Log In"
      put it into tUserPass
      get revOpenDatabase("odbc", "sc_report_odbc", , tUserName, tUserPass, "emulated static")
      if it is a number then
         put it into gSCDBID
         exit repeat
      end if
      answer "Your Log In Was Not Accepted" with "Try Again" or "Quit"
      if it is "Quit" then quit
   end repeat
   put empty into tUserName
   put empty into tUserPass
   put "Connected to Service Center Database" into field "statusField"
end mouseUp

Button to test the query in field "query":

Code: Select all

global gUserName, gUserPass, gSCDBID

on mouseup
   put revDataFromQuery(tab, return, gSCDBID, the text of field "query") into field "testing"
end mouseup

Contents of field "Query"

Code: Select all

SELECT * FROM cm3rm1 where number='CH1094900'

[bangkok]

get revOpenDatabase("odbc", "sc_report_odbc", , gUserName, gUserPass, "emulated static")

OK I think you have a problem here.

You forgot to put the name of the DB. And the type of cursor might create a problem.

Try

Code: Select all

      put  revOpenDatabase("odbc", "sc_report_odbc",gDatabaseName,gUserName,gUserPass) into gSCDBID
if gSCDBID is not  a number then
answer "Error : "&gSCDBID 
exit to top
end if

etc.

[edgore]

For ODBC databases the database name seems to be optional. From the online help:

Code: Select all

get revOpenDatabase("odbc", "BizFile", , "jenkins" ,the dbPassword of me, "emulated static")

As far as I know this particular ODBC backend has only a single database - only one is listed in the other tools I am using to browse it) and it doesn't seem to have a name - there is a server name, which is "10" (seems to be the first two digits of the IP address of the server) and a DBMS name, which is "P4 02.00.0009"

I will try experimenting with the cursor and variations on tying different things as the name and see what I get.

Thanks for you help on this.

[edgore]

After experimenting with the driver, it looks like there is a single database in the backend, which is named "P4". Using that name:

Code: Select all

get revOpenDatabase("odbc", "sc_report_odbc", "P4", gUserName, gUserPass, "emulated static")

gives me the same result though - I get a successful connection and a database ID, but querys, revDatabaseTableNames(gSCDBID), etc return no data and no error code

I will experiment with cursors and see if I get anything

[edgore]

Well, I have some progress. While things are still not working in LiveCode, I found another tool for just doing straight SQL queries against an ODBC database which does not give me the malformed responses I was getting before. In this one I can do a qury and a get back the data I expect. So, I can test my SQL at least.

This makes me think that there is definitely something wrong with either how I am connecting to the DB in livecode (seems likely since nothing works, even though I get a successful connection response) or with how I am doing my queries (possible, since I am terrible at this, but unlikely since even the Livecode native DB stuff like getting the table names doesn't work).

[edgore]

Cursor! That was it. If I specifically set it to forward only (rather than allowing it to default, or using anything else) it appears to work!

[bn]

Hi,

also doesn't return erros

I think the spelling is "Eros"

http://en.wikipedia.org/wiki/Eros

(could not resist )
Glad you got it working

Kind regards
Bernd

[edgore]

I'm not giving up until it returns eros...

[ShaneK]

Hi Edgore:

I think I am having the same problem as you were. database does nothing, no errors, connection good.

You posted "Cursor! That was it. If I specifically set it to forward only (rather than allowing it to default, or using anything else) it appears to work!"

How do you do this? Can you post the code the worked for you... It may solve my problem too. Thanks!

Update -- I have my code working so I do not need the answer here. However someone else may...