3 Replies Latest reply on Jan 4, 2007 5:48 AM by James Newton, ACP

    lingo DB SELECT

    pete_484
      Hi, can anyone help me with the SELECT statement I am trying to select data from a database but I cant seem to get the SQL right. This kind of statement works with normal SQL, but maybe its not allowed in this arca Xtra. Can anyone help solve this?

      _global.country1 = "Italy"
      _global.country2 = "France"


      --DB select code
      err = _global.myDB.executeSQL("SELECT * FROM countryInfo WHERE countryName = ? OR countryName = ?",[_global.country1, _global.country2])

      anyone got any tips?
        • 1. lingo DB SELECT
          James Newton, ACP Level 3
          What happens? What do you expect to happen?

          I've successfully performed much more complicated SELECT statements using Arca (such as those below), so I do not think the complexity of your SQL statement is the issue. Your line of code does a number of things all at once. To find out where the error is occurring, you might want to break it up into more lines:

          vDB = _global.myDB -- check that this is a pointer to your Arca database
          vSQL = "SELECT * FROM countryInfo WHERE countryName = ? OR countryName = ?"
          vList = [_global.country1, _global.country2] -- does this contain the data you expect
          vError = vDB.executeSQL(vSQL, vList)

          If only the last line produces an error, then try:

          vDB = _global.myDB -- check that this is a pointer to your Arca database
          vSQL = "SELECT * FROM countryInfo WHERE countryName = 'France' OR countryName = 'Italy'"
          vError = vDB.executeSQL(vSQL)

          If this produces an error, then test this hard-coded SQL statement on its own in the Arca browser. Are the table and field names correct? Are there records with the given values for countryName?


          • 2. Re: lingo DB SELECT
            pete_484 Level 1
            Hi, this code now works...

            vDB = _global.myDB -- check that this is a pointer to your Arca database
            vSQL = "SELECT * FROM countryInfo WHERE countryName = 'France' OR countryName = 'Italy'"
            vError = vDB.executeSQL(vSQL)

            But I want to use variables for the 2 values of countryName. I cannot get the SQL to work because obviously when I try

            _global.country1 = 'Italy'
            _global.country2 = 'France'

            vDB = _global.myDB -- check that this is a pointer to your Arca database
            vSQL = "SELECT * FROM countryInfo WHERE countryName = _global.country1 OR countryName = _global.country2"
            vError = vDB.executeSQL(vSQL)

            lingo does not like the single quotes round the values for the global variables. But the single quotes are needed for the SQL SELECT statement, putting double quotes round the global variable values does not work either. How can I get the SELECT to work using global variable the contain string values?

            thanks.
            • 3. lingo DB SELECT
              James Newton, ACP Level 3
              _global.country1 = "Italy"
              _global.country2 = "France"

              vSQL = "SELECT * FROM countryInfo WHERE countryName = '"&_global.country1&"' OR countryName = '"&_global.country2&"'"
              vError = _global.myDB.execute(vSQL)


              However, the technique of using question marks in the SQL statement and providing a list of string variables should also work. What happens when you try this?

              vDB = _global.myDB
              vSQL = "SELECT * FROM countryInfo WHERE countryName = ? OR countryName = ?"
              vList = [_global.country1, _global.country2]
              vError = vDB.executeSQL(vSQL, vList)