viewpoint-particle

Author Topic: ODBC Connection to FileMaker Server 13  (Read 4708 times)

PaulSamuelson

  • Newbie
  • *
  • Posts: 40
ODBC Connection to FileMaker Server 13
« 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

stanl

  • Pundit
  • *****
  • Posts: 939
Re: ODBC Connection to FileMaker Server 13
« Reply #1 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)
 


 

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: ODBC Connection to FileMaker Server 13
« Reply #2 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
objStudioName= RS.Fields("StudioName")
objStudioName.Value ="12345"
 

Reference:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+ADO.txt
Deana F.
Technical Support
Wilson WindowWare Inc.

PaulSamuelson

  • Newbie
  • *
  • Posts: 40
Re: ODBC Connection to FileMaker Server 13
« Reply #3 on: May 29, 2014, 10:27:22 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
objStudioName= RS.Fields("StudioName")
objStudioName.Value ="12345"
 

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


Thanks,

Paul

stanl

  • Pundit
  • *****
  • Posts: 939
Re: ODBC Connection to FileMaker Server 13
« Reply #4 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.