Hestia Control Panel

This article describes some detail I discovered when trying to set up Hestia Control Panel (HestiaCP) on an Ubuntu 18.04 VPS I have. The Ubuntu VPS has 1 Gb of memory, and 20Gb HDD.

I had a battle setting up private name servers for my domain name but eventually overcame the  issues.

Initially I tried without going to the documentation, as I found Hestia fairly intuitive.

In this article I describe solving the name server issues.

Set Up Your Package

As with all hosting panel, you need to first set up a Hosting Package.

Then add your ns1 and ns2 nameservers for your domain.

Set Name Servers in Package

In the Web listing, ensure the DNS template shows  child-ns

DNS Template

Also check that the web site is set to use the child-ns DNS Template

Rebuild your DNS Records

You can also use handy Rebuild All if you make a change to the DNS Template.

Install WordPress

You can also use the inbuilt feature to install WordPress. However this will only install WP to the main public_html folder, so be careful not to clobber any other pages you might have set up.

Go to the Edit form for the website, and the button appears as shown here.

Edit and Run Kotlin

KOTLIN is a recent JAVA-like programming language developed by JetBrains. See more at the language site here.

Below is a small snippet of editable and runable Kotlin embedded in this blog post. For this, I use a small WordPress plugin “Kotlin Playground”

Try changing the string values enclosed in ”  ” and click the small green arrow on the right side.
 note: I found I had to disable a privacy protection extension in my browser for the sample code below to show.. 

by JG and kotlin.

Using the Dummy Temp file tf

(a tip for Clarion programmers using MSSQL).

I define my dummy files with the name  tf (indicating a TempFile and quick to type and easy to find) in my local data in any procedure/function where I need it.

I set each data column as a CSTRING of at least the max size I might need for any data coming back from the SQL-Server. The number of columns I define varies as needed and can easily be changed when more are needed.

There is NO actual table needed in the Server Server database to match this dummy table, so long as I use the '/TURBOSQL = TRUE'  MSSQL driver switch. It is just a local table buffer (within the procedure).

I take care of opening and closing the file (well, it's really only a file buffer that gets created and instantiated with the OPEN() command).

Here is a typical  declaration:-

tf     file,driver('mssql', '/TURBOSQL = TRUE'), PRE(tf),owner( TblOwner )
record      record
f1    cstring(201)  
f2    cstring(201)  
                     end
         end

I also put my sql command into the local cstring-

SQLstr      CSTRING(801)

Whilst this is not absolutely necessary, it makes things neater. If I wanted to retrieve a couple of fields, say using

SELECT lname, fname from Client where sysid = 1234

So, I could do

clear(tf)
tf{prop:sql}= 'SELECT lname, fname from Client where sysid = 1234'

but I like to use the SQLstr variable....

clear(tf)
SQLstr =  'SELECT lname, fname from Client where sysid = 1234'
tf{prop:sql}= SQLstr

Now, the above shows a simplified version of what is actually used in the source code. Reason being, to prevent any SQL injection or user nastiness. That is the subject of a whole other  future  article.

Clarion for Development

I use mainly Clarion For Windows to develop my Win32 based software.

It is a great programming tool and great language. One bonus is that you can include other programming languages in your work. I have used C++ code, but you can also include Assembler, and Modular-2.

Clarion For Windows is developed by Softvelocity

For data storage, I use Microsoft SQLServer. This is a reliable and fast database system that suits my work very well. Clarion provides drivers for easy access to many database types, including MSSQL and ODBC.

For my install and update programs, I build these all with SetupBuilder from Lindersoft. They make it so easy and offer such a great tool.

John Griffiths

SQL is Faster

SQL is Faster: Recently I have been optimising some reports by using dummy temporary tables from within my programs.

About 14 years ago, I converted a large business system that was using ISAM based tables, to SQL. One report they were running daily from 30 minutes to less than 10 seconds to produce the needed data. Happy customers. They thought I was magic, but it was all due to the switch to using SQL as the database engine.

If you are still programming and using ISAM files for data tables, consider switching to SQL. I use Microsoft SQLServer. It just works so well.

John

Binary Coded Decimal

Previously, whenever I saw BCD I have always seen it as Binary Coded Decimal.

But tonight, during dinner, I now know it really means “Blue Cheese Dressing”. Yep, the salad was great!

John.

Clarionet in Clarion v6.1

RTF Control in Clarionet.

I have a client who has been running a program across a WAN using Clarionet. System was written several years ago in Clarion for Windows 6.1 and has not been modified for several years.

Recently he wanted an additional feature so that the system would generate some documents.
I was apprehensive in making the changes so built a small additional standa alone program to test with Clarionet. The programs are all Legacy template initially.

I needed to add a window with an RTF text window so the system could open a template .RTF document, and replace some tokens, then produce a print preview of the letter at the client side PC.

I knew that the Clarion for Windows RTF control was not going to behave in a Clarionet screen, so this had to be built to run without showing the RTF window and without any Clarionet code. By starting with a generated procedure, I then placed several OMIT statements to exclude any Clarionet generated calls. Next I added all my INIT, Document Open and KILL statements to right after the OPEN(Window) statement. Then I did the token replacements and saved the finished document as an RTF file.

Back in the calling procedure, I then passed the document’s filename to a report procedure, and placed the RTF file into a detail/text (rtf) control. This report then generated and the preview was auto-magically transferred to the client side PC. Done.

Now that I had got it working in a stand alone EXE, and it all worked OK, I then moved it into the trusty DLL written about 6 years ago. This worked fine and nothing got broken.

John Griffiths

terabytes bite

Oh Boy this hurts…

tbx2

 

As an older computer user, this blows my mind.

And I know from talking with many friends, this deal sounds beyond comprehension.
Makes me wonder where things will be in 5 years.
My first PC was a TRS-80 with 16K of ram and an external cassette sound recorder for more permanent storage. I remember I paid over $2000 for it.

Get the drive at frys.com

John

Remote MS-SQL2005 Connection Problem

I have a program I am trying to get ready for potential users to download and test. This is a program developed in Clarion For Windows (6.3). The testing database is a MS-SQL2005 system which is hosted on a shared server at discountasp.net and the server is located in Los Angeles. My users will most likely be located in Australia.

The problem I saw was the initial time it takes to make a connection to the remote database, and then a further delay with each Table I was opening. These turned out to be two separate issues.

Initial Connection:

The time taken to establish the initial connection was in the order of 20+ seconds. The connection was being attempted before the main APPFrame screen was being displayed. So expecting someone to start a program and wait 20+ seconds before the first screen response was not going to work. This lead me to build a separate “Starter” program with just one screen that received staged updates from the main program, and then once the AppFrame opened, the “Starter” program simply closed, or displayed any error applicable. The Starter program also had a Seconds Elapsed display and a notice that they may need to wait 20 seconds to connect to the database.

But why was this initial connection taking so long? I could fire up MS-Studio Express and connect in about 4 seconds. It was only the Clarion program that was very slow to connect. This I would research further and eventually solve. See below.

Slow File Opening:

The time taken to open a Browse/List screen was also slow. It was taking about 5 seconds per table to open. Again, this is just too slow. I solved this by adding the Driver switches /FASTCOLUMNFETCH = TRUE to most tables. That has helped. I also set the program to Keep Files Open and I opened a few of the larger tables (those with more columns, not rows).

Testing:

After spending several days fiddling with the various Driver switches, I was resigned to the fact that it would take 20+ seconds to connect. I started looking for another closed SQL server that would be hosted in Australia. Then, along came Christmas and I took some time off from the development computer. I was relaxing out-doors and just for kicks, decided to try running the program from a 10″ netbook with an Atom processor and on a local wireless network. I started up the program, which worked just fine, and got connected to my Los Angeles database server in about 4 seconds. Could not believe my eyes! Looked at the data and yes, this was indeed coming from the LA database. This little netbook had no database stuff loaded, no MS-SQL and had very few programs installed. Basically it had IE and Firefox for travelling.

So, back to the development PC which could not be 20 seconds slower than the netbook.

I tried several things… Stop all running MS-Servers, Stop a bunch of system services that were running (pretty much anything that looked like a network service). Still I could not get any faster connection from the PC.

Next I remembered someone on the Clarion Database forum had mentioned SQL Native Client. This certainly was not installed on the netbook. But it was on my PC. I had another notebook PC laying around that did have some SQL stuff installed. It was running MSSQL2005 database. I tried running my program on that notebook and it also would connect in 4 seconds. The laptop also had MS SQL Native Client installed, albeit an older version of SQLNCLI.dll. OK, after backing up my newer SQLNCLI.dll I copied the older one from the Laptop to my Development PC. Testing the program again I was still taking 20+ seconds to connect. Then I renamed the file SQLNCLI.dll (located in \windows\system32 ) and tried again. This time I finally managed to connect in about 4 seconds. I put the original SQLNCLI.dll back and connection time again went out to 20+ seconds.

So there was a problem with the SQLNCLI DLL that was causing the problem. I researched this in several places on the internet and found one other report of this problem. But there was no resolution there.

Next I went to Control Panel, and un-installed SQL Native Client. Now I was back to 4 second connect times. OK, Let’s put the Native Client back in. So I downloaded the latest MSSQL2005 version( which turned out to contain the exact same DLL that I was having problems with, and now I was able to connect in about 4 seconds each time the test program started.

MS SQL Native Client (or a related setting) had been the problem all along. By removing and re-installing I had solved the major connection problem. But I had wasted about 4 days getting to that stage! Oh well, that’s computing.

John Griffiths

 

Executing SQL directly; no cursor

I was doing some extensive software testing in a program that talks to a MS-SQL2005 database. I detected that a table INSERT/UPDATE Trigger was seemingly not firing.

I scrutinised the trigger code and could see nothing wrong, and know that it has been functioning well for several years in other applications, using both MSSQL2000 and MSSQL2005.

So what was different in this program? I searched the web and did not find an answer.

I added some code to a test program to work around the issue. That is when I started catching this error message:

No Cursor Error

Here I was trying to CALL a stored procedure to update some balances. Again, the stored procedure has been working great for many years with no change. So, what was this message all about? Back to the web again and still no resolution.

That is when I dug deeper and added some more tests into a couple of other test programs I wrote, each of which tried different approaches to see what was really happening.

Strangely, some tested OK with no errors, and others threw the same error! So, what was the difference?

The Answer

The original error it seems is caught by the SQL Native Client ( or perhaps Microsoft Data Access Components (MDAC) ) and translated for the ODBC file drivers I was using. A translation (or miss-translation) is performed and a meaningless message is captured by the ODBC based drivers and passed back to my program as “Executing SQL directly; no cursor”.

Comparing the test programs I was working with pointed me to the answer. It was all to do with permissions! Nothing about “Executing SQL directly; no cursor”.

I found in some programs where I was using Application Role permissions to access the backend SQL, that the Application Role I was using did not have EXECUTE permission on the stored procedure.

Back into MS SQL Server Management Studio I went, and ran this command:

GRANT EXECUTE ON [dbo].[jg_AllBalances11] TO [LMGaus]

Now all my test programs began behaving as expected.

Also, my TRIGGER began working. The trigger was calling the stored procedure to do some of its work, and so now it too performed as expected.

Next time you see the

Executing SQL directly; no cursor

error, check all your permissions!

Hope this helps someone.

John Griffiths