Filemaker ODBC & PHP - no data in varchar

Hey Spiceheads.

Need help trying to pull ODBC data into a php app we're building.

We're running into the issue where the varchar(1000000) is displaying empty results.  We're able to successfully pull other fields like decimal and timestamps.

I read that there is a work around by checking the "Describe text fields at long varchar" option, but we still cant get any data to display.

We've also tried to change the max characters allowed in the database to 10 and that still doesn't do it.

I'm assuming it's something simple.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Have you tried defining the fields as "text" as shown on page 58 of this reference?
CruizectrlAuthor Commented:
Hi Ray,

The problem fields are text fields.  We're using a query tool to play around with and by default it is a varchar (1000000, NULL) which is expected and doesn't read (I'm guessing because of a field limit in php.

We've changed it to varchar (10, NOT NULL) and still nothing.

Decimals and timestamps work fine (we're currently using decimals to pass data and case to display a "STATUS" field.).

We know that the query runs fine because we able to use a WHERE statement on a varchar column.
Ray PaseurCommented:
I don't think PHP "knows" anything about field definitions in a database -- it's just a pipe for information to and from the DB server.  Unfortunately, I can't do much more than read the docs; I don't have filemaker available to me any more.  Suggest you leave the question open for a while and let's see if someone else can come up with an idea.
Andrew AngellCo-Owner / DeveloperCommented:
Are you working with FM Pro only, or do you have FM Server configured?  If you have FM Server you should use the PHP API instead of ODBC.  I remember running into lots of problems with ODBC like you're explaining here, but with the PHP API those problems don't happen.

Another option (which FileMaker actually recommends over anything else) is to use a MySQL database with all of your PHP apps and then bind that database to FileMaker via External SQL Sources.

In most cases people use that to get data from a web app into FileMaker, but in your case it sounds like you're trying to pull data FileMaker into the PHP app.  As such, you could write scripts in FM to copy data from FM layouts to MySQL layouts.  This effectively gets the data into the MySQL database, and then you can hit MySQL from PHP and you won't have any issues at all.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CruizectrlAuthor Commented:
Thanks Andrew.

I didn't realize how much of a pain it would be.

We've actually had some success's with using CAST on all our varchar columns.  I just started reading through on CWP and that sounds like another viable options too.  I kind steered past it because IWP  was not very good.  We'll take a look at the MYSQL option as well since all of our other data pulls from this source without a hitch.

Thanks for your feedback.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.