FileMaker Insert to SQL Server

[Abarmard]

Hi,

Hope someone can help! I want to use FileMaker as a front end to SQL Server and want to be able to insert to the table in SQL Server using FileMaker layout. I can select the fields by ODBC but don't know how to insert. If anyone knows where I can find the step by step info to do this would be great. I am going crazy with this. Please help, thanks.

[Robert Kidd]

Define a calculation field, text result in the form :-

"INSERT INTO Accounts VALUES ( '" & Value1 & "' , '" & Value2 & "' , '" & Month(Date) & " / " & Day(Date) & " / " & Year(Date) & "' )"

Where "Accounts" is the name of the SQL table you want to insert into, and the fields are in the same order ( and format ) as they are in the SQL table.Be careful with the format of any dates.

Create a script with the step Execute SQL, select your ODBC DSN and the field you created above.

There is another INSERT syntax which specifies the field/s you want to insert into which is a little easier, I'll seee if I can find it and post it as well.

[Abarmard]

Thanks for your help, I'll give it a try today

[Abarmard]

I keep getting Exception error, but I think it's doable. Thanks again

[Rick Roades]

When trying this (I need to send data daily in batch to MySql db), All my dates come across as 0000-00-00 (MySql stores dates in yyyy-mm-dd). Since the reporting I need to do is date sensitive, I' stuck, and not a FMP developer - Can you offer any advice?

Thanks in advance

[-Queue-]

Create a calculated text field of Year(datefield) & "-" & Right( "0" & Month(datefield), 2 ) & Right( "0" & Day(datefield), 2 ) and use this field for your MySql data.

[Rick Roades]

I'm still getting "0000-00-00" in my date field in MySql...I've set the calculated field as both text and date type ("Calculation result is...").

The field looks set right, but just isn't transfering correctly.

[NetherChris]

The MySQL function "NOW()" may be of use to you.

[Rick Roades]

Sorry - I was unclear. I have to pull across several dates and times - dates of record creation, modification, project dates, etc. None of the dates I need to use involve grabbing what NOW() does.

I've read threads where people have done this, but not been able to get it to work, or to reach them individually.

[-Queue-]

My bad! I didn't include the second hyphen. Try Year(datefield) & "-" & Right( "0" & Month(datefield), 2 ) & "-" & Right( "0" & Day(datefield), 2 ). Create one calc for each date field you require.

[Abarmard]

Anyone know if it's possible to call a Stored precedure from FileMaker to SQL Server 2000? That would be fantastic. If doable I think everything will be great. Please let me know. Thanks in advance

p.s. I would like to update and insert by stored precedure while a user enters records using FMP.

[Rick Roades]

Thanks for the continuing help. Sorry for the long delay...

I actually caught the missing second hyphen, and had it in the field. But as a precaution, I copied and pasted your second post calculation, substituted the actual field, and reran the script. Still the dates (which all show properly in FMP) are 0000-00-00.

I'm at a total loss here.

[-Queue-]

Make sure the calculation is set as text. If it's a date, it definitely will get screwy. How are you pushing the data to MySQL?

[Rick Roades]

Calculation is set to text - had tried once the date setting, but when FMP didn't know what to do with it, I knew that wasn't working.

Using an ODBC connection (myodbc ver. 2.50.39.00), the MySQL and FMP are on the same dev server. The sql statement in FMP is:

insert into sales_clone (date_created)

values

(" & datecreatedformatted & ")

At this point, I guessed if I can get one date field working, the others will as well.

[-Queue-]

What happens if you try removing the quotes? Does set date_created=datecreatedformat work, with or without the quotes?

[Rick Roades]

I have tried every combination of quotes/without, with ampersand/without I can think of. Without the quotes, I get a syntax error in the sql statment from FMP on every record. Since the "datecreatedformatted" is the only field I'm sending over, that's the error. I've tried single quotes. Anything that doesn't error completely ends up as 0000-00-00 in MySQL.

Not sure what you mean on "set date_created=datecreatedformatted". If you mean using this as part of the VALUES statement in the sql statement, that would normally only be used in an UPDATE, not an INSERT, wouldn't it? Since I have to insert each record, I can't use that.

But, I will try it as an update, just to see if it goes over...

[-Queue-]

No, I mean the alternate Insert syntax of Insert into table Set colum=value.

[Rick Roades]

Ah - tried it...same result. Does anyone know of a way to set a data type during the insert statement? I'm beginning to wonder if it's because of the data type that it's zeroing out....

[-Queue-]

If you replace the date field with a simple number field that doesn't require ticks, does any data actually make it to the other side?

[William WM]

I would like to know EXACTLY which syntax I have to use to get an insert into working. None of the examples I found on the web works. Could someone please describe the exact syntax which I should use in like:

INSERT INTO tablename VALUES ( fieldName1 , fieldName3 , fieldName3 , value1 , value 2 )

Including & ' " and other characters which need to be there. And hopefully also why they have to be there, like:

You have to use & & around field names because bla bla...

Please?

[William WM]

So what I'm trying to do is impossible?