Hi all,

I'm wondering if anyone here has had success inserting and updating
records in FM6 via ADO.Net's ODBC managed provider.

I seem to be able to select records from the file, but the insert is
bombing, with an unhelpful 'System Error' exception.

Any ideas?

Here's the sql i'm trying to run:

INSERT INTO "Posting.fp5" LAYOUT "Layout #1" ( ID, EventDate,
EventTime, FTPHost, FTPPort, FTPUser, SMTPHost, SMTPTo, SMTPFrom,
SMTPSubject, SMTPBody, ArticleID, ArticleZIP ) VALUES( ?, ?, ?, ?, ?,
?, ?, ?, ?, ? )

I'm then settings the values via the command object's parameters.

The connection string is as follows:
Driver={FileMaker Pro};DSN=FMPro;

Thanks!
Andy

In article <1117734295.992332.179120 (AT) g14g2000cwa (DOT) googlegroups.com>,
ajohnstone (AT) capcitypress (DOT) com says...
1) Trap your exception and examine the inner exceptions.

2) Start small and build it back up. Start with a single simple file
with single text or number field. Lose the parameterization and layout
info etc. And just fire a straight static ExecuteNonQuery("INSERT INTO
....") at the odbc connection and see what you can get to work.

Build it back up gradually until it fails.

Good luck.

Hey 42,

Thanks for the info. I finally remembered to examine the inner
exception. The problem is that Filemaker doesn't like the OdbcType
that I'm setting the paramater to for my insert.

So I did get the insert working; removing the container field from the
insert list (since you can't do containers with FM's odbc implemetation
i guess) and setting the number field to DB_REAL..

I'm having the same problem though trying to do a select.. and its a
simplier table. Two number columns, one an ID with autoenter and such
and a text column.

I'm getting the same error, even though i'm specifying the type for the
parameter as a DB_REAL..

In article <1117820138.416227.26460 (AT) g49g2000cwa (DOT) googlegroups.com>,
ajohnstone (AT) capcitypress (DOT) com says...
Per the documentation for ODBC for Filemaker 7.
(You'll want to dig up docs for FM6 to verify they didn't change!)

number fields should be mapped with SQL_DECIMAL
text fields should be mapped with SQL_VARCHAR or SQL_LONGVARCHAR
container fields are supportd; and map to SQL_LONGVARBINARY

Per the documentation for ADO.NET

SQL_DECIMAL should be mapped to Decimal
SQL_LONGVARCHAR should be mapped to String or Char[]
SQL_LONGVARBINARY should be mapped to Byte[]

You indicated you were using real numbers...this could be a source of
problems.

42,

I initally went with what was in the FM7 documenation; switching to
real solved the problem for FM6 (since I can't seem to locate
equivelent documenation for 6).

I did get it working (finally), but now I'm on to another issue.

When sharing the file with the Pro client, I noticed that when I did a
select, it changed the found set on the client.

Wanting to verify it wouldn't affect other users found sets if the file
is hosted by Server 5.5, I moved the file to a test server, and changed
my DSN to use the remote server.

I ran my program, and the found set indeed changed... which I found
hard to believe. So I closed the client and ran the app again.

To my dismay, I get a Connection failed error.

Any ideas?

In article <1117826185.104925.294070 (AT) z14g2000cwz (DOT) googlegroups.com>,
ajohnstone (AT) capcitypress (DOT) com says...
What did you do? Sharing the solution to your problem, especially
ODBC/ADO.NET stuff is always appreciated... becuase its usually silly
little minutia, and its always nice not to trip over the same problem
you just solved

That is by design for FM6, and you'll see similiar behaviour when
dealing with custom web publishing, instant web publising, and odbc/jdbc
connectivity.

Additionally FM Server 5.5 does NOT deal with ODBC it -just- talks to FM
clients. The FM client handles the publishing.

Naturally, if you are web hosting FM (via IWP, CWP, ODBC/JDBC, etc), the
proper deployment model, per the FM white papers on the subject is:

Data hosted on an FM5.5 Server.
Data published to the web/odbc/xml via (possibly a pool) of dedicated
FM6 Unlimited stations (or FM6 Pro if you only need a few simultaneous
connections).

You cannot effectively host ODBC/IWP/etc connections from a workstation
that somebody actually uses. ODBC in FM5/6 is very much just clumsily
"bolted on" to Filemaker Pro. It ain't pretty and it doesn't work
great... but at least its there.

For what its worth, this has all been substantially addressed in 7, and
the FM7 Server Advanced can do IWP/CWP/XML/ODBC/JDBC etc without
dangling the connectivity through a client. FM7 ODBC is light years
ahead of FM6 ODBC... but still light years behind MySQL or MS SQL
Server. Fortunately for FM ODBC is -not- its "normal" mode of operation.
Its still more "there if you need it", and though its steadily improving
I doubt it will ever catch up to MySQL... Filemaker doesn't "think" in
SQL, so its never going to be as good at it as one that does.

Have you considered doing this the other way, and using the Import script
step in Filemaker to pull this data into Filemaker rather than pushing it
in?

You perform an import in Filemaker from an ODBC data source (which you will
have to set up based on your source data). Once you've chosen the data
source, you get an interface which allows you to build a standard SQL query.
When you execute that, you get Filemaker's import screen, which allows you
to map your fields. If you're just adding new records, execute it and
you're done. If on the other hand you are updating some records based upon
a matching unique key and adding where you don't have a matching unique key
(such as Customer ID or whatever), choose "Update matching records in
current found set" and designate the unique key by clicking on the little
arrow beside it, and it turns to a bidirectional arrow. When you perform
this import, it updates the data in Filemaker without creating duplicate
records.

When you have successfully performed an import like this, you can create a
script step to save that import, and from then on activate it by launching
that script. You can make this completely silent by choosing to perform the
script without a dialog.

I hope this helps,
Ron

"Andy" <ajohnstone (AT) capcitypress (DOT) com> wrote

One last note to save whoever might try this a headache: do a "Show all
Records" in filemaker before performing an update import, otherwise it will
only update the records in the current found set and create duplicates on
the others.


"Ron Hofius" <Ron (AT) itsupport (DOT) net> wrote

42 wrote:
Oh, sorry I wasn't clear on this. There were two things I did.

Firstly, I set the command parameter for the Number field to be
OdbcType.Real, which maps to SQL_REAL. I think something changed
between 6 & 7 where 7 now uses the documented type, but 6 uses the Real
type.

The other thing I had to do was not try to work with the Container
field. Appearently you just can't use those fields at all with FM
ODBC.

Thats very disapointing. Its odd, because with MS I've never really
felt lockedg in building applications (Office is another story..but I
digress), but with Filemaker it certainly feels like they don't want
you running anything else but Filemaker.

Well, this is good to know, and may be the route we take for now.
Fortunatly, as you mention below, 7 handles this more easily, but
you're right, there still are alot of things missing.

Well I hope its at least Good Enough. More and more we'd like to get
to FM data from outside of FM itself. FM has some nice features, but
I'm kinda suprised how much even the lastest version is still lacking..
transactions for example. Of course maybe I missed that part too :-)

Thanks again for your input.
Andy

Ron,

We are generating data from outside of FM, it doesn't yet exist in
another DB or anything.

Basically we have an application doing some stuff, and simply want to
keep some audit records of what happened.

So it'd be easier just to hit FM with a SQL insert call, rather then
trying to force it to some other format, and then trying to trigger
something in FM to pull it in.

Thanks for the suggestion though, always do appereciate other takes
on solutions!

Andy