export script

[Matt P]

I was wondering if there was a script that would allow you to export from Filemaker to an ODBC. I would export to a file but the data I need to xfer is image files. Thanks in advance.

matt

[Matt P]

Does anyone know if this is possible?

[bruceR]

Does anyone know if this is possible?

If WHAT is possible?

Your question makes no sense. What are you actually trying to do?

[elo]

Yes, you can export "BLOB's" or FileMaker container fields over the ODBC connection provided by the FileMaker Pro ODBC driver (included on the CD-ROM).

If you have a table, tPictures having the fields picture_id (number) and picture (container). You can query FileMaker using ODBC to SELECT picture_id, picture FROM tPictures.

Your program is then responsible for how to interpret the picture (container/BLOB) data.

Additionally, some convenience functions for GetAsJPEG (check the included ODBC documentation PDF on the FileMaker CD-ROM for the exact syntax/name) are provided for container fields, e.g. SELECT picture_id, GetAsJPEG(picture) as picture FROM tPictures

etc.

[elo]

Here's some Ruby sample code that shows using the GetAs(fieldname, '4 character string') to extract a JPEG from a file.

require 'ADOWrap.rb'

$strConn = 'DSN=Filemaker;UID=ODBCUser;Pwd=ODBCUser;'



connection = ADOWrap.new($strConn)



qstring = %q[ 

SELECT CompanyLogo as raw, GetAs(CompanyLogo, 'JPEG') as jpeg, GetAs(CompanyLogo, 'GIFf') as gif

FROM gGlobals

]



begin

	connection.query(qstring, :symbol)

rescue ADONorecs => details

	print "Rescue: #{details.sets}n"

end



# the 'as' doesn't seem to play nice with GetAs directive, list out our column names

print 'Column names: '

connection.fieldlist.each { |f| print "#{f.to_s}; " }

print "n"



r = connection.data[0]

f = File.open('C:Tempoutfile.jpg','wb')

f.print r[:raw].pack('c*')

f.close()



# Is the :raw data the same as the GetAs(fieldname, 'JPEG') data?

print "Is the 'raw' data the same as GetAS(field, 'JPEG'? ", r[:raw] == r[:col2], "n"

# yes it is



# does the SQL driver do conversions?

print "Did the ODBC SQL driver convert my container to a GIFf for me? ", r[:col3], "n"

# no it does not, I get a nil

This only writes the 1st JPEG out. Looks like you need to know what type of data is in each row. Though it also looks like you can use the GetAs function's nil'ing to figure it out from a limited subset.



Here's the printed out put of running the program on one of my "global" tables that has a single record with the preferences including a company logo field:

>ruby testADOWrap3.rb

Column names: raw; col2; col3; 

Is the 'raw' data the same as GetAS(field, 'JPEG'? true

Did the ODBC SQL driver convert my container to a GIFf for me? nil

>Exit code: 0

So you can see that if you just ask for the column back or you GetAs(column, 'JPEG') assuming you have a JPEG the results are the same.

BUT, you cannot get FileMaker to convert the JPEG to a GIFf for you which is shown by the empty ('nil') value for the GetAs(column, 'GIFf')

ADOWrap.txt

Edited July 13, 2008 by Guest
fixed reference to ADOWrap.rb to be current directory, save the attachment as ADOWrap.rb