WinBatch® Technical Support Forum

Archived Boards => COM Automation and dotNet => Topic started by: PaulSamuelson on May 28, 2014, 10:35:14 AM

Title: ODBC Connection to FileMaker Server 13
Post by: PaulSamuelson on May 28, 2014, 10:35:14 AM
I am trying to connect to a FileMaker Pro Server 13 via ODBC. I am able to read data with the following code:

DBServer="192.168.1.129"
Database="dbname"
User="ODBC"
Pass="password"

cConn=ObjectCreate("ADODB.Connection")
RS1 = ObjectCreate('ADODB.Recordset')
RS1.CursorLocation=3;adUseClient

cConn.ConnectionString='DRIVER={FileMaker ODBC};SERVER=':DBServer:';DATABASE=':Database:';UID=':User:';PWD=':Pass:';'
cConn.Open()

RS1.Open("SELECT * FROM Studios WHERE Code='Test'",cConn,3,3)
StudioName=RS1.Collect("StudioName")
message("Studio",StudioName)


However, if I try to update data I get Error 1261, Multi-Stop operation generated errors...

RS1.Collect("StudioName")="12345"


I am able to use SQL to make changes, but would prefer to use ADO if possible.

RS1.Open("UPDATE Studios SET StudioName='12345' WHERE Code='Test'",cConn,3,3)

Any thoughts?


Thanks,

Paul Samuelson
Title: Re: ODBC Connection to FileMaker Server 13
Post by: stanl on May 29, 2014, 06:46:31 AM
Maybe

RS1.Open("UPDATE Studios SET StudioName='12345' WHERE Code='Test'",cConn,3,3,1) ;

[NOTE: I generally use cConn as my Connection String and oConn as my ADODB.Connection]


Multi-step errors the are worse to debug as they can refer to any number of issues. You might consider going directly from the connection (assuming it is opened as updateable):

cSQL="UPDATE Studios SET StudioName='12345' WHERE Code='Test';"
cConn.Execute(cSQL)



Title: Re: ODBC Connection to FileMaker Server 13
Post by: Deana on May 29, 2014, 07:40:41 AM
I am not familiar with the ADO Collect method. I know you can set values for Fields using something like this:

Code (winbatch) Select

objStudioName= RS.Fields("StudioName")
objStudioName.Value ="12345"


Reference:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+ADO.txt
Title: Re: ODBC Connection to FileMaker Server 13
Post by: PaulSamuelson on May 29, 2014, 10:27:22 AM
Quote from: Deana on May 29, 2014, 07:40:41 AM
I am not familiar with the ADO Collect method. I know you can set values for Fields using something like this:

You should be. It (normally) works well to read or write data in one step.

Code (winbatch) Select

objStudioName= RS.Fields("StudioName")
objStudioName.Value ="12345"


I tried that, too. The first line works, but the .value gives a 1261 error.


Thanks,

Paul
Title: Re: ODBC Connection to FileMaker Server 13
Post by: stanl on May 29, 2014, 10:31:47 AM
collect() is a 'hidden' ado function, not publicised that much but doesn't require the overhead of loading the fields object.

Using either connection.execute or collect(), the former requiring less code - both are ADO. You might need to look into setting the cursor location for the connection object. Also, in your SQL statement you used single quotes, while with collect() you used double. I know from experience those little things can cause multi-step (for example SQL server likes ', access likes "). Maybe setting a server cursor location rather than client for something like UPDATE. Maybe try a dynamic cursor and optimistic locking with collect().  I'm sure it is something little, perhaps peculiar to FileMakers ODBC.