I have been transitioning my work over from Delphi to Lazarus. For the most part it has been smooth and satisfying experience. I'm loving Lazarus and FPC and being unburdened by closed source proprietary stuff in general.

The majority of the work I do requires connections to a Pervasive database (from Btrieve v6 all the way up to the most current). I should be able to do this via ODBC. I have no problem reading data, executing queries, etc. but as soon I try and act on a TSQLQuery using methods like .Delete(), .Append(), etc. everything appears to work fine, but as soon as the dataset refreshes my changes disappear. I can write to the same tables using INSERT, DELETE and UPDATE queries without issue.

Am I missing something stupid?

I see a lot of changes to this unit by marco, lacak and joost. If any of you or anyone familiar with the SQLODBC stuff are on this list I would love to chat with you about this, either via email or better still, on the IRC channel. I'm nateloaf on IRC, and try to be online most of the time.

Thanks so much for any help you can offer. This vital link in the chain is the only thing preventing me from ditching Delphi for all new projects!

Nathan Wild wrote

I have been transitioning my work over from Delphi to Lazarus. For the most part it has been smooth and satisfying experience. I'm loving Lazarus and FPC and being unburdened by closed source proprietary stuff in general.

Good to hear that ;)

We don't know your code, so no idea... <open door> Are you .Posting the data?

To give you an idea, the GUI/RAD tutorials
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial0 http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1 and
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2 should work with odbc as well as with other dbs; just use odbcconn..

Also, you could have a look at the database test suite: http://wiki.lazarus.freepascal.org/Databases#Running_FPC_database_tests fpc\packages\fcl-db\tests\dbtestframework.pas

The info on
http://wiki.lazarus.freepascal.org/ODBCConn
you probably already know about as you can connect etc. Please feel free to document any hints/tips/needed additions for Pervasive there...

You could create a minimal test program based on
http://wiki.lazarus.freepascal.org/Database_bug_reporting#FreePascal and post on the forum
http://forum.lazarus.freepascal.org/index.php/board,63.0.html with an attachment.

Reinier Olislagers wrote

On 13/03/2014 00:06, Nathan Wild wrote:

Please note that the original poster is not subscribed to the mailing list and thus may not see your responses (like the one below) unless including him in Cc: explicitly. Unfortunately, he hasn't mentioned this fact in his post...

Tomas

Thanks so much for responding! I am extremely excited about the prospect of getting this working.

Nothing I do in databound controls seems to commit anything to the underlying dataset.

If I open a connection, activate a TSQLQuery with 'SELECT * FROM AnyTable', connect it toa dbgrid, etc. I can delete rows, edit data etc. and it appears to work normally. Changes take effect inside the controls, values change in the dataset, etc. but as soon as I disconnect and reconnect the dataset all my changes are reverted.

  ODBCConnection1.Connected := TRUE;
  SQLQuery1.SQL.Clear();
  SQLQuery1.SQL.Append('SELECT * FROM AnyTable');
  SQLQuery1.Active := TRUE;
  SQLQuery1.Delete();
  SQLQuery1.Edit();
  SQLQuery1['AnyField'] := 'CHANGED';
  SQLQuery1.Post();

All of these changes take place in the UI. i.e. the first row is deleted and the field is modified in the second. As soon as I close and re-open the table, both changes are undone.

UPDATE and DELETE queries work as expected.

Some notes:
- Calling combinations of StartTransaction / Commit / CommitRetaining don't appear to do anything?
- Nothing I do changes the Active property of the associated SQLTransansaction (I'm not actually sure if anything should? Just mentioning it as this feels to me like a commit problem)
- I have nothing set in the UpdateSQL / DeleteSQL / InsertSQL properties of the TSQLQuery. Are these required when accessing data in this manner?

Thanks again to all who take the time to read this and I hope I'm not just missing something stupid!!

Nathan Wild wrote / napísal(a):

Do you use TSQLQuery.ApplyUpdates followed by TSQLTransaction.Commit or CommitRetaining ?
-lacak.

Nope. Is that required?

How does that factor in to accessing the data through bound controls?

Thanks.

Nathan T. Wild

You must call applyupdates.
Post only modifies the in-memory copy of the data, but doesn't actually execute any queries on the database. Only when you call ApplyUpdates(), the SQL will be executed to update the database.

Michael.

Nathan Wild wrote

Nothing I do in databound controls seems to commit anything to the underlying dataset.

It seems you're mixing GUI databound controls (dbgrid) with programmatic editing of the query...

The GUI controls are provided by Lazarus, not FPC. As this list only covers FPC development, could you please post questions about Lazarus on the Lazarus list, or, perhaps even better, on the Lazarus forum as you can include a sample project that demonstrates your problems?

Thanks

Responding privately - I read the Lazarus forum & mailing list, so I'll see responsed there as well...

I'm not a GUI guru at all but have you tried the sqldb tutorials 0 through 2? Do they work for you?

I'm betting the lack of calling applyupdates() is my problem then, at least when accessing the data programmatically.

Still doesn't explain why changes made in bound controls disappear also? I'll investigate this further on the Lazarus end.

Thanks very much!

Nathan T. Wild

Thanks very much!

I listed both programmatic and GUI examples because I use both in my day to day work... I think the fact that I was not calling applyupdates() is what was causing my issues. I will test this when I get to a computer later this afternoon. :)

I looked through the tutorials when I first started playing with Lazarus, but they looked like just an overview of things I've been doing in Delphi for almost 20 years. I will review them more closely and see if there are steps I'm omitting.

In Delphi there is no need to call applyupdates(), so perhaps there is a corresponding missing step using bound controls in Lazarus?

Thanks very much for taking the time to help out!

Nathan T. Wild

Nope. Is that required?

Yes is. TSQLQuery is descendant of "buffered dataset" so all changes what you do are cached localy until they are applied to remote database. ApplyUpdates does it ...

How does that factor in to accessing the data through bound controls?

Bound controls have nothing with it ;-)
L.