ryanp Posted June 11, 2008 Share Posted June 11, 2008 Hello all, so I have FMPA 9.0v3 installed on OS X Leopard 10.5.2 running a solution on FMSA 9 from a Windows Server. My solution connects to a SQL Server for several tables using the server's ODBC connection. Then I also run SQL statements in a few scripts, which require the ODBC connection also on the client, for which I use the Actual Technologies driver on OS X. All of this was working flawlessly, until I upgraded my system from a MacBookPro to a MacPro this week. I do have a fair bit of experience with upgrading systems and all that, but for some reason, the client's ODBC just throws errors when I try running the scripts from that client. I still have one other client that can run them as well, and she can still run them successfully, so I know the script didn't change or anything like that. Yet when I run them, I get bizarre errors from FM, where the title says "ODBC Error: abcde" where the actual error text is in a jumble of foreign chars, making no sense whatsoever. To make matters worse, most of the time, after getting these errors, Filemaker crashes, and that is the end. I wrote a simple test script that simply tries to run a known query against the SQL server (an UPDATE), and then dumps the error to screen if there is any (always has been so far on the problem box). I have tried running this from FMP 9.0v3 installed on the same box, and same issue. Could having both on the same box do something weird? I have upgraded my Actual Technologies drivers from 2.7 to the new 2.8e, and still same issue. I also have run the "TEST connection" button from within the Actual setup, and it connects to the SQL server no problem, so the issue lies with my particular FMP installations it would seem? Just checking if anyone else has seen anything like this? Ryan Link to comment Share on other sites More sharing options...
ryanp Posted June 11, 2008 Author Share Posted June 11, 2008 OK, I just got down wiping out all traces of FMP and FMPA from my MacPro, then I reinstalled FMPA, and tried running my test script, and it still failed with FMPA 9.0v1. I then went ahead and ran the updater, and then tried again with FMPA 9.0v3 and the test script failed again with an unintelligible error given again. Link to comment Share on other sites More sharing options...
ryanp Posted June 11, 2008 Author Share Posted June 11, 2008 As a further development in this saga, I have now installed FMP on another MacBookPro, 10.5 w/ the Actual drivers 2.8e, set up the ODBC connection, tested it, and then tried to run the test script, and this one actually displays the errors. So it gives me 3 errors, each being dismissed by an "OK' button, one after the other. They are: 1) Failed to allocate statement 2) Failed to disconnect 3) Failed to disconnect Anyone know what those mean, or how I can go about fixing this situation? Ryan Link to comment Share on other sites More sharing options...
devinriley Posted June 12, 2008 Share Posted June 12, 2008 Check the DSN on the server again and verify that it can connect to your ODBC source. If it can, try looking in the script again and being certain you are using that connection and not your local one to that machine. I've had similar errors lately like that and the code is specific to the Actual driver. Occasionally, you can find info about the codes online, but that driver has been flaky on me of late. As an alternative, is this the kind of thing that you could set up as a VIEW in your ODBC source and then just reference that way? That would eliminate the need for the client drivers and let the server carry the load. Link to comment Share on other sites More sharing options...
ryanp Posted June 12, 2008 Author Share Posted June 12, 2008 Well, the server part is working just fine in the solution. I am in the process of re-writing my script to avoid having to use a machine-specific ODBC source since that is giving me such grief. This script that throws errors sends SQL statements, so those, at least in my experience, require the local machine ODBC source. Not sure exactly what you are talking about for the last part as far as the VIEW? I would prefer to let the server do the work. Link to comment Share on other sites More sharing options...
devinriley Posted June 13, 2008 Share Posted June 13, 2008 If the records you intend to update are always going to match specific criteria that won't really change, you could write a SQL query and turn that into a View in SQL server. It basically creates a virtual table of records matching your request with a SELECT statement. Now in Filemaker, you can reference that view just like a regular SQL table, and set up the appropriate relationships, say an id key field even to a global. Now just work on the table in FMP on the client side while the server does the work to get you the SQL data. Those SQL errors are coming from the Actual Driver not communicating properly, and that's why I'm recommending avoiding them if possible. Link to comment Share on other sites More sharing options...
ryanp Posted June 13, 2008 Author Share Posted June 13, 2008 Sorry, perhaps I didn't make the query situation quite clear enough. The only SQL statements I am running are UPDATE and INSERTs, and occasionally a DELETE. I am not using SELECTs at all, as you mentioned, I have VIEWs for that. Link to comment Share on other sites More sharing options...
devinriley Posted June 13, 2008 Share Posted June 13, 2008 My point with that was to ask if there is a reason you aren't letting Filemaker do that natively instead of using the SQL code. You can just as easily delete a record in SQL when working with that table on a layout as you could a Filemaker record. If you need some control, is it something you could handle as a script to prevent errors that you might be concerned about. So for the update, perhaps a script with a stored find in the layout that refers to your VIEW and then write a loop to go through and make your change, no? Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts