 | |
#1
 
| |||
| |||
|
ADO support -
05-09-2005
, 04:10 PM
#2
| |||
| |||
|
|
Hi, I'm totally new to FileMaker, but experienced with programming. I'm using FileMaker Developer 7. I want to do some advanced operations with the database, so I installed the ODBC driver. I'm trying to use ADO to connect to it, from an ASP page. I was able to select, read and display records successfully. However, updating records through a recordset doesn't seem to work. I tried various cursor and lock types, but still no luck. It either hanged or said "Query-based update failed because the row to update could not be found." Can anybody help me with this? |
perform a replace with serial numbers, and then update the 'next value
to use' to the next serial number. (for future records).
Quote:
|
On a side note, what I was trying to do is to add an "autonumber" column to a table that already has many records. I created the column in FM but I couldn't get it to fill it with data automatically (for the existing records). If you know how to do that, I'm also interested. But I still want to be able to access the database through ADO, if possible. |
complete subset of SQL keyword and syntax that is supported. Are you
sure what you are trying to do is supported?
While I know you do have *some* control via odbc to create and define
tables, its not complete, and certainly not as detailed as what can be
done in FM directly.
The ODBC support for FM is really primarily about
queries/inserts/updates, not table definition or database management.
#3
| |||
| |||
|
|
If would be much simpler to just define the new field in FM, and then perform a replace with serial numbers, and then update the 'next value to use' to the next serial number. (for future records). |
Quote:
|
Read the ODBC documentation carefully, the appendices go through the complete subset of SQL keyword and syntax that is supported. Are you sure what you are trying to do is supported? |
suppose it is implemented via an UPDATE command underneath.
Quote:
|
While I know you do have *some* control via odbc to create and define tables, its not complete, The ODBC support for FM is really primarily about queries/inserts/updates, not table definition or database management. |
ODBC) with FM. Why do you think changing a field value would involve
that?
Adrian
#4
| |||
| |||
|
|
If would be much simpler to just define the new field in FM, and then perform a replace with serial numbers, and then update the 'next value to use' to the next serial number. (for future records). I think it worked, thanks. Read the ODBC documentation carefully, the appendices go through the complete subset of SQL keyword and syntax that is supported. Are you sure what you are trying to do is supported? What I am trying to do does not involve SQL commands explicitly, but I suppose it is implemented via an UPDATE command underneath. |
the SQL statements that are failing. Check them with a query utility,
and against the odbc docs to sort out exactly why they aren't working.
Its likely that Microsofts 'automagically generated update logic' is
flawed, and solving the problem might simply invove taking manual
control over it.
You're talking ADO and ASP; I only breifly used those and hated them.
But I have used ADO.NET/ASP.NET quite successfully. ADO has significant
limitations, as I recall, in the degree of control you can take over its
cursor engine and command builder.
Worst case I think you should be able to get around it by manually
implementing your own update logic and using that. (e.g. loop through
the recordset calling your own update/delete/insert SQL statements as
appropriate.)
Google around the ADO sites for additional help. From what I understand,
it was a reasonably common problem with ADO in a variety of scenarios...
not just FM. The first step is to check the SQL that its hitting the
odbc driver with though, to see where its gone south.
Quote:
|
While I know you do have *some* control via odbc to create and define tables, its not complete, The ODBC support for FM is really primarily about queries/inserts/updates, not table definition or database management. I never tried to do anything about table creation/definition (through ODBC) with FM. Why do you think changing a field value would involve that? |
"what I was trying to do is to add an 'autonumber'
column to a table that already has many records. I created the column
in FM but I couldn't get it to fill it with data automatically (for the
existing records)."
It *sounded* like you were trying to "Alter Table" to set up a column as
auto number or something, upon re-reading, i understand that was not the
case.
#5
| |||
| |||
|
|
In article <1115710400.964366.261500 (AT) o13g2000cwo (DOT) googlegroups.com>, aditsu (AT) gmail (DOT) com says... If would be much simpler to just define the new field in FM, and then perform a replace with serial numbers, and then update the 'next value to use' to the next serial number. (for future records). I think it worked, thanks. Read the ODBC documentation carefully, the appendices go through the complete subset of SQL keyword and syntax that is supported. Are you sure what you are trying to do is supported? What I am trying to do does not involve SQL commands explicitly, but I suppose it is implemented via an UPDATE command underneath. It most certainly does. Get in there with the debugger etc and capture the SQL statements that are failing. Check them with a query utility, and against the odbc docs to sort out exactly why they aren't working. Its likely that Microsofts 'automagically generated update logic' is flawed, and solving the problem might simply invove taking manual control over it. You're talking ADO and ASP; I only breifly used those and hated them. But I have used ADO.NET/ASP.NET quite successfully. ADO has significant limitations, as I recall, in the degree of control you can take over its cursor engine and command builder. Worst case I think you should be able to get around it by manually implementing your own update logic and using that. (e.g. loop through the recordset calling your own update/delete/insert SQL statements as appropriate.) Google around the ADO sites for additional help. From what I understand, it was a reasonably common problem with ADO in a variety of scenarios... not just FM. The first step is to check the SQL that its hitting the odbc driver with though, to see where its gone south. While I know you do have *some* control via odbc to create and define tables, its not complete, The ODBC support for FM is really primarily about queries/inserts/updates, not table definition or database management. I never tried to do anything about table creation/definition (through ODBC) with FM. Why do you think changing a field value would involve that? This is what you said: "what I was trying to do is to add an 'autonumber' column to a table that already has many records. I created the column in FM but I couldn't get it to fill it with data automatically (for the existing records)." It *sounded* like you were trying to "Alter Table" to set up a column as auto number or something, upon re-reading, i understand that was not the case. |
#6
| |||
| |||
|
|
42, I'd like to hear more about how you can 'check' the SQL syntax. All I've been able to do is keep trying different syntax until something works, but I've not found anything that will point me to the correct syntax for the DD driver. Have you? |
(or copy/paste) and get the result sets (or error messages) quickly,
without having to recompile the app each time. You can use that to
rapidly narrow down the clauses that are causing problems.
In this particular users case, he doesn't even know what sql is getting
generated right now, so it would be very useful to capture it, and then
try it manually (for error messages), and check it against the
documentation for issues.
The documentation I'm referring to is the "FM 7 ODBC_JDBC
Developer.pdf" on the FMSA CD, particularly "chapter 4".
That document has helped me out several times... e.g. when porting from
mysql I ran into some queries which failed. It turned out mysql has a
'concat()' string function but from the docs it was clear that there was
no concat() function and that DataDirect/FMSA uses the "+" operator to
concatenate strings.
That said, I have heard that there are some errors/omissions in it. But
so far, at least, its served me well.
#7
| |||
| |||
|
|
In article <1181ub0ni5l5n40 (AT) news (DOT) supernews.com>, john (AT) datagrace (DOT) biz says... 42, I'd like to hear more about how you can 'check' the SQL syntax. All I've been able to do is keep trying different syntax until something works, but I've not found anything that will point me to the correct syntax for the DD driver. Have you? I merely meant to use a query tool, so you can just type in the queries (or copy/paste) and get the result sets (or error messages) quickly, without having to recompile the app each time. You can use that to rapidly narrow down the clauses that are causing problems. In this particular users case, he doesn't even know what sql is getting generated right now, so it would be very useful to capture it, and then try it manually (for error messages), and check it against the documentation for issues. The documentation I'm referring to is the "FM 7 ODBC_JDBC Developer.pdf" on the FMSA CD, particularly "chapter 4". That document has helped me out several times... e.g. when porting from mysql I ran into some queries which failed. It turned out mysql has a 'concat()' string function but from the docs it was clear that there was no concat() function and that DataDirect/FMSA uses the "+" operator to concatenate strings. That said, I have heard that there are some errors/omissions in it. But so far, at least, its served me well. |
#8
| |||||
| |||||
|
|
It most certainly does. Get in there with the debugger etc and capture the SQL statements that are failing. Check them with a query utility, and against the odbc docs to sort out exactly why they aren't working. |
I'll try that when I have more time. However, I think it doesn't just
involve SQL but the ODBC architecture too, and I'm not familiar with
that.
Quote:
|
You're talking ADO and ASP; I only breifly used those and hated them. But I have used ADO.NET/ASP.NET quite successfully. ADO has significant limitations, as I recall, in the degree of control you can take over its cursor engine and command builder. |
Quote:
|
Worst case I think you should be able to get around it by manually implementing your own update logic and using that. (e.g. loop through the recordset calling your own update/delete/insert SQL statements as appropriate.) |
"primary key" in that table (that's what I wanted to add), so I relied
on the recordset mechanism to do that.
Quote:
|
Google around the ADO sites for additional help. From what I understand, it was a reasonably common problem with ADO in a variety of scenarios... not just FM. |
tried to work with MySQL. And I found that MyODBC had some options to
fix them. Besides that, it worked perfectly every time. I expect that a
good ODBC driver should work with ADO.
Quote:
|
It *sounded* like you were trying to "Alter Table" to set up a column as auto number or something, upon re-reading, i understand that was not the case. |
didn't need to do it from ADO. The problem was filling it with values
(which I eventually did in FM too).
Regards,
Adrian
#9
| |||
| |||
|
|
42 wrote: It most certainly does. Get in there with the debugger etc and capture the SQL statements that are failing. Check them with a query utility, and against the odbc docs to sort out exactly why they aren't working. I'll try that when I have more time. However, I think it doesn't just involve SQL but the ODBC architecture too, and I'm not familiar with that. |
statements. Sure you could blame Filemaker or DataDirect (the ODBC
driver vendor)
Quote:
|
You're talking ADO and ASP; I only breifly used those and hated them. But I have used ADO.NET/ASP.NET quite successfully. ADO has significant limitations, as I recall, in the degree of control you can take over its cursor engine and command builder. Does ADO.NET have more control over those things? |
Quote:
|
Worst case I think you should be able to get around it by manually implementing your own update logic and using that. (e.g. loop through the recordset calling your own update/delete/insert SQL statements as appropriate.) Well, the main problem is uniquely identifying the rows. There's no "primary key" in that table (that's what I wanted to add), so I relied on the recordset mechanism to do that. |
basis whether they need to be deleted, updated, or inserted, etc so when
you submit back to the database it just loops through the rows and
performs the action. (by submitting an appropriate sql statement). It
does this on a row-by-row basis. Each row is reponsible for exactly one
update/insert/delete call, (or nothing at all if its unchanged).
I assume ADO has a similiar internal architecture, but can't comment how
accessible it is to a developer.
In ADO.NET you could manually loop and check that row property yourself,
or just manually set up the update/delete/insert yourself and let ADO do
its own loop.
If there is no primary key, and your relying on ADO to generate
appropriate sql statements, it would be interesting to see what its
doing.
After all, if -you- aren't uniquely indentifying rows in the database,
and -you- couldn't write appropriate sql update/insert/delete statements
that would work properly to update/insert/ and delete particular rows...
ADO might not be able to either, and this may part of the reason its
choking on you.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.