ODBC Filemaker Query

I’ve dealt with this before but its been a few years and cant for the life of me remember the steps that i need to take. I need to query the connected database. the syntax given to me …

SELECT a.“AOS_kprime”, a.“Com22”, a.“VIC”, a.“CFO”
FROM “Company” a

Embed into string in xojo? or re-write? (tried re-writing and SQL works in FM’s script edit, but not in Xojo, returns nil)

sql="-----" …

or what the hell am i thinking.

thanks lol

assuming this is the right path. - http://www.bkeeney.com/smartsql/

It’s funny, SmartSQL comes up every now and then. We haven’t used it in years because I think in the long run it’s better to write your own SQL.

I don’t know much about querying FileMake via ODBC, but I would think the query should be like this:

SELECT a.AOS_kprime, a.Com22, a.VIC, a.CFO
FROM Company a

I’m pretty sure the quotes are unnecessary and can cause issues if your editor makes the ‘smart’ quotes.

If your recordsets are nil then you need to be looking at the database error bit and message to see what the database is telling you. Usually it’s pretty good.

http://documentation.xojo.com/index.php/Database.ErrorMessage

for that matter the “a.” is unneccessary as well… no need to alias a table when there is only one

[quote=271857:@Bob Keeney]It’s funny, SmartSQL comes up every now and then. We haven’t used it in years because I think in the long run it’s better to write your own SQL.
[/quote]
+1

Sure I write my own SQL and then bake it into my own version of SmartSQL.

Thanks guys.

I went ahead and re-wrote it per your statements and ended up with this.

Receives a Nil Exception - Actual error is “Length of Colnames <> Colmodel”

so fu^&ing confusing lol

  • no errors on connecting to DB. odbc says successful.

Did you remove the table alias (a) and replace with the table name (Company)?
Make sure the column names are exactly the same as in filemaker (case). Use Quotes if they contain any spaces.
I query FM with ODBC all the time and have never seen that error. I Googled it and it looks like a jQuery error message?

yup, further handling states its a java script message.

aliasing “works” - but both ways recieve an error.

JAVASCRIPT ERROR

SELECT CompanyID_kprime,CompanyName,CompanyPhone,CompanyUrl FROM Companies
SELECT a.CompanyID_kprime,a.CompanyName,a.CompanyPhone,a.CompanyUrl FROM Companies a

Could this be due to FileMaker maybe having its own external data-source? I’m just barely orienting myself to this file… but then again that would make no sense, I am staring right at the fields in FM14 with my own eyes. they exist in the DB Management window… changing case also doesn’t fix that fact. I still receive a JS error, but at least its not like before where i was getting the FileMaker “?” box.

and this…

SELECT 'companyid_kprime','companyname','companyphone','companyurl' FROM Companies
SELECT 'CompanyID_kprime', 'CompanyName', 'CompanyPhone', 'CompanyUrl' FROM Companies

returns no error, but no values.

tells me the table exists… but its receiving null on col-names? or wouldn’t the lack of columns trigger a NilObject too?

im lost, no shame. -_- O_o

this is me rn. lol

Try a single field first…

Select Companies.companyname From Companies

Your initial query (first post) suggests the table name is “Company”. Your last post suggests “Companies” ??

^ first post was me posting example of issue off the top of my head 19hours ago. last posts are the issue (: - single column returns records. so we’re doing something right here. its just syntax for sure now

NVM - Placing the statement

SELECT CompanyID_kprime , CompanyName , CompanyPhone , CompanyUrl FROM Companies

(apparently you cant have the commas touching the colnames)

directly in FileMaker script box returns 4,999 rows. but they are ALL blank. no data.

Place it into XOJO, returns a NilObjectException -_-

What is the actual code you are using in Xojo?

Dim sql as string = “SELECT CompanyID_kprime , CompanyName , CompanyPhone , CompanyUrl FROM Companies”

Dim rs as recordset = yourODBCDatabaseObject.SQLSelect(sql)

if yourODBCDatabaseObject.error then

msgbox yourODBCDatabaseObject.errormessage

end if

if rs <> nil and not rs.eof then

// process recordset

end if

[code] dim sql as string
dim rs as RecordSet

sql="SELECT CompanyID_kprime , CompanyName FROM Companies"
rs=FMPDB.SQLSelect(sql)
If FMPDB.Error Then MsgBox FMPDB.ErrorMessage
MsgBox str(rs.RecordCount())
rs.Close[/code] 

returns nilobj

but return records in FM using this same statement now. the FROM having to be on a separate line. (syntax)

SELECT CompanyID_kprime, CompanyName FROM Companies

so i tried doing it in xojo, cause i was curious.

[code] dim sql as string
dim rs as RecordSet

sql="SELECT CompanyID_kprime , CompanyName" + EndOfLine + "FROM Companies"
rs=FMPDB.SQLSelect(sql)
If FMPDB.Error Then MsgBox FMPDB.ErrorMessage
MsgBox str(rs.RecordCount())
rs.Close

[/code]

still nadda. nilobj

It has to be something simple but I’m at a loss at the moment. You said the single column returned records? What are the field types you are querying in FM?

well, its returning in FM. nothing in xojo - justa nilobj. im at a loss too. -_-

Resolved.

We started this discussion with obvious syntax confusion. We worked through it. Thanks to Peter, Bob & Dave - I was able to get things straight.

Proper syntax is

SELECT Company_Name, Company_Type FROM Company

no EOL’s / No aliasing required & the statement for the above smart-sql is true. However I had to resolve the login again (user/pass) - Thank you guys for the time, that shit was confusing but at least now I know how to properly navigate these waters.