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)
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.
[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
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.
^ 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
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
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?
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.