Hi

Need to resolve a problem when use LoadFromStream  to load some records transferred from another table with identical structure in order to synchronize the two tables in an accounting program where the last table perform  record updates when recorded transactions in acounting sofware.

The problem is created when fti_autoincrement field values​​, AutoInc field of the first table matches values ​​in the table where the data will be loaded. Then I presented the following error Duplicate key found in the index '' of the table 'fti_autoincrement.

I thought about using the trigger fix onnewrecord soperacioninv table is the table involved but in reality I need create a routine that checks for all tables in the database and fix the primary key before post the record.

I can not find documentation about using onnewrecord nor the use of triggers with components created in code as seen below:

localSession:= TDBISAMSession.create(nil);
  localsession.SessionType:=stlocal;
   localsession.sessionname:='nueva'  ;
     LocalSession.name:='default';
    localsession.active:=True;
     LocalDatabase:=TDBISAMDatabase.Create(nil);
     localdatabase.SessionName:='nueva';
     localsession.privatedir:='C:\Sistema\interface\llegada';
     localdatabase.DatabaseName:='datostempo';
           localdatabase.Directory:='C:\Sistema\interface\tiendas\'+oficina+'\EMPRE001\DATA';
      localdatabase.Connected:=True;
TempTable:=TDBISAMTable.Create(nil);

I apologize if the question I ask maybe for some obvious answer but I have managed to solve all the other steps but can not find the solution for this.

What I need is an example of code to implement the trigger onnewrecord considering I do not use droppend components on the form i created it in code shown above.

Thanks in advance

Regards

Juan Carlos Carta

Sorry about my english ispeak spanish

On 7/28/2013 8:09 PM, jccarta wrote:
> Hi
>
> I thought about using the trigger fix onnewrecord soperacioninv table is the table involved but in reality I need create a routine that checks for all tables in the database and fix the primary key before post the record.
> I can not find documentation about using onnewrecord nor the use of triggers with components created in code as seen below:

OnNewRecord is a TDataSet event so documentation is in Delhi (e.g.
http://docwiki.embarcadero.com/Libraries/XE4/en/Datasnap.DBClient.TClientDataSet.OnNewRecord)


As to the triggers - since you're using a local session you need to
attach them in code to the engine itself (or drop an engine somehwere in
your project and then you can do it in the IDE).

See
http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphiwin32&version=XE4&topic=Customizing_Engine

(Triggers section)

Raul

jccarta


I'm not confident that you can do it the way you're thinking. My approach would be a bit more laborious and take a bit more programming.

Create an interim table (again with the same structure) that you empty and stream the data into then using navigational code loop through that and transfer the data into the table you want it in leaving out the autoinc column which will then be populated by the engine.

Roy Lambert

Juan,

<< The problem is created when fti_autoincrement field values&#8203;&#8203;,
AutoInc field of the first table matches values &#8203;&#8203;in the table
where the data will be loaded. Then I presented the following error
Duplicate key found in the index '' of the table 'fti_autoincrement. >>

Roy's suggestion is the solution I would use.  Create an in-memory table
that looks just like your target table, minus any primary indexes.  Load the
stream into that table, clear out the autoinc fields so they are null, save
the contents to a stream again, and then load *that* stream into the target
table.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


The other one I thought of was to use a query on the server which produced the data with a NULL rather than the autoinc column and stream that.

Roy Lambert [Team Elevate]

Thank You Roy Tim and Raul for your answers

Please i need last aid

There is Some way to create or copy a memory table  from a Disk Based Table


Roy Lambert wrote:

Tim


The other one I thought of was to use a query on the server which produced the data with a NULL rather than the autoinc column and stream that.

Roy Lambert [Team Elevate]

jccarta


The easiest way is with a SELECT INTO statement.

Roy Lambert [Team Elevate]

Juan,

<< There is Some way to create or copy a memory table  from a Disk Based
Table >>

The easiest way is to use this method:

http://www.elevatesoft.com/manual?action=viewmethod&id=dbisam4&product=delphi&version=7&comp=TDBISAMTable&method=CopyTable

Just use "Memory" as the target database name.

Tim Young
Elevate Software
www.elevatesoft.com

Thank You Tim and Roy
I try the solutions from un but  i cannot solve any Problems;

Tim I try to use the comand copy table with the copydata parameter but i receive error: too many parameters

Roy I try to use select into with this code:

localquery.sql.clear  ;

                     tabla := temptable.TableName;
                     TablaTemp:=TDBISAMTable.Create(nil);
                     localquery.sql.add('Select * into memory\tmp_'+tabla+' from '+tabla) ;
                     localquery.Active:=True;
                     tablatemp.DatabaseName:='memory';
                     TABLATEMP.Name:='tmp_'+TABLA;
                     tablatemp.LOADFROMstream(FS);

When execute the last line I receive error message:

DBisam Engine error #11010 table or backup file '' not exist

"Tim Young [Elevate Software]" wrote:
Juan,

<< There is Some way to create or copy a memory table  from a Disk Based
Table >>

The easiest way is to use this method:

http://www.elevatesoft.com/manual?action=viewmethod&id=dbisam4&product=delphi&version=7&comp=TDBISAMTable&method=CopyTable

Just use "Memory" as the target database name.

Tim Young
Elevate Software
www.elevatesoft.com

jccarta

>localquery.sql.clear ;
>
> tabla := temptable.TableName;
> TablaTemp:=TDBISAMTable.Create(nil);
> localquery.sql.add('Select * into memory\tmp_'+tabla+' from '+tabla) ;
> localquery.Active:=True;
> tablatemp.DatabaseName:='memory';
> TABLATEMP.Name:='tmp_'+TABLA;
> tablatemp.LOADFROMstream(FS);

I'm not 100% sure what's going on here.

1. I'd use ExecSQL rather than Active := True (personal preference as much as anything)
2. I think you'll probably need to close the query (I'm not sure if the table is still being kept exclusive until you do)
3. I don't know if you have to open the table before calling LoadFromStream - I've never used it.
4. I'm not sure where the memory table is going to reside - on the server or the local client (how have you set up the sessions for localquery and tablatemp?)

Roy Lambert [Team Elevate]