MariaDB SQL
Here you can see a new SQL Syntax to Exclude Fields in your SELECT Statement.
Android App
However, sticking to the Theme of previous Posts, I developed an Android APK which doesn't do a whole lot,
but the Little it does, it does well.
An SQL Statement can be entered and send to a Server which runs a little PHP API to process the Query and returns
the Records from the Query Statement. The APK displays that in a Table with the Field Names as Headings.
A Click on the Row opens a new Table with ONLY the selected Record displayed, however, additional technical Field Information
such as Table, Type, Key (if applicable), Comment, etc. are available as a Hover feature.
Nothing fancy, but for those needing a Starter or want to explore, you get a working Example!
Hang-on, Sources will be posted shortly.
I have tested this with INFINITYFREE.NET (and the AES.JS restriction) as well as my Apache2 Server and MariaDB.
Captive Access Point
In the Meantime, I got annoyed with the Access-Point setup as used in many Places. Not quite sure why People have the Configuration of a Standard Hotspot with Password,
when all they really want (since the Access isn't open), is just a little Web-Page to establish the Internet Connection.
Long Story short, that Setup is called a Captive Connection and most of us will have been at one Place or the Other where upon Connecting to the Hotspot
a Web-Page is presented, where a User acknowledges the Terms of Use and once done, the Internet Access is activated.
Sure, that's playing around, but so is most of what I do.
Long Story short, I have created such a Hotspot or AccessPoint for UBUNTU and will post the Configuration shortly.
Since I make the Assumption that Apache, PHP and some other Basics are already up and running, I will only focus on the Setup, which isn't all that complicated.
Compared to a traditional Hotspot, access (and logging) can be much better dealt with. If you look at it from a Commercial perspective, billing information can be recorded,
Time-Out options set and so on, which a simple WAP Connection wouldn't be able to do.
Captive Portal Example
SAP Client Setup
Plenty of People Talk about it, but a comprehensive Idea is rarely hatching. I am gonna change that.
Taking the Parties involved into account (Development, Training, Testing, ...) to Develop a Landscape that is manageable and makes sense.
All mixed like a good Salad and tossed up with a bit of Change Management.
Per Diem API
To help others, I have created a PerDiemFinder. Just a no frills, simple Web-Page anyone can run on their Browser. Only an API key is needed (2 Min Effort) from Uncle Sam
Database
However, I ended up creating a new MariaDB Instance as Replication Receiver, using my MariaDB Development Instance as the
Master or Primary Server. The minor struggle was the Configuration considerting that all runs on the same Machine and the only
"separator" I have are the Ports. Pretty much any "advise" I found on the Net naturally assumes different Machines, which means
Standard Configuration Files, thus there was not simple adaptation.
ANYWHO I got it up and running, thus any changes done on the Master Database is immediately replicated on the Receiver.
Frankly, in a setup as mine that makes little to no sense, besides the simple Excercise, but it is better than dying stupid.
So, my current Landscape has 6 MariaDB Instances (Development, Quality Assurance, Production, Garbage-Collector, Analyzer, Replication)
plus the two DB2 that are just there because why not?
This Ground-Work will however come in handy when investigating other options in the near future as it would apply to ERP Systems
and in my case SAP. Given that SAP by Design has a System wide Configuration and Database Clients that hold the Master Data
the next steps are to build a Setup that accomodates the various Stages when implementing a SAP System. There isn't a one fits all
Approach, but splitting up Master Data between Impelemtation Tasks such as Development of Programs, Data Imports, Testing, Training, Quality
makes a lot of sense, if the Endresult should be a painlessly running Production Environment that is systainable in the long run.
Once this nasty Cold subsides, I get on to it.
SAP Global Template
To me that is rather simple. If I do a Roll-Out into another Country and need to Apply a Configuration that has a Global Impact
then the "Global Template" wasn't designed all the way through. After all, a Configuration Change requires testing and if a Change
has a Global Impact then the Global Business Community needs to test the Change.
In my Optinion, that can't be the Meaning or Intent of a "Global Template".
But perhaps someone out here can educate me?
SAP Training
Whilst this appears to be simple, it always raises Questions and I can use this for Classes or Show Case.
Obviously the Freight Companies use far more Complex Calculations, after all, that is their Business Model,
but this Routine captures the Essentials and can of course be extended.
Also a nice Example on how to Program APIs. Add a Token to identify the Customer and you are in Cloud Service when you store every successful Return.
So, click to see what I did.
Database
Whilst it was reasonably clear that Field Types would need to be adjusted, the same applies to Field and Table Names as well.
Further, dealing with BLOBS turned out to be an issue due to the SQL Statement length and thus Prepared Statements need to be used.
Obviously investigating the various litte "Features" and finding a Solution took time.
I don't think that every eventuality has been addressed, but the way the Code is structured, any additions can be implemented with relative ease.
For reference purposes, every adjustment is captured in a conversion Table.
OBVIOUSLY this is an EXAMPlE not designed to be working in a Production environment, but it would be more than just a "Starting Point".
Also incorporated is BLOCKING of INSERT STATEMENTS where possible. Due to the length of BLOBS, these are INSERTED in individual Statements.
The BLOCKSIZE can be adjusted quite easily. I went for 100 at a time for no other reason than liking the Number and having Memory concerns.
Anyway, celebrate the Result. I also added one just to Copy from one MariaDB to another MariaDB.
One more, but I am not sure, if I need to add DB2 to DB2.Copy from DB2 to MariaDB.
Due to popular Demand, I added DB2 to DB2 copy Example.
Database
to DB2. It works, but I need to do a few sanity checks before posting it here. Likely be some time tomorrow.
NOTE: I wanted a LIVE update between Databases. Not a BATCH LOAD.
Initial Sanity Check was a good idea. DB2 doesn't like everything that MariaDB likes and vice versa.
Not only Field Types (that was a given), but also Field Names. Thus a conversion Table needs to be added.
Also, Memory usage becomes an Issue. Rows and Connections need to be freed. My Test was for a few million Records.
BUT, still those little Things aside, works like a Dream.
Database
After all, we want some Statistics. So lets change that.
ColumnStore Chart Example
I think that rounds up the small Excercise of ColumnStore Exploration.
By the Way, did you notice that all the Software is Open-Source and except for your Laptop or PC,
there are no $ Signs involved!. Just a bit of Patience.
Database
Whist the ColumnStore accepts simple INSERT INTO TABLE VALUES(1); although slower than a Snail,
it completely ignores INSERT INTO TABLE VALUES(1),(2),(3),....(n);
without any Error. Instead just Bulk Load Data into a InnoDB Table (since your Application has that anyways)
and then do a INSERT INTO TARGETTABLE SELECT * FROM SOURCETABLE.
Ultimately that is what you would do anyways in order to get Data for analytical purposes, which is the Goal after all.
Now with that crazy Landscape of 4 MariaDB Instances, it might be time to think about Replication.
Database
Next little Project is a Sample on how to Copy Data from MariaDB to DB2.
HOWEVER, I just came across MariaDB-Column-Store
Worth an Investigation
ColumnStore Load Example
Database
Fair Question
Assume you use different Software which in turn use different Databases
- Sales System
- Financial System
- Warehouse System
- etc
Running all on the same Machine saves Money (load permitting) vs individual Systems
Plus, the People you need don't need to nurse multiple Operating Systems.
or at least not that many.
I will add a Section on Data Access Setup with Program examples shortly.
That gets complex.
Ok, not pretty and first Version, but here are the Connectors.
Database
I did (and will share) different Connection Examples for MariaDB and DB2.
My only Query Statement is listing the Database Tables, thus nothing fance for now, BUT the Performace difference is STUNNING.
Clear Winner regardless if PDO, Native or ODBC is MariaDB by a margin. To be fair, DB2 might be better with
more Memory or perhaps under heavy Load or whatever. Thus I won't say my "Experiment" is conclusive.
I also measured the Time including making the Connection and just executing the Query.
Here a rather stark difference can be observed as well.
Once I have everything documented, I will write a short routine to copy Data from MariaDB to DB2 and maybe vice versa.
Please keep in mind that with the Setup I have now, I can connect to any Instance (3 MariaDB, 2 DB2) I choose and go forth and back between them.
A fair warning as to the Setup: I work on Linux and no matter what, something is always missing, thus be prepared to hunt down missing Packages.
Because Web-Sites do vanish, I won't link to them only to find out that 6 Month from now I get 404 Errors.
Yay!! MariaDB Multi Instance Documentation in Version 1
DB2 Multi Instance Documentation in Version 1
Database
Doing quite a bit of research, I found a few hints on how to add Instances.
A few Caveats like annoying User Profiles on my Logon Screen and other "features", but - fine.
Major Headache was figuring out how that would now all work. From Autostart via SYSTEMD to Database Connection
to the individual Instances. Turns out, you could get lucky or you could chew Finger Nails.
Feels like I have no Fingers left. I will make a detailed Post on how to get this running.
What might be obvious (if you wear the right Glasses) and what might not be so obvious.
Needless to say, I finally got it all up and running and considering that I am NOT a Database Admin, I am a little proud
having seen so many rather frustrated Comments during my R&D.
Stay tuned, it's Weekend, but I have it posted soon.
Database
Where I faced a MAJOR issue was getting DB2 and PDO running in PHP. Spending a LOT of time doing all kinds of stuff since I didn't
even get a proper Error Message other than "driver not working", I finally found a setting for PHP.INI
ibm_db2.instance_name=db2inst1
Done!
JDBC, ODBC, DB2 and PDO all running like young foal.
Checking a JDBC Connection with
java com.ibm.db2.jcc.DB2Jcc -user db2inst2 -password xxxxxx -url jdbc:db2://127.0.0.1:50000/testdb2
Database
Once a Transport is released to the next System (Quality Assurance), all the Data in the QA System is replaced with the new ones. Caveat: SAP may not be able to warn you, if you delete something you shouldn't delete. If you remove a Storrage location that holds Inventory, I wouldn't bet my Life on that as a good Idea. Transports are not Master Data whereas a Client Copy is Master Data.
Another reason for Caution is potential delays. You could be in some Transaction in a QA System whilst a Transport is being applied and what you see may not be what is actually in the System. I had that happening a few times. Programs and related Items (Database changes, etc) are Transported as well.
Database
Time
Since every User is also a User of the Database, that takes care of the Database Logging, but sometimes
you just want to know the User-Id of someone locking a Record or to resolve Problems.
That said, a feature has been added activating SQL Logging on Database Level.
Sure, that can be done from a Command Line, but why not have it conviniently placed in the Application itself?
Time
Now I can return to Development work. I went through the Process of creating the "Modal" effect.
I prefer the "manual" one over the standard as it allows more flexibility.
Not that it matters all that much, but it is a nice Design touch.
Time
New Log-In with New INI File is already working too.
The SQL Statements will take a while.
Web Crawler
It had a little Hick-Up that got fixed too.
I started this not only as a Web Crawer, but also Document Index tool.
The Database looks fine at first glance, but needs some Validation.
MariaDB
. Moving from a single Instance to a Multi one shouldn't take long as only a Choice of Host needs to be presented to the User at Logon, BUT whilst I am on it, I might as well modify the Database Tables to include a Client Number. Technically the Table Change is no Problem. SHOW TABLES will provide a nice List and ALTER TABLE <table name> ADD COLUMN ClientId INT(3) FIRST
will do the change, followed by an UPDATE <table name> SET ClientId=000Caveat is changing the PHP Code to ammend the SQL Statements to Include the new Field.
Works like a Charm and will come in very Handy as a Tool for future Projects.
Lesson learned: ChatGPT is a help, but far from perfect.
Much better than just Screen Recordings. With a few Animations, it could be captivating and much less boring.
People have put a lot of work into this Gadget. It is my On and Off Drive By whenever I have time on my Hands.
Background
Years ago, my wife I I were renting out a vacation home and we made Information available on the Net.
We did that in the Languages we knew. English, French, German. The Text was stored in a Database and pulled according to User's choice. This was Hosted on a Windows Server with Access as Database. Not very fancy, but it worked. That private "Fun" led to the Development of Web/IT on the AS/400, which was essentially an Integration of DB/2 into the then new Web-Server on the AS/400.
Since my move from Windows to Linux, I have maintained an Apache Server and MySQL Database on my Laptop. Not only to Maintain my Web-Site, but also to Store work related Information such as SAP Table and Transaction Code References which comes in handy when Offline.
So on this Date I decided to convert MySQL to MariaDB and to add some Caveat: Running multiple Instances of MariaDB.
Fun little Research Project to get that to work including phpMyAdmin, choosing the Instance, setting up Users and the like. I ended up with 3 Instances, maybe simply because of SAP, but the Setup can be further explored when keeping Cloud Services and Applications in mind.
Whilst it would work with one Instance, I can now write API's connecting to Apache on a different Port and simulate Access to a remote Service.