Salient Soft

I obtained an EE mobile router as backup for the occasions when my main FTTC internet goes down. The main internet went down recently for and extended period due to an OpenReach Fibre connection issue to the exchange.

EE have the best mobile data coverage for my area per the ofcom coverage site here.

Zen internet sent me an EE mobile router as backup during the above outage (I presume they knew that EE had the best coverage in my area).

I was able to readily plug the router into a PC and get mobile coverage.

However, doing this via my Fritzbox 7530 needed some tweaking. The fritzbox has mobile internet backup capability via a submenu on its internet menu. This allows enabling the mobile router (plugged in via its usb port) as a backup only when the main internet is down.

Initially when I tried this having unplugged the main DSL connection, the internet showed as up, but DNS translation did not work. I found that under the internet menu/Account Information/DNS Server settings, I had overridden the DNSV4  servers provided by the ISP with specific Zen ones. Once I returned this setting to “use DNSv4 servers assigned by the internet service provider (recommended)”, DNS then worked on the mobile connection. In addition, automatic failover to mobile worked correctly when I unplugged the DSL connection, although this did take a few minutes to stabilise and was not instant.

In practice, I planned not to leave the mobile router plugged in, but to connect when needed. However, the fact that it is capable of providing shared internet and wifi via the router to my whole network was a real plus.

I found that the EE mobile router performed particularly well from my garden office (which is of wooden construction). I obtained approx 50Mb down and 1 Mb up in this situation – the down link speed was actually better than my FTTC which is around 32Mb. However in the house via the Fritzbox 7530, this reduced to around 20Mb down, presumably due to the brick construction blocking the signal more than the wooden building did.

However, in both of these situations the mobile data rate was way better than I had previously obtained by tethering my Vodafone Mobile – in the latter case, the connection was very slow and intermittent – sometimes I could not get a connection at all, so it would have been no use as a backup for remote working. The EE mobile router was perfectly suited for use as a remote working backup, although I planned not to leave it plugged in permanently as a hot backup but to manually plug in when required, as it was not instant anyway, and in practice I may have to buy additional minutes from EE as they seem to expire if you don’t use them within a set period.

Comments Off on EE Mobile Router Backup via Fritzbox 7530

I initially investigated office-js to store excel macros using angular and typescript, but in the end there was not enough in the way of working examples online, and the platform did not appear fully typescript enabled – there were a number of posts about missing capabilities in the typescript interfacing.

I then looked at the old-skool VBA macro recording and this was much more straightforward, even if it is somewhat legacy going forward.

• To start with, you need to enable the developer tab via File/Options. This opens the way to all the macro capabilities.
• From this tab, you can then click to record a macro, perform whatever actions you like, then click stop recording.
• When you click to record a macro, you get the choice of current workbook, new workbook, or personal macro workbook. I would also have liked the option to record to a designated workbook of my own, but you can’t do this.
• Fortunately, Macros are easy to copy around as source via the Visual Basic option on the developer tab. This opens a VBA view which shows all VBA from all currently open workbooks at once, so copying VBA between them is easy.
• I will need to come up with a place to store my macros. As I am using them to reformat different transaction sheets to merge into a master workbook, it looks like the master workbook would be the place. Also you can use VBA to open any existing workbook so I could use VBA to open the workbook to import, from VBA in the master workbook, and then run the desired macros on them depending on the type of the imported workbook (which would be selectable on my open dialog).
• Merging and editing macros, and copying between workbooks, is easy in the VBA view, as they all end up as VBA source. I intend to record a number of small atomic macros to do single actions. Each is saved as a separate method in a VBA module, so I can easily add another top level method to call them in turn etc. and add other logic. Very little VB/VBA knowledge is needed (as you might expect).

Whilst all this is perhaps a bit legacy compared with office-js (which itself does not appear to be fully supported for typescript and local use on all platforms), it is just so much less hassle than office-js for what I am trying to achieve.

Comments Off on Storing Office Macros

I was using Spring Data JPA with Oracle as part of an export process from CouchDB to Oracle. The CouchDB export application first exports my existing Places data from CouchDB, then maps it to the entity model I am using for JPA/Oracle, then persists it into Oracle, and finally performs some rudimentary listing of the Oracle data by logging it.

Some issues/points came to light during this excercise which I am noting here:-

1/ My connect string to Oracle was using a Pluggable database in Oracle XE. This needs to use a service name (xepdb1) rather than a SID (xe) for the connection. To do this I needed to use a slash before the service name in the application properties, to indicate a service name, rather than the colon I would normally use for a SID:

# Oracle settings
spring.datasource.url = jdbc:oracle:thin:@localhost:1521/xepdb1
spring.datasource.username = places
spring.datasource.password = xxxxxxxx

2/ I changed the Hibernate naming strategy in the application properties to prevent unwanted snake casing of Oracle column names:

# Hibernate settings
spring.jpa.hibernate.naming.physical-strategy = org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3/ For persisting to Oracle, I had the choice of using Spring Data JPA, calling save() or saveAll() on the Individual Repository interfaces that extended CrudRepository (such as PlacesRepository), or I could use the Entity Manager. The Spring Data methods would persist or update fine, and could also save an iterable in one call. However, I had some small additional logic as I was passing and returning a Map. As I chose not to put this logic in the service layer, I therefore used an ordinary JPA Dao (repository) class, and autowired the entity manager via the @PersistenceContext annotation. I could therefore incorporate my logic in a single call and use entityManager.merge() to persist. I felt that situations vary and that it was not always appropriate to use Spring Data Interfaces for some use cases. However the Spring Data find queries were useful and clever for querying the database as they allowed for example a find by key with a like, and order by, just by declaring the appropriate method calls in the interface. I also used the @Query annotation for a custom query, which again was just annotated on a method in the interface.

4/ I had issues whereby the foreign keys on dependant entities were not being auto populated and threw up as null, even when I set nullable=false on the @JoinColumn annotations. In the end it was a simple case of not populating the objects in my model on both sides of the join before persisting – i.e. I was populating a list in a parent object for a one-to-many, but not populating the parent in the dependant object. Doing this resolved the problem and everything persisted automatically with a single parent persist/merge, with hibernate allocating primary keys via Oracle sequences, and then auto populating them in the foreign keys. This stack overflow post here pointed me in the right direction on this. I kept the nullable=false annotations where appropriate to correctly match the relationship I was using, and what was in Oracle.

5/ Once I was at the point of listing everything I had persisted, using Spring Data queries and some simple logging, I was initially hit with Hibernate multiple bag fetch exceptions when listing the data. At this stage I had annotated my repository methods as @Transactional rather than the service layer, and was therefore using explicit eager loading annotations as I needed everything to load before the methods returned. This post here details the issue, and it appears that Hibernate can only eagerly load one collection at a time. My solution to this was to take the more normal route of annotating my service methods as @Transactional rather than the repository, which then allowed a number of repository calls to be made from a single service method, all in the same transaction. Whilst this did expose the service layer to the transaction architecture and javax.transaction package, it is the more normal approach and gives flexibility in the service layer. In my case I could then just revert to the default lazy loading and perform the logging in the same service layer method (which I was happy to do as this was a very basic example just to demonstrate fetching the imported data from Oracle). Everything then worked fine.

Comments Off on Using Spring Data JPA with Oracle

I just installed the latest 21c version of XE, and having created the system user, I connected to this via SQL developer, using XE as the SID. All fine so far.

When I then went to create a new schema via the CREATE USER command, I received ORA-65096: invalid common user or role name. Upon researching this I found that Oracle now uses a hierarchy of pluggable databases or PDBs inside a container database or CDB. Schemas aka users can then sit inside a PDB as normal. Inside the CDB, where I was logged in, the rules are different and common users are required which have their own rules and naming conventions.

I just wanted a simple local develpment solution for JPA use, so did not especially want to learn all the ins and outs of this. I found that an XE installation creates a default PDB called XEPDB1, and managed to create an SQL developer connection to this using the already created system account. I tried using XEPDB1 as the SID rather than XE, but this failed as invalid. However when I tried XEPDB1 as a service name rather than a SID it all worked fine. I did not need to make any other changes to the connection settings – user (system), password, and port (1521) were identical.

Having then connected via this new connection, I was successfully able to create a new schema in the default PDB. As expected, to connect to this new schema I again had to use the service name of XEPDB1 and I was able to connect, once I had granted the CREATE SESSION privilege to the new user after creating it to allow me to log on. I could then proceed to grant the necessary privileges to the user and create/populate tables etc. Initially, in addition to granting CREATE SESSION, I just simply granted UNLIMITED TABLESPACE to allow the schema user to allow tables to be populated. With this I was successfully able to perform inserts, upates, deletes, and selects on all the tables present.

Comments Off on Oracle XE 21c – Container vs Pluggable Databases – CDBs vs PDBs

I had a legacy EA 12 model for the places guide database which I had used as the basis for the CouchDB implementation.

I wanted to explore a relational database as an alternative route, with the aim of not bothering with completely offline access due to the devlopment effort needed, and the lessening need due to improvements in mobile data access in the last few years. Initially I created an Oracle version of the model file, and tweaked the PK and FK  data types from UUID to long and checked a few other things.

I then tried selecting the domain model in the explorer, and using Package/Database Engineering/Generate Package DDL from the toolbar. This brought up the relevant dialogue to do the export, but try as I might it could not find any entities to export as DDL.

I then found this post here, which points out that you need to transform the relevant entities to DDL first to enable this to work. I did this using Package/Model Transformation (MDA)/Transform Current Package. You then select the entities to transform and select DDL on the right under transformations, and then hit Do Transform. This creates a DDL diagram. The original Generate Package DDL option above then worked – I was able to find all the desired entities and transform them. I had to visit each entity to select the target database DDL format, in my case Oracle, or else I got an error, but once this was done, it all worked fine.

I elected to create a single DDL SQL file and did not worry about the entity ordering – you can reorder when doing the DDL generation but I did not bother. The output DDL was pretty good as a starter and was clear. It also appeared to have been created in a way that was order independent – the tables were all created first, followed by all the foreign key contraints, so that all the tables existed prior to creating the constraints, making it order independent.

Some tweaking of the model will be needed, especially in the area of Place Features related tables, and also sequence generation, as I would likely use Oracle sequences for PK generation, via JPA/Hibernate. However, I had reached a good trial starting point which avoided the need to create all the DDL manually from scratch.

Comments Off on Exporting and Enterprise Architect 12 model as SQL/DDL statements

Having got CouchDB working under Windows 11 with the legacy places data as per this post here, I then wanted to run the legacy places angular and ionic apps if possible, just for reference and to consider my options going forward.

The angular app was in v6.1.7 which I did not want to revisit and upgrade. Similarly, the ionic app was an earlier legacy version which could be tricky to reinstate under windows 11.

However, I did have already built distributions for both apps, and both were able to run successfully under Windows 11 via http-server without having to install anything else. For angular I used the existing build under the dist subdirectory, and for Ionic I the build is under the www subdirectory. Note that whilst the index.html for the Ionic version did specify that cordova.js was required, and caused a load error in the web console, this was not an issue when using from a browser as it was not needed.

I did want to be able to run them remotely as well, from other PCs and mobiles. I tried installing the distributions under Zen hosting, with remote access to CouchDB running on the local PC. However, this would not run under HTTPS as it meant having mixed content – the access to CouchDB was not HTTPS and I did not want to go to the trouble of installing a self signed cert locally to get it all to work – this excercise was not worth the effort. I could not find an easy way under Zen cpanel of allowing just this app to be HTTP only, with everything else defaulting to HTTPS. If I turned off forced defaulting to HTTPS in cpanel, the app worked fine under HTTP but other access was also allowed to be HTTP only which I did not like, so I dumped the idea of hosting under Zen directly. I did hit a spate of nasty looking cpanel issues when I did this and for a while thought I had broken the domain/ssl/cpanel access entirely whilst messing with domain/alias/redirect settings in cpanel, but in the end it all worked fine again.

I therefore continued to run/host locally via http-server. I did want to be able to potentially remote boot my local PC via Fritz and access the app from anywhere, which would mean auto running the http-servers at boot time. The easiest way to do this turned out to be to use the windows task scheduler, which unlike services can run batch files without any other tools such as srvany which is commonly used to do this for services. Whilst the scheduler is often used with time based triggers, it is perfectly possible to specify a trigger as ‘run at boot time’. I also specified that it should run without any user logged in, therefore with local access only/no user authentication. This worked fine and I could boot the PC and then immediately access the places apps from a mobile without logging in.

Another trick needed was to add some remote port sharing in the Fritz box for both apps, via my static IP addresses. When doing this I also had to add a port share for the CouchDB access, otherwise CouchDB also complained about mixing private CORS stuff with the remote access. Once I did this, and changed the app config to use the remote URL for CouchDB access, it all worked fine. Fortunately, I could also continue to access the same apps locally on the hosting PC even though they were still using the remote URL for CouchDB access – it all worked fine.

I then set up redirects under cpanel from Zen to make the app access look a bit more friendly with salient soft urls, and this all ran fine.

Comments Off on Running Legacy Places Guide under Windows 11 for reference

Update 9/5/23

I reinstalled the current latest version, V3.3.2, to try to solve an issue where I could not connect to Fauxton (In fact this was my mistake – I was effectively using 127.0.0.1:5984 rather than the actual IP address i.e. nodename:5984, having forgotton I had changed the binding in local.ini).

After reinstalling, I edited local.ini and used my previous trick below to add a plaintext password and let couchDB encode it. However, couchDB seemed to ignore anything I placed in local.ini, and instead was using a file etc\local.d\10-admins.ini. When I editied this latter file, I could add the password correctly and it was honored (then encoded by CouchDB and replaced in the file).

When reinstalling, I had simply uninstalled the old version via control panel, then installed the new one on top without removing any other existing files (in particular I left the existing database in place). It appears that the installer took my old custom settings and placed them in the above 10-admins.ini file.

I have left this as it is at present but may change this later. This page here gives a good intro to the configuration files, their precedence order and how to override stuff. This section on authentication mentions the 10-admins.ini file which it says is present if you intall from a package like I did.

Original Post

I had a zip backup of a complete old version with data, but needed to locate the old release to reinstall on a new PC and reinstate the data

MSIs of old releases can be found in the apache archive here 

If you look under the releases subfolder in the old installation there is a RELEASES text file which details the release information. Mine was release 2.0.0.

The latest release at the time of posting is 3.2.2, but I was not clear that the data structures would be forwards compatible if I just restored all the previous data I had (this is one way that backup is proposed, as opposed to replicating to another server which I didn’t want to do).

On issue was that I had temporarily forgotten the admin password. This post here details how to change an admin password manually if you forget – you can just change the hashed password against an admin user with a plaintext one, and CouchDB will detect this and swap it for the hashed version when it is next restarted.

After installing v2.0.0, it would not fully start on Windows 11 – the service paused during startup. It did however run on an older Windows 10 pc, and also ran correctly when I restored all the data.

I then tried the latest 3.2.2 version on the Windows 11 pc, and again restored the data. I had a couple of issues – one was an incorrect server name in the app config url which prevented access to the database. A second issue peculiar to V3.2.2 was that initially I got errors when trying to access the database from the app with no authentication (this simple legacy Proof of Concept was not authenticated). I then found in the user settings in V3 that by default there were admin roles specified which were preventing simple access with no authentication. Once I removed these, this solved the problem and access from the application worked correctly without authentication.

This then worked fine – there were no compatibility issues with V2.0.0 data when restored under v3.2.2.

Comments Off on Reinstalling Apache CouchDB

The title says it all. This really shouldn’t be hard, but I searched a number of forums full of gripes from people with no answers, and even on a microsoft forum, a microsoft reply said basically “sorry you can’t do that”. I felt this was particularly poor misinformation given that as below, I persevered with looking further and found a perfectly good solution that does it easily.

This post here was a start – you can do a “file/save as” of a a presentation, and select “powerpoint show” i.e. PPSX file (there is also a macro-enabled show file but did not need to try that). This looked promising as it did start the show when clicked on as a local file, but still did not actually start the show when shared via a hyperlink.

Later I found this post here which gave a clue to the issue and tried just adding a query string parameter “action=embedview” to the link URL. This worked perfectly in exactly the way I wanted – the show started automatically via the embedded viewer but still showed in a browser tab. If you want full screen you can use f11 as normal to toggle it in the browser. I didn’t care for having a link which directly took you into full screen powerpoint showing, as this takes control away from those who might be unaware of what is going on. There is an ‘open in new window’ icon at the bottom right, and this does open the presentation in a new tab without showing it, but this is fine, if someone wants to play with it they can. It also means they can save a copy themselves, but I was happy with this as they are viewing it all anyway, and if it was a web page they could export to PDF so not much different.

One key point on this is that it only worked when the file was hosted on my company MS onedrive share which actually had the office licence. It did not work on a Zen hosted file (it just downloaded it), and when I tried on a free MS onedrive share, it opened the presentation without running it directly, even when the above query string parameter was added. I am not sure of the reason for this, and may look into this further at some point, but it was not an issue anyway – I just needed to host the file on the correct share, and create a read only onedrive hyperlink with onedrive, and then add the query string parameter to the url, and it all worked fine.

This meant I could easily prepare simple powerpoint presentations to share for viewing, more easily than trying to put web pages together to do a similar thing – after all, the kind of presentation I wanted is exactly what powerpoint is for, and it does it easily. It was just a shame that the solution does not appear to be documented well online, but I was thankful for a good solution in the end.

Comments Off on Creating a hyperlink to a powerpoint presentation that opens and shows automatically

Firstly I installed the latest current release of node under NVM. This was 18.2.0, so this was installed simply using “nvm install 18.2.0”. This is only needed once as it is global for any project/folder running under NVM.

I then followed the upgrade procedure for angular here. Upgrading is well documented and supported – you can pick any pair of versions along with some details of your app complexity/what you use, and get specific update help here.

I then updated the various dependent components. I needed to add the switches “–force –allow-dirty”. “–force” was to force the updates through even when there were dependency version issues – this would all come out in the wash when finished, it was just a question of the dependency update order which triggered this. “–allow-dirty” allowed the update to proceed even when there were pending git commits present. I am unsure why the process is so picky about this – I did not intend to commit after every individual update command, and did not see why there should be a warning about it. The following updates were done:

npm install primeng@13.4.1 –force –allow-dirty
npm install primeicons@5.0.0 –force –allow-dirty
npm install primeng@13.4.1 –force –allow-dirty
npm install primeflex@3.2.0 –force –allow-dirty
npm install jasmine-core@4.1.1 –force –allow-dirty
npm install @angular/cdk@13.3.7 –force –allow-dirty

for my chart microapp which uses the PrimeNG chart component and therefore chart.js, I had to upgrade chart.js to the latest 3.8.0 or builds won’t work:

npm install chart.js@3.8.0 –force –allow-dirty

I also with noted with charts that the styling changed – charts were too big for browser window and needed unwanted scrolling, so would need to look into styling issues on this. Also the growl when you click on chart points etc. has gone.
This is replaced with a hover “growl” next to where you hover over a point. I confirmed the behaviour change with the primeng showcase.

I also noted that @angular/cdk  slipped the net orignally and needed doing (even adding again) manually as listed above.

After all the updates as above I was able to build and run in the usual way.

Comments Off on Upgrading Angular & PrimeNG Microapps from V11 to V13

This is fairly straightforward, but appears to be subject to the fundamental limitation that the Fritz Box cannot make use of multiple external IP addresses for the port forwarding, even if you are on an 8 port subnet like I am. I made absolutely sure that the Fritz Box was aware of the subnet by accessing its menus, then navigating to Home Network/Network on the left menus, and then clicking on the Network Settings tab. You then click on Additional settings to reveal some extra settings, and then scroll down and then click on IPv4 settings. Finally, if you scroll down and look under Public IPv4 Subnet you will see the public subnet visible/allocated to the Fritz Box, as a prefix and network mask.

In my case this was clearly an 8 port subnet, but unfortunately the fritz box only allowed port forwarding using the primary router public IP address.

I managed to work within this limitation however, as you can still forward multiple different ports to multiple different local devices. For example, to enable inbound RDP access to a PC as I did, do the following:-

1/ Visit Internet/Permit Access on the menus, and then click the Add device for sharing button.

2/ Select the target PC/device at the top, then at the bottom click New Sharing, which configures the desired sharing for the device.

3/ Then Click the Port Sharing radio button (as opposed to the default My FRITZ! sharing). Under application, I selected MS remote Desktop as the Fritz box was aware of RDP. You can select Other Application and enter the ports manually if you are working with an application that the Fritz box is unaware of.

4/ You then have 3 ports to configure, which is slightly confusing – I was not clear on all of this and the online help was blank on some of this detail. When I selected MS Remote Desktop, Port to Device and through… were both set to 3389, which was the correct internal port to use for RDP. Below this was Port requested externally, which is the actual external port you want to use. In one case therefore I left the defaults, but for a second PC, I set the external port to 3390, which allowed me to use the same external IP address for 2 different RDP configuration to access 2 different internal PCs successfully. I was not clear however what the distinction between Port to Device and through… was all about, but did not need to touch it.

5/ When accessing via the MS android RDP app, I was able to configure multiple PCs as required, and could also configure both Lan and internet configurations of the same PC so that I could access it both ways from my phone. A key trick here is that this app does allow a different RDP port to be used. Under PC name for the internet, I set the target IP address (the public IP address of the router), and appended a different port using e.g. :3390 notation to use port 3390 instead of 3389. You are able to give an additional friendly name for the PC when doing this, which is useful when configuring external IP access. Internally on the lan you can just use the standard windows network names. This then all worked fine and I was able to use my phone both to remotely access the fritz box to wake the PC, and then remote login using the MS RDP app to access it.

Comments Off on Enabling External access/port forwarding with the Fritzbox 7530, and using the MS RDP Android App.