Is there any documentation for the SQLDB components ? I have put a fair bit of effort in to looking round the FPC and Lazarus documentation and wiki areas, and while there are a few helpful hints here and there, I have not been able to find any sort of overview of how the components are supposed to work, particularly once you try to update data. While I can make some guesses from looking through the source, it is really hard to guess from that how the writer intended them to work, and the best way to use them.

thanks,
John

That's indeed very difficult to understand without documentation. And indeed, there isn't any.

IN principle you can set ReadOnly to false and ParseSQL to true. That way sqldb tries to parse your query. If it's a simple 'select * from table' the TSQLQuery will be updateable. It automatically generates update/delete and insert queries. For the 'where' clause is uses by default the primary key of the table. (That's a setting, upWhereKeyOnly)

For example: 'delete * from table where pk=:old_pk'

If you edit some data, those changes will be stored in an updatebuffer. With TSQLQuery.CancelUpdates all those changes are lost. But if you call .ApplyUpdates, it will execute one query for every changed record. (That could be a insert, update or delete query)

If you have a more complex query (sqldb can handle more complex queries then the one above, but not everything, offcourse) you can provide your own update/insert and delete queries.

You can use parameters in those queries. The new value for a field is stored in a parameter with the same name as the field. If you need the old value from a field, you have to use the prefix 'old_' For example:

'update table set field1=:field1, field2=:field2, field3=:field3 where (field1=:old_field1) and (field2=:old_field2) and (field3=:old_field3)'

Those are the basics. If you have questions, ask them here. And if you have any time, please document it somewhere on the wiki. ;)

Joost

Joost van der Sluis wrote

Those are the basics. If you have questions, ask them here.

I have two!

- please send me some hints to configure a query as fast as possible -- read only and unidirectional. I will open the query, read everything and close it.

- what about RowsAffected?

Thanks!

Set ParseSQL to false. (This wil automatically set readonly to true) It won't be unidirectional, but I made an unidirectional TSQLQuery once, and coudn't measure any speed difference. The difference was to small. And maybe you could tweak packetrecords. Set it to -1 so that all data is 's faster if you really need all data
immediately. (Consumes more memory, though)

- what about RowsAffected?

You mean how many rows are affected by the last query you've run? I saw request for that earlier.

But imho, you never need it. You always should know how many rows are affected before you execute a query. It could be a debug-tool, though.Maybe I could implement it. Maybe for one specific connection, or maybe even in a general form.

Joost.

Thanks for the hints.

This feature will be used in an opf framework to ensure that I didn't have a conflict. Something like this:

update <list> where id = :id and updatecount = :lastupdatecount

If RowsAffected is 0 I have a conflict. I can use a select just before the update but I will create an useless overhead, since the database provides this information.

When you say implement it, you don't mean 2.2 I think. If so, would you have a workaround to point out?

Thanks.

you can't always know, for instance if you do a conditional update: update programmers set overtime = 'yes' where work_hours > 80

i've never used it in a program myself, but I've noted that there's an api function for this in mysql and there's the sql function ROW_COUNT() (in 5.0.1). i suspect there are similar functions for different flavours...

Joost.

henry

Of course not, what absurd nonsense.

It could be a debug-tool,

MySQL has mysql_affected_rows, Postgres has PQcmdTuples.

Regards,

Adriaan van Os

Hi Everyone, (gdb) list
I have made FPC (fpc-2.1.4_aka_2.2.0_beta) for ARM with DEBUG=1 so I can debug problem:

I have a program which works okey in same version for i386 but for arm I'm trying to debug it to see what it's happening

I have run program in GDB and I received it:

Program received signal SIGSEGV, Segmentation fault. TRY_CONCAT_FREE_CHUNK_FORWARD (MC=0x40213170) at heap.inc:679 679 in heap.inc

and Backtrace is:

(gdb) print MC
$17 = (PMEMCHUNK_VAR) 0x40213170
(gdb) print MC_TMP
$18 = (PMEMCHUNK_VAR) 0x0
(gdb) print SIZEMAK
No symbol "SIZEMAK" in current context.
(gdb) print SIZEMASK
$19 = -16
(gdb) PRINT MC.SIZE
$20 = 690513264
(gdb) bt
#0 TRY_CONCAT_FREE_CHUNK_FORWARD (MC=0x40213170) at heap.inc:678 #1 0x0002a574 in TRY_CONCAT_FREE_CHUNK (MC=0x40213170) at heap.inc:691 #2 0x0002acc0 in SYSFREEMEM_VAR (PMCV=0x40213170) at heap.inc:1040 #3 0x0002ad0c in SYSFREEMEM (P=0x40213170) at heap.inc:1059 #4 0x00029ad8 in FREEMEM (P=0x40213178) at heap.inc:342 #5 0x0001cd0c in fpc_ansistr_decr_ref (S=0x40213178) at astrings.inc:107 #6 0x00077ee8 in LUAPUSHVARIANT (L=0xdda08, N=void) at LuaUtils.pas:464 #7 0x00069a10 in LUACOPYTABLE (L=0xdda08, IDXFROM=4, IDXTO=2, MTTO=3) at lua_WrapUp.pas:51
#8 0x00069dd8 in NEW_TMYTYPE (L=0xdda08) at lua_WrapUp.pas:125 #9 0x000961bc in luaD_precall ()
#10 0x0009f084 in luaV_execute ()
#11 0x00000000 in ?? ()

mc^size of 690513264 is not very regular, is it?
What size is normal for it?

Any Idea?

How Can I patch it?

thank you for your help

Jose Pascual

Hi Everyone,

I have downloaded last snap (20_06_2007) for fpc 2.3.1 and I have make and make installed for i386,
for ARM compilation stop compiling objects.pp because {$E+} is inside code,

It does not work for ARM I have commented this switch to comopile.

best regards

Jose Pascual

So if I change some field values and post the record, that changed data goes into the update buffer - of TBufDataset ? I should then be able to accumulate a few changes of these, and then, in code, call TSQLQuery.ApplyUpdate, and it should be sent to the database ? Should this also commit at the database level, or do I have to do this ? (I think I tried this, but didn't pursue it very far, because I wasn't sure if it even should work)

Would it be feasible / sensible to call ApplyUpdate from an AfterPost event handler ?

Those are the basics. If you have questions, ask them here. And if you have any time, please document it somewhere on the wiki. ;)

I will, if I get to the point of thinking I understand it enough to not make a fool of myself!

John

You have still to commit the database-transaction. (SQLQuery.Transaction.commit(retaining)) And yes, the buffer is handled by TBufDataset.

Would it be feasible / sensible to call ApplyUpdate from an AfterPost event handler ?

Yes, you could do that. But that could generate a lot of sql-traffic. Maybe in an after-scroll is a better idea.

> Those are the basics. If you have questions, ask them here. And if you > have any time, please document it somewhere on the wiki. ;) I will, if I get to the point of thinking I understand it enough to not make a fool of myself!

Just start it, others will correct mistakes if needed.

Joost

Following earlier advice, I constructed a simple query ("select * from organiser.durn_type"), set readonly to false and tried to modify some data and commit it.

(At this point I should note that I am doing this from Lazarus 0.9.22 with the supplied fpc, 2.0.4 I think, and I am connecting to a postgresql database v 8.1.4 on the same PC, all on Window$ XP SP2.)

I have no trouble opening the query to display the data in a grid (readonly or not) but when I made some changes, ApplyUpdates gave a database error: "syntax error at end of input at character 127"

I first tried to trace the execution (in Lazarus IDE), but was unable to trace into the db or sqldb modules - "step into" ran straight to the error, and breakpoints reverted to disabled once the program started to run. Is there any reason why this should be so ? Or should I ask in the Lazarus discussion areas ?

I then turned on lots of logging in Postgresql, and found the error to be in a statement:

2007-06-29 14:50:16 LOG: statement: EXECUTE <unnamed> [PREPARE: prepare prepst0 as select * from organiser.durn_type] 2007-06-29 14:50:16 LOG: statement: prepare prepst1 as select ic.relname as indexname, tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique
from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i
where( (i.indrelid = tc.oid)
and (ta.attrelid = tc.oid)
and (ia.attrelid = i.indexrelid)
and (ic.oid = i.indexrelid)
and (ta.attnum = i.indkey[ia.attnum-1])
and (upper(tc.relname)='ORGANISER.DURN_TYPE') )

  order by ic.relname;

I then ran this in an SQL window from PGAdmin, and it ran, but returned no rows. I then removed the "ORGANISER." prefix on the table, and it ran and returned some rows. So, I removed the "organiser." schema prefix from the query in Lazarus, and it worked ! YAY! (The database connection is logged in under that schema) However, is it definite that I can't specify a schema prefix for a table, and should I document it as a restriction, or does this rate as a bug ? I can see from looking at the pg_class table that the above query is never going to work - relname is a simple object name, and the schema owner is an oid in another column.

Finally, as this table is a set of fairly static codes, and therefore would not expect a lot of activity, I tried constructing the editing form as a dialog box, only applying updates and committing when the OK button is used to close the form, and canceling the updates otherwise. Again, does this make sense ? It seems to work, except that: When I only change one line and close (committing) it is fine, but if I change several lines, I get a string of errors "EVariantTypeCastError - could not convert type (String) to type (OleStr). " It still works - I presume the errors must be "handled", as I do not see them if I run from outside the IDE, and the changes are saved.

thanks for any help,
John

This is what is done: to create the update/insert/delete it has to detect what the primary-key is. For that purpose it executes the second query.

To fix this the table-name has to be parsed, to get the schema-prefix. That's not an easy fix.
You could solve this problem by setting the update/delete/insert queries yourself. (and set parsesql to false) Or you could set UsePrimaryKeyAsKey to false, and set updatemode to UpWhereAll, or make one of the fields a key-field.

This is normal. The exception is handled, but the debugger used by lazarus, can't detect this. But check that your changes are really saved.

Joost

I have a basic editable connection working now, but there remain a number of questions:

1) When I tried editing in a dbGrid, I had trouble with the field length. Looking through the code, I can't see anywhere where the length of a string is checked against the length of the field, and longer strings appear to overflow the field / record buffer. Should this be so ? I can fix the problem by doing the edits with a dbEdit control with a specified maximum length, but I can't find anything in the dbGrid component to do this. (I presume it would really be the TColumn component)

My real question is, should it be the data control be doing it, or should the sql components truncate a string that is too long ?

2) I am now trying a simple master slave form, with the slave table having a "parent" field which links to the primary key (a sequence generated number) in the master table. I can make this work with an After Scroll Event on the master table thus:

  with sqlAC {TSQLQuery component selecting from slave table} do begin
    active := false;
    Params[0].AsInteger := sqlPars.Fields[0].AsInteger; {sqlPars is on
the master table}
    active := true;
    end;

Is there anything less drastic than closing and reopening the sql that will refresh the query with the new parameter values ?

I tried to do this by setting the datasource on the above SQL, (and masking the AfterScroll event), but kept getting "field not not found" errors. Should this work ? (I had the parameter name matching the field name I was trying to link to. The field is only called "ref", so there is not a lot of room for typos, and yes I checked for the correct case!)

You could solve this problem by setting the update/delete/insert queries yourself. (and set parsesql to false)

When I set ParseSQL to False, the object inspector in Lazarus says "updating is only possible if ParseSQL is true" - unless I make it readonly, which defeats the purpose.

Looking through the code, I surmise that when the query is opened, InternalOpen calls Prepare which in turn calls ParseSQL, but that ParseSQL exits after only getting the statement type if ParseSQL is false. If the statement is a "select" statement and if it is updateable, then InternalOpen initialises the update queries. If SQL text is supplied, it is assigned to the queries, otherwise they are left blank. When ApplyUpdates is called, this calls ApplyRecUpdate for each record, and this then either runs the supplied SQL, or, if the supplied SQL is null, generates it own SQL (using the stuff stored by Parse SQL) .

Is this basically correct ? (If so, you might see some of this expanded a bit into some documentation)

If so, I guess leaving ParseSQL=true will waste some processing time, but not actually stop it working.

Any hint about the structure of the code for the update queries ? I guess I can work them out looking at what ApplyRecUpdate is trying to generate, but an example would make life easier.

4) I still haven't been able to set a breakpoint in, or trace into any of the sqldb code. Is there any good reason for this ? I can do so in other library units, "buttons" for example. (If I could work this out, I might be able to answer a more of the other questions for myself)

Thanks for your patience,

John

John wrote

I have a basic editable connection working now, but there remain a number of questions:

Have a look on MSEide+MSEgui:

http://www.homepage.bluewin.ch/msegui/

Screenshots:
http://sourceforge.net/project/screenshots.php?group_id=165409

It has lookup buffers, a sophisticated tdbwidgetgrid and many other useful components for database development and a stable and highly productive IDE. The users of MSEide+MSEgui affirm that it is production ready.

Please send questions and bug reports to (NNTP):

news://news.grid-sky.com/public.mseide-msegui.talk

Martin

This is more a Lazarus-issue.

My real question is, should it be the data control be doing it, or should the sql components truncate a string that is too long ?

The data-controls. Or you can leave it to the SQL-Server. The official SQL-specs say that the string should be truncated without any error. But not all SQL-servers obey that rule...

There is some master-slave system build in. But it effectively does the same as you did. Hoe you should use it exactly, I don't know.

That's a but, I think. It should check if there are any update/delete/insert queries provided. If that is the case, you can make a query updateable, even if ParseSQL is false.

Yes, except...

If so, I guess leaving ParseSQL=true will waste some processing time, but not actually stop it working.

... sometimes ParseSQL also changes the query a bit, mostly to help filtering work. In some cases, the query isn't parsed correctly, and the changes make the query invalid. If that's the case, you'll get syntax errors in your query, while you think that there's nothing wrong. In that case you have to set parsesql to false.
Second thing is that it also tries to obtain the primary key. This could also lead to trouble. (as in your case)

Any hint about the structure of the code for the update queries ? I guess I can work them out looking at what ApplyRecUpdate is trying to generate, but an example would make life easier.

update table tblPeople set name=:name, birthdate=:birthdate, email=:email where PeopleID=:PeopleID

4) I still haven't been able to set a breakpoint in, or trace into any of the sqldb code. Is there any good reason for this ? I can do so in other library units, "buttons" for example. (If I could work this out, I might be able to answer a more of the other questions for myself)

That's because sqldb is part of Freepascal, while buttons is part of Lazarus. By default, fpc is distributed without debug-info, but Lazarus does have debug-info.

So you have to recompile fcl-db with debug info. If you have the full fpc-sources, go to fpc/packages/fcl-db and execute 'make clean all OPT='-gl' '
After you have done that you have to install the freshly created .ppu's. You could copy them over the old ones yourself. Or you could use 'make install' if the system is configured right.

Joost.

It depends on the completely rewritten tmsebufdataset, if you want to use the MSEgui DB components you need to to replace TBufDataset with tmsebufdataset and the SQL connection components need to be patched. The development speed of MSEide+MSEgui is too fast that I could depend for DB code on FCL with the difficult and time consuming FPC coordination process for bug fixes and changes. Another problem is that I am not bound to Delphi compatibility so I don't think that my code will be accepted by the FPC team. Feel free to integrate any MSEgui code which you will find useful into FCL. :-)

Martin

Why don't you contribute the database code to the FCL ? This way, everybody benefits from it. It's non-visual code, so it can perfectly be separated out.

Michael.

As a relatively new user, I absolutely agree. There is a wealth of components already, and trying to work out which to use where is already quite daunting, especially when there is little/no documentation to guide you! And I am new only to FPC/Lazurus - I have been programming Pascal since Turbo Pascal 3, and did some reasonably serious database applications using Delphi 4 C/S into Oracle "on the side" during a stint as an Oracle DBA.

John Sunderland

So in fact, you are duplicating SQLDB. Judging from your website, you even used the lazarus icons for the components.
(this is ok, just a constatation)

The development speed of MSEide+MSEgui is too fast that I could depend for DB code on FCL with the difficult and time consuming FPC coordination process for bug fixes and changes. Another problem is that I am not bound to Delphi compatibility so I don't think that my code will be accepted by the FPC team.

Since SQLDB is in no way connected to Delphi, so you're free to do as you please, as long as it descends from TDataset ?

The coordination process would be non-existent, because I would give you direct access to the FCL subversion. As long as you follow the rules for the merging of fixes to fixbranches there is no problem.

In fact there would be less problems, as fixes would be tested sooner/better and would make it to the fixes branch easier. With the new packaging system which will be in effect after 2.2, the FCL can be released on a more regular basis.

Feel free to integrate any MSEgui code which you will find useful into FCL. :-)

I'd rather see that the whole is coordinated a bit. I don't think all this duplication is a good idea; One solid set of components makes much more sense: the number of developers available for it's maintenance would double. The end user would also benefit, as we can give 1 solid set of components, no doubts possible.

There are not so many people available who understand the inner workings of TDataset and friends, so all this scattering and dividing is counterproductive.

It would be much better if you, me, Joost and Bram (who work on SQLDB) had a single set of components to work with: speed would not be an issue then, and we could guarantee a working and reliable set of components for everyone.

Michael.

Can you make a bug-report for this?

I aggree with Michael on this. Besides, last time I looked at your 'TBufDataset' (which is a actually a fork, offcourse) it only implemented blob-fields, on top of the existing TBufDataset.

The current TBufDataset also supports blob-fields, in a (in my opinion) better way, now. Are there any other changes/additions?

Regards,
Joost

You are right, I will think about your offer. :-) Michael, Joost and Bram please look into my code, can you imagine to merge with FCL?
The relevant files are in
https://mseide-msegui.svn.sourceforge.net/svnroot/mseide-msegui/trunk/lib/common/db

Martin

It is completely rewritten. Buffer organisation is not a linked list, the record pointers are hold in arrays to allow simple record accessing by locally builded indexes and easy recno handling. String fields are stored as widestrings, there are no fixed string field sizes in the record buffer. It has the possibility to work comparable to TClientDataset with a local change log and data file.

Martin

That's good, thank you :-)

Michael, Joost and Bram please look into my code, can you imagine to merge with FCL? The relevant files are in https://mseide-msegui.svn.sourceforge.net/svnroot/mseide-msegui/trunk/lib/common/db

Checking out now.

I'll consult with Joost and Bram separately. I am a bit worried about the widestring issue. While good for msegui which uses widestrings internally, it's also slower and more memory consuming. A fixed buffer has it's advantages. The change-log handling and indexes are a Good Thing (tm), and I remarked on that some time ago already :-)

We'll see. Let me consult, and then I'll get back to you.

Michael.

Quick question, before I continue with the evaluation:

I find no evidence of this last thing in the code ? (in trunk, that is) msbufdataset has fixed buffers for strings, and uses 1 byte per char.

Michael.

Hm. Quite the difference, indeed.

Thanks.

Michael.

There is some garbage in the directory.
The component is tmsebufdataset and the file msebufdataset.pas.

Martin

I had the 'old' tmbufdataset in mind. The TMSEbufDataset is indeed very different.

As you can probably remember, the TBufDataset also used a array to store the record buffers. I changed that because benchmarks showed that the current solution is much faster. Even if you want to address a record immediately, as long as you use a bookmark, and not the recno directly. Which is imho not always a good idea.

But there are no indexes yet, that's completely true.

What I do like is the idea of storing strings in a widestring/ansistring. I tried to implement that earlier, but it's not doable in a Delphi-compatible way. As you said, you don't have that problem. But it's still something on my whish-list for sqldb/TDataset/TBufDataset. There must be a solution, without using the blob-system...

What do you mean with the local change log and data file?

Regards,
Joost.

Recno should never be used for positioning.

I do not think this is a good idea: the amount of memory used for this is a multiple of the classic buffer scheme, the heap will be terribly fragmented. The buffer system is exactly meant to save memory (blobs excepted).

What do you mean with the local change log and data file?

Like TClientDataset: the changes to the records (the delta) are kept in a separate index. All can be written to file. It's a briefcase model.

Michael.

Do you think? Using the buffer, there's a lot of memory which isn't used. I thought about adding a new kind of fieldtype, for example ftAnsiString which you could use for larger strings. But maybe you can use a ftMemo in that case....

I thought so. TBufDataset also has that, it can't save it to file, though. But that's easy to implement.

Not more than when using ansistrings - or worse, widestrings - to store the string data. You forget the heap overhead and ansistring "hidden" data, plus the whole reference counting mechanism.

I thought about adding a new kind of fieldtype, for example ftAnsiString which you could use for larger strings. But maybe you can use a ftMemo in that case....

Memo is very special and hard to handle in grids and the like.

See my private mail to you for a more detailed calculation.

Indeed. All one needs is some time :-)

Michael.