Jeff Overcash

Clear not only the Fields property but the FieldDefs too.  Do this while the dataset is closed.  Then you can rebuild your new fielddefs.

  if FDMemTable1.Active then
  begin
    FDMemTable1.Close;
    FDMemTable1.Fields.Clear;
    FDMemTable1.FieldDefs.Clear;
  end;

Now it is ready for you to define the new columns.  Index etc done the same way, just clear their definitions.  Don't forget there are both an Indexes and an IndexDef, both should be cleared.  Filter you would just assign the empty string.

Database unavailable often happens because you have multiple InterBase installs and have the IB_PROTOCOL environment setting set either globally or within the IDE's local variables.  If that variable is set it must match the running server's port name.  If it doesn't you can not make local connections only loopback TCP connections.  You do not need IB_PROTOCOL set for anything it is just a quick way to identify the running instance so you can remove the environment variable without worry.  It is one of the first things I remove after installing a new RAD or new IB instance (I keep all mine on port 3050 just manually start the one I want to test against).

A little more on this.  There was originally a bug with deletes so you need the latest patched version of IB (2020 and 2017 I am sure).  But with FireDAC there is more you have to do to see deletes.

"FireDAC is very different. In order to display deleted rows from a ChangeView, it is necessary to switch the FDQuery into cached updates mode. You then have to add rtDeleted to the FilterChanges property, which is only available when CachedUpdates is true. Once I did this, I saw the deleted rows. So it would seem, with a default FDQuery, deleted rows are actively excluded when querying from a change view that records deleted rows"

I am not familiar with how FireDAC ended up implementing it.  IBX has an IBSubscription class for managing subscriptions, but much beyond that it will be manual to transfer changes from the main DB to a secondary or back.  You would just start a snapshot transaction, turn on the subscription read the data like normal and then walk the result set to and use a second connection and component to apply the changes. 

Adding some sort of merge feature would not be that difficult I think, but also something I'd have to wait until the next time I can do an interface breaking change.  Probably as simple as a MergeChangesTo and you pass an IBDatabase to use and it would use the existing InsertSQL et al but against the passed connection vs the original one.

I do recall an issue with FireDAC and Deletes in Change views (for some reason IBX was not affected).  I thought it was fixed in the latest patches for both 2017 and 2020, but I might be mistaken on that.  I have pinged Sriram on this to see if he can shed more light on it.

That would be a bug in FireDAC.  What you can do though is in your DB set up an Int64 domain that is numeric(18,0) and then it should go through.  I had a similar bug in IBExtract in IBX.

Try numeric(18,0).  InterBase does not have an alias for Int64 directly, but 18,0 is an int 64.

While I do not support Firebird, I also try not to introduce incompatibilities.  As long as Fb does not break backward compatibility you should be ok.  There is one caveat with this and it is more around the service API I think.  In most cases the Fb developers made sure to introduce new constants in a range higher than the original 6.0 open source release so they would not overlap with new ones introduced by IB.  With the service API though they just started at the next number so there are instances that Fb constant reflects a different function than the IB one.

For the past 22 years though there have been very few incompatibilities.  One of the reasons I went to an Interface/registration type system (see IBX.IBIntf.pas) for loading and resolving the client interface was so the Fb users could easily create a FbServer (which would look much like IBServer) class and intercept when data needs massaging to Fb constants before being sent to the server.

Some new features may or may not work like the RAD 11.0 PrecommittedReads feature to do reads through a connection-level pre-committed transaction so the dataset can stay open forever without causing OIT/OAT issues.  I do not know if Fb ever copied that feature from IB.

10 hours ago, jdg said:

This code:

tblConts.open;
ShowMessage('tblConts Record count: ' + IntToStr(tblConts.RecordCount));

Shows a message that says I have one record.  I don't understand why I'm getting this incorrect result.  It should be four.

 

 

InterBase does not return a record count on a select statement (it always returns 42) because this is expensive to do.  So RecordCount reflects the number of records fetched to the client.  Just call FetchAll after open if you want an accurate RecordCount.

If you are going to loop through the records just loop till Eof.  RecordCount is not needed to work through the result set.

FindFirst and FindNext are when you are filtering a Dataset on the client side.

To get the count of days in a month just subtract (as dates)  the first day of the next month from the first day of this month. Actually, as long as the day is the same from both months that works too so don't have to start with day 1 of the month.

select cast('2/1/2021' as date) - cast('1/1/2021' as date) from rdb$database

returns 31.

select cast('3/15/2020' as date) - cast('2/15/2020' as date) from rdb$database

returns 29  for Feb in 20202 etc.

Stano's only gives you the dates of the first and last days of the passed in month, you still need to subtract them and add 1 to get the number of days.

your problem is in your empty Table

procedure TfrmEntry.EmptyTable(tbl: TADOTable; tablename: string);
var
  MyCmd: TADOCommand;
begin
    tbl.Active := true;

    MyCmd.CommandText := 'Delete * from '+tablename;
    MyCmd.Execute;

    tbl.Active := false;
end;

You have a local variable named MyCmd, but you never initialize it.  Nor do you hook it up to the connection.  try this instead

procedure TfrmEntry.EmptyTable(tbl: TADOTable);
var
  MyCmd: TADOCommand;
begin
  MyCmd := TADOCommand.Create(nil);
  try
    MyCmd.Connection := tbl.Connection;
    MyCmd.CommandText := 'Delete * from ' + tbl.TableName;
    MyCmd.Execute;
  finally
    MyCmd.Free;
  end;
end;

Note you have 2 MyCmd variables.  One is local to your EmptyTable procedure and one public to your form, but neither is shown to ever be created.  You need to both instantiate a TADOCommand object (the .Create ) and you need to tell it what connection to use (setting the connection property).  Don't forget to clean up the variable when done.

20 hours ago, Henry Olive said:

Interbase

 

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * I.CURRRATE
WHERE EXISTS (SELECT 1 FROM INVOICE I where I.RNO = ID.RNO)

I'm getting   Column unknown I.CURRRATE  err.msg.

Thank You

 

 

Actually, the message is accurate.  You do not have a table alias visible in the set part named I. Assuming Currate is in the invoice table (what you aliased as I "I" the where clause tryt

UPDATE  INVDETAIL ID
SET  ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * (select I.CURRRATE from invoice i where i.rno = id.rno)
WHERE EXISTS (SELECT 1 FROM INVOICE I2 where I2.RNO = ID.RNO)

This part

(select I.CURRRATE from invoice i where i.rno = id.rno)

should reflect wherever currrate is.  

On 1/15/2021 at 6:20 PM, Gary said:

@emailx45

Success!!

I had to start over and use your example as a guide. Thanks much. I still am not sure where I went wrong. I think some of it had to do with setting things in the IDE,

and also I was using TIBDatabase instead of Firedac.

 

IBDatabase works just fine.  You set the Server type to IBEmbedded instead of IBServer to work with the embedded version of IB (Either IBToGo or IBLiite, license determines which mode the embedded works as).

Because Precommitted transactions do not get a transaction ID and can basically run forever without causing growth in the TIP page, the wisql window in IBConsole defaults to read-only transactions (not to be confused with a read-only database).

To change this click Transactions | Options and just switch the radio button from read to write.  If you always want it to be a writeable transaction check the Set as Default at the bottom.

Log in to the server node itself, then on the backups node right-click it and select restore.  From there should be rather self explanatory.  It is hidden and only available with the server node logged in even though that isn't necessary to do the restore itself.

16 hours ago, MikeMon said:

@Sriram& @Dany Marmur

 

Thank you for your input.

 

Dany, allow me to disagree with you in the sense that you are assuming that firstly, I want to HIDE the metadata, and secondly, that what's important in terms of protection is just the data and NOT the metadata. What I really want is to show the metadata of ONLY the tables or views that I've given a user a view access to; which should be the default behavior in my opinion. Only the database owner and the sysdba should have full access to view all the metadata by default. To give you an example, I've created a view and given view access to a user to just view the data in that view, but that user can connect to the database, let's say with IBConsole, and view the metadata of the whole database. In this way, they can use the metadata for their own use, e.g. set up their own database. For me this is a no-brainer. Why would I give a view access to a user for a single view, but be OK for that user to see my database's whole metadata?

Well, technically you can never hide your table structures cause clients can/have to get at that and have to get at that when SQL is prepared (and plans will tell someone really interested in the relationships).  What you can do though is null out your trigger and SP source in rdb$triggers and rdb$procedures leaving behind the binary version of it as that is what IB uses not your source.  So the real "brains" of your DB can not be extracted.  They can get at input and output variables (once again the server has to tell clients about that information so even hiding the metadata it can be extracted by someone knowing how), but how those variables are used internally can be hidden by removing the source after it is compiled.  BLR is not recompiled on backup/restore so the no source is nor harmful to any operations.

This looks like a difference between IB and Fb.  InterBase does not actually check the character length, but instead only checks that the # of bytes sent fit into the max # of bytes of the column declaration.  Normally that is 1:1@charset size bytes, but with variable byte character sets (like SJIS or UTF8) you could actually store more characters than defined.  So for instance anything in ANSI <= 127 all map to a 1-byte code point in UTF8.  So if you have a VarChar(5) UTF8, InterBase (and therefore early versions of Fb) would allow you to store '1234567890' into that column.  Reading the FireDac code section in this thread that is the type of check FD is doing.  Seems Fb at some point started enforcing strict character counting verification and is now throwing the exception seen here. 

I had complaints from people (ab)using this for SJIS when IBX went to Unicode because I enforced strict character counting on the client-side.

I tested this against IB and no exception is thrown (as expected) so at some point (I could not find in the FB documentation this change in behavior) strict character counting for multi-byte character sets must have been implemented in Fb. I am also able to insert 35 characters into a UTF8 VarCahr(32) in IB.

you can try this fix.  Untested but reads right to me -

{code}     

     if not lIsNull then begin
        pUTF8 := nil;
        if (lib.FBrand = ibFirebird) then begin
          if ALen > DataSize div 4 then
            if FVars.Statement.StrsTrim2Len then
              ALen := DataSize div 4
            else
              ErrorDataTooLarge(DataSize div 4, ALen);
          iByteLen := FVars.Statement.Database.Encoder.Encode(ApData, ALen,
            pUTF8, ecUTF16);
        end
        else begin
          iByteLen := FVars.Statement.Database.Encoder.Encode(ApData, ALen,
            pUTF8, ecUTF16);
          if iByteLen > DataSize then
            if FVars.Statement.StrsTrim2Len then
              iByteLen := DataSize
            else
              ErrorDataTooLarge(DataSize, iByteLen);
        end;
        if SQLDataType = SQL_VARYING then begin
          PVary(pData)^.vary_length := Smallint(iByteLen);
          pData := @PVary(pData)^.vary_string[0];
        end
        else
          pLen^ := Smallint(iByteLen);
        Move(PFDAnsiString(pUTF8)^, PFDAnsiString(pData)^, iByteLen * SizeOf(TFDAnsiChar));
      end;

{code}

The license is the same as for FireDAC the VCL or any other component of Delphi.  Free to use in your apps, but you can not redistribute the source to people who do not already have access (they are licensed) to the source.