Proper Function giving Basic Syntax Error

[Jawspro]

Edit: New question in Create HSQL 2.3 User Function: PROPER ( Mixed Case ) moved to new topic. — MrProgrammer 2020-06-17 22:32 UTC

Hi. I'm back with more questions. I don't think it will come as any surprise to anyone but I have not coding experience. I am also new to LibreOffice. Formerly I used a Database system that included the coding for Proper and allowed to copy parts or all the previous record.

I have a problem at the beginning of the run. At the first line it tells me there is a "Basic Syntax Error". And I get no further than that message.

Any help offered would be greatly appreciated, but like Denzel Washington said "Explain it to me like I'm a 5 year old".

Thank for any help you all can offer.
Jodi

[Sliderule]

Your above question, is a new topic from the use of PROPER function, therefore, start a new forum topic and be sure to identify the database back-end you are using, AND, exactly what you are doing when you received Basic Syntax Error" .

Edit: Moved as suggested, Sliderule

Additionally, you say you are new to LibreOffice, yet, you indicate you are using OpenOffice 4.1.5, please be specific of your Base environment.

Sliderule

[Jawspro]

Thanks for the heads up. Question: Why don't the writers of LibreOffice Base include the functions for copying the previous record and the utilization of the proper caps in their coding of a new release? I cannot be the only person who is frustrated by these two omissions.

[Sliderule]

I'll repeat myself again:

Your above question, is a new topic from the use of PROPER function, therefore, start a new forum topic and be sure to identify the database back-end you are using, AND, exactly what you are doing when you received "Basic Syntax Error" .

Sliderule

[Jawspro]

I was given the following information from a very kind LibreOffice user. The problem I am having is from the beginning a "BASIC syntax error" comes up and I do not know what that means. When I remove the Sub it looks for the Sub. I know nothing about program language and very little about Base.

Any help would be great. Thank you.

Also, I have seen that some other people have had questions on the Copy Function [parts of one record to another] and the use of Proper Function. Why are these functions not included when writing the new code for BASE? Just from looking at the topics for Beginners I see others who have similar questions.

Code: Select all

Sub CREATE FUNCTION PROPER (INPUTSTRING VARCHAR(32000))
   RETURNS VARCHAR(32000)
   LANGUAGE SQL
   NOT DETERMINISTIC
   CONTAINS SQL
   -- Be sure to return a NULL value if input is NULL
   RETURNS NULL ON NULL INPUT
   BEGIN ATOMIC
      DECLARE PROPERCASE VARCHAR(32000);
      -- Bring back all characters as lower CASE
      SET PROPERCASE = LOWER(INPUTSTRING);
      -- When PROPERCASE is blank, no need to proceed so end it here
      IF PROPERCASE = '' THEN
         RETURN PROPERCASE;
      END IF;
      -- Use UNNEST, and REGEXP_SUBSTRING_ARRAY to bring back unique array of
      -- NOT [^\p{javaLowerCase}] followed by [\p{javaLowerCase}]
      FOR
          SELECT DISTINCT REG_ARRAY as REG_CHAR FROM UNNEST(REGEXP_SUBSTRING_ARRAY(PROPERCASE,'[^\p{javaLowerCase}][\p{javaLowerCase}]')) AS T(REG_ARRAY)
      DO
         SET PROPERCASE = REPLACE(PROPERCASE,REG_CHAR,UPPER(REG_CHAR));
      END FOR;
      -- Make sure first character is UPPER CASE
      SET PROPERCASE = UPPER(SUBSTR(PROPERCASE,1,1)) || SUBSTR(PROPERCASE,2);
   RETURN PROPERCASE;
END Sub

[RusselB]

Suggest you read Sliderule's post regarding the custom PROPER function, which you can find here

[robleyd]

It looks like Jawspro is trying to use Sliderule's function from the linked topic; note that the linked topic has the warning "for use with HSQL database back-end ( starting with HSQL Version 2.3 ) . . . BUT NOT HSQL Version 1.8 . . . NOR the Embedded Version of HSQL"

If you are using the embedded database with LibreOffice, either HSQL or Firebird - about which you haven't told us - then you can expect it not to work.

[Sliderule]

Instructions to add this user defined function to your version of HSQL 2.3 back-end, and, this needs to be done, only once:

1. Open your OpenOffice / LibreOffice Base file ( *.odb )
2. Click on the Tables icon on the left
3. From the Menu: Tools -> SQL...
4. In the Command to execute box, copy and paste the following code:
   

   Code: Select all

   CREATE FUNCTION PROPER (INPUTSTRING VARCHAR(32000)) 
      RETURNS VARCHAR(32000) 
      LANGUAGE SQL 
      NOT DETERMINISTIC 
      CONTAINS SQL 
      -- Be sure to return a NULL value if input is NULL 
      RETURNS NULL ON NULL INPUT 
      BEGIN ATOMIC 
         DECLARE PROPERCASE VARCHAR(32000); 
         -- Bring back all characters as lower CASE 
         SET PROPERCASE = LOWER(INPUTSTRING); 
         -- When PROPERCASE is blank, no need to proceed so end it here
         IF PROPERCASE = '' THEN 
            RETURN PROPERCASE; 
         END IF; 
         -- Use UNNEST, and REGEXP_SUBSTRING_ARRAY to bring back unique array of 
         -- NOT [^\p{javaLowerCase}] followed by [\p{javaLowerCase}]
         FOR 
             SELECT DISTINCT REG_ARRAY as REG_CHAR FROM UNNEST(REGEXP_SUBSTRING_ARRAY(PROPERCASE,'[^\p{javaLowerCase}][\p{javaLowerCase}]')) AS T(REG_ARRAY) 
         DO 
            SET PROPERCASE = REPLACE(PROPERCASE,REG_CHAR,UPPER(REG_CHAR)); 
         END FOR; 
         -- Make sure first character is UPPER CASE 
         SET PROPERCASE = UPPER(SUBSTR(PROPERCASE,1,1)) || SUBSTR(PROPERCASE,2); 
      RETURN PROPERCASE; 
   END
   

5. Press the Execute button
6. After it runs ( very quickly ) . . . press the Close button
7. You may now use the function . . . PROPER, for example, with any SELECT or UPDATE statement

The above is NOT a OpenOffice / LibreOffice Basic macro. It is HSQL user defined Function ( CREATE FUNCTION PROPER (INPUTSTRING VARCHAR(32000)) ).

Therefore, it is able to be used with any programme using HSQL ( Version 2.X ) as a database back-end, including but not limited to OpenOffice / LibreOffice Base.