In the ODBC guide is this gem:
You can retrieve binary data, file reference information, or data of a specific file type from a container field.
Within a SELECT statement, use the CAST() function to retrieve file reference information, and use the GetAs() function to retrieve data of a specific file type.
So your statement needs to be: SELECT GetAS(image_container, "JPEG"), if they are Jpegs, of course! And I'm assuming that you are using a container field as the result.
Untested so I don't know if this will work.
Thanks Beverly! I'll do some testing later today, and will post my results.
Thanks again for the reference to the ODBC guide.
Here's what I've found so far:
First, here's my basic SQL statement:
WHERE related_UID LIKE '%" & $_uid & "%'
related_context LIKE '%" & $_context & "%' " ;
"|" ; "¶" )
When I execute this, I get a list of the related file names.
So then, I tried GETAS(file_thumbnail, 'JPEG'), and got the same results.
By the way, I've tried storing the container contents both internaly and externaly. When stored externally, the results include the file path along with the file name.
I'm really hoping I can do this without a plugin.
I'm assuming that you didn't get any further with this. I am struggling with the exact same problem.
What did you do as a workaround?
I am having the same problem. My SQL seems to be formatted ok but i keep getting the error....There is an error with the syntax of the query. Did you manage to get this to work? I am also trying to populate a container field called logo from a table called LOGOS.
SELECT GET AS ( "logo", DEFAULT )
WHERE "id" = 10058
yes, old thread. ExecuteSQL(), the function, only returns TEXT, not container field - except the name of the file.
If, on the other hand you are talking about ESS and/or using the IMPORT and EXECUTE SQL, script steps, then please specify. The xDBC guide (and SQL guide) work with these.
Are you trying to push blob to container (from MySQL to FileMaker)? or ?? There may be driver limitations.
Hi.....thanks for the reply. Yes i am working with Filemaker and the execute sql function. I was trying to populate a container using an autoenter sql calc without having to add a new relationship to my relationship graph.
If it is not possible i guess i will just have to add a new relationship to populate my field.
1 of 1 people found this helpful
sorry, for the confusion:
ExecuteSQL() - the function
is NOT the same as
Execute SQL - the script step
Can you be a little more specific of your systems, set up and what you are trying to do?
1 of 1 people found this helpful
Just for everyone still looking for a solution to get container-data with ExecuteSQL() - function:
For the container field you want to retrieve data from via ExecuteSQL() , let's say a PDF, you just need to create a formula field in the same table of the container field that does Base64Encode() the container field.
With ExecuteSQL() you now receive a base64 encoded text string, that you just need to Base64Decode()
Code of formula "table::container_b64_enc": Base64Encode ( table::container )
Call to receive base64encoded container data from "table::container_b64_enc":
ExecuteSQL ( "SELECT container_b64_enc FROM table";"";"" )
When you Base64Decode a text-string you should give it a filename. That way if it's an PDF you can set a container field with the result and its showing the correct preview.
Hope that helps. I had to dive into it, to make custom-functions more reusable.
Might be faster to use ExecuteSQL to get the primary key of the record with this container field and set a (possibly global) match field to that ID so that a regular relationship matches to the record.
Do you know what the speed difference would be, especially with deployment over WAN? I do a lot with ExecuteSQL() to save TOs and grab data into variables keeping functions slimmer... In my mind it saves bandwidth.
I do not, but processing a large file byte by byte to encode or unencode the image file would have to take some time wouldn't it?
You'd need to do some tests with your data and WAN to see which is faster.
Putting data into variables can improve speed, but depending on your data and your queries, you may lose some of that gain due to the time spent evaluating ExecuteSQL queries--they aren't necessarily faster than other methods for accessing aggregate date in a different table. But there are a lot of variables involved so test and and see.
...processing a large file byte by byte to encode or unencode the image file would have to take some time wouldn't it?
Now I got your point on speed. thx. maybe a auto enter field value would do it.
I also love the idea to have base64(rfc) encoded text of a container field ready to be grabbed via SQL and send to APIs. different topic though I guess.