lua pquery parameter passing inconsistent?

ronaldvr
Contributor

Hello, I am relatively new to Exasol, but something is happening that may be a bug or me doing something wrong (or having expectations that do not correspond to what actually should happen. I have a query as follows:

 CREATE TABLE DESTSCHEMA.TESTTABLE like GLOBAL."CamelCaseView"; 

This works as expected. Now in Lua I use the statement

 local create_success, create_result = pquery('CREATE TABLE ::st.::n2 like ::sc.::n1', {sc=source_schema, st=target_schema, n1=source_object_name, n2=target_object_name } ) 

And I get the error message:

 table: \"DESTSCHEMA\".\"DESTTABLE\" creation failedobject GLOBAL.CAMELCASEVIEW not found [line 1, column 78]" 

The pquery.result.statement_text gives:

 CREATE TABLE USER_RONALDVANRAAIJ.CAMELCASEVIEW like CDS_GLOBAL.CustomerAllSharedContactTouchpoints 

SO I though, perhaps I interpret the manual wrong, so I tried this:

 local create_success2, create_result2 = pquery('CREATE TABLE ::st.::n2 like ::sc.:n1', {sc=source_schema, st=target_schema, n1=source_object_name, n2=target_object_name } ) 

No Dice

The pquery.result.statement_text:

  CREATE TABLE DESTSCHEMA.CAMELCASEVIEW like GLOBAL.'CamelCaseView' 

Gives the error:

 syntax error, unexpected UNSIGNED_NUMERIC_LITERAL_, expecting END_OF_INPUT_ or ';' [line 1, column 88] 

 

And oh this:

local create_success2, create_result2 = pquery([[CREATE TABLE ::st.::n2 like ::sc."]]..source_object_name..[["]], {sc=source_schema, st=target_schema, n1=source_object_name, n2=target_object_name } ) 

Does work, but is not how you would expect pquery to behave.

7 REPLIES 7

exa-Chris
Community Manager
Community Manager

@Francesco if I remember right you played around with Lua a lot...

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

ronaldvr
Contributor

Hello Carsten, thanks for your reply, but (there would be a but 😎 ) either I misunderstand something, or there are some inconsistencies in the way this is handled. Let's take SQL Server as an example. Identifiers can there be either not delimited or delimited with [ ] ( SCHEMA.TABLE == [SCHEMA].[TABLE] and if identifiers have special characters like a space, they have to be delimited with [])

Now if I were to implement a pquery with the parameter substitution (which explicitly makes a difference between variable (:x) and identifier(::x) ) I would then do the following:

1: parameter is identifier (::x)

2: Is this parameter already delimited with [ and ] ?

3a: Yes: OK do nothing

3b: No: place [ and beginning and ] at end

4 substitute

So pquery("SELECT * FROM ::s.::t", {s="[SCHEMA]",t="TABLE") would result in the statement "SELECT * FROM [SCHEMA].[TABLE]")

Now, it very well may be I lack knowledge on how Exasol implements this, and that the " delimiter only works in case of camelcase? But that would be another 'inconsistency' this time in the SQL statement in that single quotes do not seem to have the same effect as double quotes (because this  CREATE TABLE DESTSCHEMA.CAMELCASEVIEW like GLOBAL.'CamelCaseView' (see above) did not work)? Or the documentation is insufficient in that here: https://docs.exasol.com/sql_references/basiclanguageelements.htm is only mentioned quotation marks should be used, and it does not specify that " are needed.

So could you please clarify this for me?

mwellbro
Xpert

Hi @ronaldvr ,

the double quote delimiter ( " ) is the standard way of delimiting identifiers, the example you mentioned with SQLServers "bracket" notation is SQLServer ( or Microsoft specific if you´d like to count Access ) specific.

"But that would be another 'inconsistency' this time in the SQL statement in that single quotes do not seem to have the same effect as double quotes(...)" => single quotes and double quotes have radically different meanings in SQL hence they do not behave the same way: 

- single quotes are used to enclose strings
- double quotes are used to enclose identifiers

You can use double quotes to enclose CamelCase,UPPERCASE,lowercase or StRaNgEcAsE , putting the identifier in double quotes tells your DB that the identifiers is to be stored exactly as you typed it in ( which can be much fun if your DB has an all UPPERCASE dictionary but you choose to enforce an exception because reasons 🙂  ).

Returning to your original error message: it states that GLOBAL.CAMELCASEVIEW was not found, which seems correct given that you used quoted identifiers in the previous stand alone SQL query.
I would expect that 

 local create_success, create_result = pquery('CREATE TABLE ::st.::n2 like ::sc.::n1', {sc=source_schema, st=target_schema, n1=source_object_name, n2=target_object_name } ) 

would have to be changed to 

 local create_success, create_result = pquery('CREATE TABLE ::st.::n2 like ::sc."::n1" ', {sc=source_schema, st=target_schema, n1=source_object_name, n2=target_object_name } ) 

and you would have to take care that n1 is cased / passed to lua in CamelCase , just like it apparently was created with.

Hope this clarifies things a bit ?

Cheers,
Malte

ronaldvr
Contributor

Hi Malte,

Thank you for your reply, but the problem I have is more convoluted, and the reply you give does not explain everything I think. I did some more searching and I found this:https://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

and this:

https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers

Now the problem I have is that I have a table with columns that contain identifiers e.g.

SCHEMA_NAMETABLE_NAME
SCHEMA1CamelCaseTable
SCHEMA1cAmelcasetable

SCHEMA1

CAMELCASETABLE

SCHEMA1

"CamelCaseTable"

 

Now If I use the following statements to iterate:

local metaq_success, meta_result = pquery("SELECT * FROM METATABLE")

for i=1, #meta_result do

pquery( [[SELECT COLUMN_SCHEMA, COLUMN_table FROM sys.EXA_USER_COLUMNS WHERE COLUMN_SCHEMA = :s AND COLUMN_TABLE IN (:t) ]], {s=meta_result[i].SCHEMA_NAME,t=meta_result[i].TABLE_NAME)
pquery("SELECT * FROM ::s.::t", {s=meta_result[i].SCHEMA_NAME,t=meta_result[i].TABLE_NAME)

end

 

I am in trouble either way: I have no way to know in advance what I will get (camelcase or not), and using the quote or join function will not help me I think.

mwellbro
Xpert

Hi ronaldvr,

you should be fine if you lua-string-replace your meta_result[i].TABLE_NAME to get rid of all potentially present double-quotes and force double quotes in your lower pquery, no ?


Cheers,
Malte

ronaldvr
Contributor

Hello all,

 

I actually -since I need to iterate over many table names- created a function that returns a table with 3 values, not only properly quoted but also checked whether the table actually exists in that form:

function get_exa_id(idstring, objecttype, obj_schema ) 
/*Function to check if a view or table actually exists, and create a table of  identifier strings from an input string 
The function checks whether the identifier actually extists as a table or view and returns an array with the 3 types of string to be used in queries.
Input:
   idstring: string to be 'treated'
   objecttype: the objecttype to retrieve from sys
   obj_schema: where schema clause

Returns: A table/array with 3 entries
	.identfr: correct case with ""
	.quoted: correct case with ''
	.unquoted: correct case with no quotes
*/
	local identifiers={}

    if (string.sub(trim(idstring),1,1) == '"') then
        --strip: we will determine ourselves whether these are needed
        idstring=string.sub(trim(idstring), 2, -2)        
        addtolog("info","1: quotes stripped "..idstring)
    end
    if (objecttype=='table') then
        --treat tables and views as similar
        --first try with allcaps
        local succes1, result1=pquery([[SELECT OBJECT_NAME FROM SYS.EXA_ALL_OBJECTS WHERE 
		(object_type = 'VIEW' OR object_type = 'TABLE') AND ROOT_TYPE='SCHEMA' AND ROOT_NAME=:s 
		AND OBJECT_NAME = ucase(:i)]], {i=idstring, s=obj_schema} )
        addtolog("info","1: Statement "..result1.statement_text)
        if succes1 then
            if (#result1 == 0) then 
                --not found retry with original case
                local succes2, result2=pquery([[SELECT OBJECT_NAME
                FROM SYS.EXA_ALL_OBJECTS
                WHERE (object_type = 'VIEW' OR object_type = 'TABLE')  AND ROOT_TYPE='SCHEMA' AND ROOT_NAME=:s 
                AND OBJECT_NAME = :i]], {i=idstring, s=obj_schema} )
                addtolog("info","1: Statement "..result2.statement_text)
                if succes2 then
                    if #result2 == 0 then
                        --Houston we have a problem
                        addtolog("fatal","Object: "..idstring.."not present in database")
                        return 2, {}
                    elseif (#result2==1) then
                    -- ding ding we have a winner
                        identifiers.identifr=[["]]..idstring..[["]]
                        identifiers.quoted=[[']]..idstring..[[']]
                        identifiers.unquoted=idstring
                        addtolog("info","Object: "..identifiers.identifr.."found in database")
                    else
                        --Huh? this should/could actually never happen of course, but anyway defensive programming 
                        addtolog("fatal","Object: "..idstring.."multiple times("..#result2..") present in database")
                        return 9, {}
                    end
                else
                    --Houston we have a problem
                    addtolog("fatal","Query error: "..result2.error_message)
                    return 9, {}
                end
            elseif (#result1==1) then
                -- ding ding we have a winner
                    identifiers.identifr=[["]]..string.upper(idstring)..[["]]
                    identifiers.quoted=[[']]..string.upper(idstring)..[[']]
                    identifiers.unquoted=string.upper(idstring)
                    addtolog("info","Object: "..identifiers.identifr.."found in database")
            else
                --Huh? this should/could actually never happen of course, but anyway defensive programming 
                addtolog("fatal","Object: "..idstring.."multiple times("..#result1..") present in database")
                return 9, {}
            end
        else
            --Houston we have a problem
            addtolog("fatal","Query error: "..result1.error_message)
            return 9, {}
        end
    end
    return 0,  identifiers
end

exa-Carsten
Moderator
Moderator

Hi @ronaldvr 

in order get proper, fully qualified identifiers, there is the join function in Lua. Also, the quote function is helpful to quote identifiers. Hence, I would probably implement your example the following way (as I understand, you want to create the new table like the old one, but not case sensitive):

 

local source = join(".", source_schema, quote(source_object_name))
local dest = join(".", target_schema, target_object_name)
local create_success, create_result = pquery([[CREATE TABLE ::st like ::sc]], {sc = source, st = dest})