--- lib/db/plugin_4d.lua
-- plugin_4d.lua
-- plugin_api.lua
-- if ffi.arch == "x64" then
-- 	local plugin_4d_x64 = require "plugin_4d_x64"
-- 	return plugin_4d_x64
-- end
--[[local arg = ...
if not arg then
  arg = "plugin_4d"
end
module(arg, package.seeall)
]] --
module(..., package.seeall)

local ffi = require "mffi"
-- require "table.new" -- do not work in athlon
local C = ffi.C
local util = require "util"
local json = require "json"
local dsql2 = require "db/db_sql2"
local dschema = require "db/db_schema"
local skip = util.skip
local c4d = util.c4d()
local useSetName -- sync database-rest4d.lua and plg4d.lua

require "public_types"
-- require "private_types"
require "plugin_api"
local unicode = require "unicode4d"
local date = require "dt"
local l = require"lang".l
local fromUnichar = unicode.fromUnichar
local toUnichar = unicode.toUnichar
-- local unicharLen = unicode.unicharLen
local fromUnistring = unicode.fromUnistring
local toUnistring = unicode.toUnistring
local peg = require "peg"
local dfld2 -- delay load = require "db/db_field2"

local printSql = false

local f
-- dbfix: f
local function fieldNameToNumber(fldName)
	local ret = f[tostring(fldName)] or f["_" .. tostring(fldName)] -- or try 4D cha -> _cha
	if not ret and type(fldName) == "string" then
		local pos = peg.find(fldName, ".")
		if pos > 0 then
			local fld1 = fieldNameToNumber(fldName:sub(1, pos - 1))
			local fld2 = fieldNameToNumber(fldName:sub(pos + 1))
			if fld1 and fld2 then
				ret = {fld1, fld2}
			end
		end
	end
	return ret
end

function constantNumber(constantName)
	if constantName == "_el_kmOutput" then
		return 1100005
	elseif constantName == "_el_kmModify" then
		return 1100004
	elseif constantName == "_el_kmBrowse" then
		return 1100003
	elseif constantName == "Into set" then
		return 1
	elseif constantName == "Into named selection" then
		return 2
	else
		return nil
	end
end

function showSql(val)
	if val == nil then
		printSql = true
	else
		printSql = val
	end
end

local clearVariableCount = 0
local function clearVariable(paVariable)
	clearVariableCount = clearVariableCount + 1
	-- if clearVariableCount % 10 == 0 then
	--	print("clearVariableCount: "..clearVariableCount)
	-- end
	-- util.printError("c4d.PA_ClearVariable(paVariable)")
	-- c4d.PA_ClearVariable(paVariable)
end

function isCompiled()
	local ret = c4d.PA_IsCompiled(1)
	if tonumber(ret) == 1 then
		return true
	end
	return false
end

--[[
function executeMethod(param)
	c4d.PA_ExecuteMethod(toUnistring(param))
end
]]

-- === Variables ===

function getString(paVariable)
	--[[local value = fromUnistring(c4d.PA_GetStringVariable(variable))
	local eb = ffi.newNoAnchor("EngineBlock")
	eb.fHandle = ffi.cast("PA_Handle", variable)
	eb.fUniString1.fLength = 0
	eb.fUniString1.fString = ffi.cast("PA_Unichar*", 0)
	eb.fUniString1.fReserved1 = 0
	eb.fUniString1.fReserved2 = 0
	c4d.Call4D( C.EX_VARIABLE_TO_STRING, eb )
	local value = fromUnistring(eb.fUniString1)
	]]
	local value = ""
	if paVariable.fType == C.eVK_Unistring then
		-- value = fromUnistring(paVariable.uValue.fString)
		value = peg.replace(fromUnistring(paVariable.uValue.fString), "\r", "\n")
	end
	return value
end

function getLong(paVariable)
	local value = 0
	if paVariable.fType == C.eVK_Longint then
		value = paVariable.uValue.fLongint
	end
	return value
end

local function getReal(paVariable)
	local value = 0.0
	if paVariable.fType == C.eVK_Real then
		value = paVariable.uValue.fReal
	end
	return value
end

local function getTime(paVariable)
	local value = 0
	if paVariable.fType == C.eVK_Time then
		value = paVariable.uValue.fTime
	end
	return value
end

local function getDate(paVariable)
	local y, m, d = 0, 0, 0
	if paVariable.fType == C.eVK_Date then
		y = paVariable.uValue.fDate.fYear
		m = paVariable.uValue.fDate.fMonth
		d = paVariable.uValue.fDate.fDay
	end
	return y, m, d
end

local function getBoolean(paVariable)
	local value = 0
	if paVariable.fType == C.eVK_Boolean then
		value = paVariable.uValue.fBoolean
	end
	return value == 1
end

--[[
	DOES not work with normal variables
function getVariableName(paVariable)
	local value = paVariable.uValue.fVariableDefinition.fName
	value = ffi.string(value)
	return "**-"..value.."-**" -- "**-"..fromUnichar(value).."-**" --fromUnichar(value) --value --toUnichar(value)
end
]]

function getPointerName(paPointer)
	local pointerToVariable = paPointer.uValue.fVariable.fName
	local strLen = pointerToVariable[0] -- first char is string length
	return ffi.string(pointerToVariable + 1, strLen) -- get from second, max 1+31 chars
end

local function executeFunction(param)
	local paVariable = c4d.PA_ExecuteFunction(toUnistring(param))
	return paVariable -- MUST use c4d.PA_ClearVariable(paVariable) in calling function
end

function applicationVersion()
	local paVariable = executeFunction("Application version")
	local ret = getString(paVariable)
	clearVariable(paVariable)
	return ret
end

local callMethod
do
	local functionId = {} -- create local func name cache tbl
	callMethod = function(method, ...)
		-- === Parameters ===
		local function setLongParam(param)
			local ret = ffi.newNoAnchorNoTrace("PA_Variable") -- prevent mffi trace
			c4d.PA_SetLongintVariable(ret, param)
			return ret
		end
		local function setRealParam(param)
			local ret = ffi.newNoAnchorNoTrace("PA_Variable") -- prevent mffi trace
			c4d.PA_SetRealVariable(ret, param)
			return ret
		end
		local function setStringParam(param)
			-- local ret = ffi.newNoAnchor("PA_Variable")
			local ret = ffi.newNoAnchorNoTrace("PA_Variable") -- prevent mffi trace infinite loop
			c4d.PA_SetStringVariable(ret, toUnistring(param))
			return ret
		end
		local function setDateParam(year, month, day)
			local ret = ffi.newNoAnchorNoTrace("PA_Variable") -- prevent mffi trace
			c4d.PA_SetDateVariable(ret, day, month, year)
			-- void PA_SetDateVariable( PA_Variable* variable, short day, short month, short year )
			return ret
		end
		local function setTimeParam(param)
			local ret = ffi.newNoAnchorNoTrace("PA_Variable") -- prevent mffi trace
			c4d.PA_SetTimeVariable(param)
			-- void PA_SetDateVariable( PA_Variable* variable, short day, short month, short year )
			return ret
		end
		local function setBooleanParam(param)
			local ret = ffi.newNoAnchorNoTrace("PA_Variable") -- prevent mffi trace
			if param then
				c4d.PA_SetBooleanVariable(ret, 1)
			else
				c4d.PA_SetBooleanVariable(ret, 0)
			end
			-- void PA_SetDateVariable( PA_Variable* variable, short day, short month, short year )
			return ret
		end
		-- void PA_SetTableFieldVariable( PA_Variable* variable, short table, short field )
		-- void PA_SetBlobVariable( PA_Variable* variable, void* blob, long len )
		-- void PA_SetBlobHandleVariable( PA_Variable* variable, PA_Handle hblob )
		-- void PA_SetPictureVariable( PA_Variable* variable, PA_Picture picture )

		if not c4d then
			util.printError(l "c4d is null")
			return nil
		end
		local arg = {...}
		local argCount = #arg
		-- local params = ffi.newNoAnchor("PA_Variable[?]", argCount)
		local params = ffi.newNoAnchorNoTrace("PA_Variable[?]", argCount) -- prevent mffi trace infinite loop
		for i, p in ipairs(arg) do
			local ptype = type(p)
			if ptype == "cdata" then
				params[i - 1] = p
			elseif ptype == "string" then
				params[i - 1] = setStringParam(p)
			elseif ptype == "number" then
				if math.floor(p) == p then
					params[i - 1] = setLongParam(p)
				else
					params[i - 1] = setRealParam(p)
				end
			elseif ptype == "boolean" then
				params[i - 1] = setBooleanParam(p)
			else
				util.printError(l "parameter type is not supported: " .. ptype)
				return nil
			end
		end
		local methodId = functionId[method]
		if not methodId then
			methodId = c4d.PA_GetMethodID(toUnichar(method))
			if methodId < 1 then
				util.printError(l " method was not found: " .. method)
				return nil
			end
			functionId[method] = methodId
			-- else
			--   print("cached method id: "..method..": "..methodId)
		end
		local paVariable = c4d.PA_ExecuteMethodByID(methodId, params, argCount)
		for i = 1, #arg do
			clearVariable(params[i - 1])
		end
		return paVariable
	end
end

function currentProcessNumber()
	return c4d.PA_GetCurrentProcessNumber()
end

function sleep(delayInTicks)
	-- local process = ...
	-- c4d.PA_PutProcessToSleep(process, delayInTicks)
	callMethodNoReturn("_lx_WAIT", delayInTicks)
end

function yield()
	c4d.PA_Yield()
end

function yieldAbsolute()
	c4d.PA_YieldAbsolute()
end

--- Structure Access
-- http:--sources.4d.com/trac/4d_4dpluginapi/wiki/XKU00034.HTM

function fieldStructureName(tableNum, fieldNum)
	local fieldName = ffi.newNoAnchor("PA_Unichar[32]")
	c4d.PA_GetFieldName(tableNum, fieldNum, fieldName)
	local ret = fromUnichar(fieldName)
	clearVariable(fieldName)
	return ret
end

function tableStructureName(tableNum)
	local tableName = ffi.newNoAnchor("PA_Unichar[32]")
	c4d.PA_GetTableName(tableNum, tableName)
	local ret = fromUnichar(tableName)
	clearVariable(tableName)
	return ret
end

do
	local tableCountCache
	function tableCount()
		if not tableCountCache then
			tableCountCache = c4d.PA_CountTables() -- plg.callMethodReturnLong("_tbl_ Count")
		end
		return tableCountCache
	end
end

do
	local fieldCountCache = {}
	function fieldCount(tableNum)
		if not fieldCountCache[tableNum] then
			fieldCountCache[tableNum] = c4d.PA_CountFields(tableNum)
		end
		return fieldCountCache[tableNum]
	end
end

--- Get variable
function getVariable(variable)
	return c4d.PA_GetVariable(toUnichar(variable))
end

function getPointer(paVariable)
	if paVariable.fType == C.eVK_Pointer then
		return paVariable.uValue.fPointer -- PA_Pointer
	end
	util.printError(l "parameter 1 was not a pointer to variable")
	return nil
end

function getPointerArray(variable)
	local paVariable = getVariable(variable)
	if paVariable.fType == C.eVK_ArrayPointer then
		return paVariable
	end
	util.printError(l "parameter 1 was not a pointer to variable")
	return nil
end

function getStringVariable(variable)
	return getString(c4d.PA_GetVariable(toUnichar(variable)))
end

function getLongVariable(variable)
	local paVariable = c4d.PA_GetVariable(toUnichar(variable))
	local value = getLong(paVariable)
	return value
end

function getRealVariable(variable)
	return getReal(c4d.PA_GetVariable(toUnichar(variable)))
end

function getTimeVariable(variable)
	return getTime(c4d.PA_GetVariable(toUnichar(variable)))
end

function getDateVariable(variable)
	return getDate(c4d.PA_GetVariable(toUnichar(variable))) -- returns 3 values
end

function getBooleanVariable(variable)
	return getBoolean(c4d.PA_GetVariable(toUnichar(variable)))
end

-- ===
function callMethodNoReturn(...)
	local paVariable = callMethod(...)
	clearVariable(paVariable)
end

function callMethodReturnString(...)
	local paVariable = callMethod(...)
	local ret = getString(paVariable)
	clearVariable(paVariable)
	return ret
end

function callMethodReturnLong(...)
	local paVariable = callMethod(...)
	local ret = getLong(paVariable)
	clearVariable(paVariable)
	return ret
end

function callMethodReturnReal(...)
	local paVariable = callMethod(...)
	local ret = getReal(paVariable)
	clearVariable(paVariable)
	return ret
end

function callMethodReturnTime(...)
	local paVariable = callMethod(...)
	local ret = getTime(paVariable)
	clearVariable(paVariable)
	return ret
end

function callMethodReturnDate(...)
	local paVariable = callMethod(...)
	local ret = getDate(paVariable)
	clearVariable(paVariable)
	return ret
end

function callMethodReturnBoolean(...)
	local paVariable = callMethod(...)
	local ret = getBoolean(paVariable)
	clearVariable(paVariable)
	return ret
end

--[[
function setLongVariable(varName, value)
	local var = getVariable(varName) -- prevent mffi trace
	c4d.PA_SetLongintVariable(var, value)
	return var
end
--]]

-- === Arrays ===

function getArrayType(ar)
	if ar.fType == C.eVK_ArrayLongint then
		return "long"
	elseif ar.fType == C.eVK_ArrayReal then
		return "real"
	elseif ar.fType == C.eVK_ArrayUnicode then
		return "string"
	elseif ar.fType == C.eVK_ArrayDate then
		return "date"
	elseif ar.fType == C.eVK_ArrayBoolean then
		return "boolean"
	elseif ar.fType == C.eVK_ArrayInteger then
		return "integer"
	elseif ar.fType == C.eVK_ArrayPointer then
		return "pointer"
	end
	util.printError(l "not supprted array type: " .. ar.fType)
	return nil
end

-- PA_GetPointerInArray
-- void PA_ResizeArray( PA_Variable *ar, long nb )
function getSizeOfArray(ar) -- PA_GetArrayNbElements
	if not ar then
		util.printError("ar-parameter is nil")
		return -1
	end
	if (ar.fType >= C.eVK_ArrayOfArray and ar.fType <= C.eVK_ArrayBoolean) or ar.fType == C.eVK_ArrayUnicode then
		return ar.uValue.fArray.fNbElements
	end
	return -1
end

function getArrayElement(ar, i)
	if ar.fType == C.eVK_ArrayLongint then
		return getArrayLongElement(ar, i)
	elseif ar.fType == C.eVK_ArrayReal then
		return getArrayRealElement(ar, i)
	elseif ar.fType == C.eVK_ArrayUnicode then
		return getArrayStringElement(ar, i)
	elseif ar.fType == C.eVK_ArrayDate then
		return getArrayDateElementStr(ar, i) -- getArrayDateElementYMD or getArrayDateElementLong ?
	elseif ar.fType == C.eVK_ArrayBoolean then
		return getArrayBooleanElement(ar, i)
	elseif ar.fType == C.eVK_ArrayInteger then
		return getArrayIntElement(ar, i)
	elseif ar.fType == C.eVK_ArrayPointer then
		return getArrayPointerElement(ar, i)
	end
	util.printError(l "not supprted array type: " .. ar.fType)
	return nil
end

function getArrayPointerElement(ar, i) -- long PA_GetLongintInArray( PA_Variable ar, long i )
	local value = nil
	if ar.fType == C.eVK_ArrayPointer and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( *(PointerBlock**) ar.uValue.fArray.fData ) [ i ];
		local arr = ffi.cast("PointerBlock**", ar.uValue.fArray.fData)[0]
		value = arr[i]
	end
	return value
end

function setArrayPointerElement(ar, i, value) -- long PA_GetLongintInArray( PA_Variable ar, long i )
	if ar.fType == C.eVK_ArrayPointer and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( *(PointerBlock**) ar.uValue.fArray.fData ) [ i ];
		local arr = ffi.cast("PointerBlock**", ar.uValue.fArray.fData)[0]
		arr[i] = value
	end
	return value
end

function getArrayIntElement(ar, i) -- long PA_GetLongintInArray( PA_Variable ar, long i )
	local value = 0
	if ar.fType == C.eVK_ArrayInteger and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( * (short**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("short**", ar.uValue.fArray.fData)[0]
		value = arr[i]
	end
	return value
end

function setArrayIntElement(ar, i, value) -- long PA_GetLongintInArray( PA_Variable ar, long i )
	if ar.fType == C.eVK_ArrayInteger and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( * (short**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("short**", ar.uValue.fArray.fData)[0]
		arr[i] = value
	end
	return value
end

function getArrayLongElement(ar, i) -- long PA_GetLongintInArray( PA_Variable ar, long i )
	local value = 0
	if ar.fType == C.eVK_ArrayLongint and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( * (long**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("long**", ar.uValue.fArray.fData)[0]
		value = arr[i]
	end
	return value
end

function setArrayLongintElement(ar, i, value)
	if ar.fType == C.eVK_ArrayLongint and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- ( * (long**) (ar.uValue.fArray.fData) )[i] = value;
		local arr = ffi.cast("long**", ar.uValue.fArray.fData)[0]
		arr[i] = value
	end
end

function getArrayRealElement(ar, i) -- double PA_GetRealInArray( PA_Variable ar, long i )
	local value = 0
	if ar.fType == C.eVK_ArrayReal and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( * (long**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("double**", ar.uValue.fArray.fData)[0]
		value = arr[i]
	end
	return value
end

function setArrayRealElement(ar, i, value) -- double PA_GetRealInArray( PA_Variable ar, long i )
	if ar.fType == C.eVK_ArrayReal and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( * (long**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("double**", ar.uValue.fArray.fData)[0]
		arr[i] = value
	end
	return value
end

local tabTrue = {0x01, 0x02, 0x04, 0x08, 0x10, 0x20, 0x40, 0x80}
-- local tabFalse= { 0xFE, 0xFD, 0xFB, 0xF7, 0xEF, 0xDF, 0xBF, 0x7F }
local function GetBitInTable(bitTable, index)
	local a = bitTable + bit.rshift(index, 3) -- a[0] == *( bitTable + ( index >> 3 )
	local b = tabTrue[bit.band(index, 7) + 1] -- tabTrue[ index & 7 ], +1 because Lua index starts at 1
	return bit.band(a[0], b) ~= 0
	--[[static char GetBitInTable( unsigned char *bitTable, long index)
	{
		return (char) ( ( *( bitTable + ( index >> 3 ) ) & tabTrue[ index & 7 ] ) ? 1 : 0 );
	}
	]]
end

function getArrayBooleanElement(ar, i) -- double PA_GetBooleanInArray( PA_Variable ar, long i )
	local value = false
	if ar.fType == C.eVK_ArrayBoolean and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- value = ( * (unsigned char**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("unsigned char**", ar.uValue.fArray.fData)[0]
		value = GetBitInTable(arr, i) -- c4d.GetBitInTable(arr, i)
	end
	return value
end

function getArrayStringElement(ar, i) -- PA_GetStringInArray( arr, i )
	local value = ""
	if ar.fType == C.eVK_ArrayUnicode and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- str = ( * (PA_Unistring**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("PA_Unistring**", ar.uValue.fArray.fData)[0]
		-- pointer to start of array == arr[0] == * (PA_Unistring**)
		-- see: http:--luajit.org/ext_ffi_tutorial.html, list of common C idioms and their translation to the LuaJIT FFI:
		-- print("PA_Unistring"..tostring(arr[i]))
		-- value = fromUnistring(arr[i])
		value = peg.replace(fromUnistring(arr[i]), "\r", "\n")
	end
	return value
end

function setArrayStringElement(ar, i, value) -- PA_GetStringInArray( arr, i )
	if ar.fType == C.eVK_ArrayUnicode and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- str = ( * (PA_Unistring**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("PA_Unistring**", ar.uValue.fArray.fData)[0]
		-- pointer to start of array == arr[0] == * (PA_Unistring**)
		-- see: http:--luajit.org/ext_ffi_tutorial.html, list of common C idioms and their translation to the LuaJIT FFI:
		-- print("PA_Unistring"..tostring(arr[i]))
		arr[i] = toUnistring(value)
	end
	return value
end

function getArrayDateElementYMD(ar, i) -- void PA_GetDateInArray( PA_Variable ar, long i, short* day, short* month, short* year )
	if ar.fType == C.eVK_ArrayDate and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- ( * (PA_Date**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("PA_Date**", ar.uValue.fArray.fData)[0]
		return arr[i].fYear, arr[i].fMonth, arr[i].fDay
	end
	util.printError(l("date array index '%s' is invalid", tostring(i)))
	return 0, 0, 0
end

function getArrayDateElementStr(ar, i)
	local y, m, d = getArrayDateElementYMD(ar, i)
	local dateStr = dsql2.dateYmdToSqlString(y, m, d)
	return dateStr
end

function setArrayDateElementYMD(ar, i, y, m, d)
	if ar.fType == C.eVK_ArrayDate and ar.uValue.fArray.fData and i >= 0 and i <= ar.uValue.fArray.fNbElements then
		-- date = ( * (PA_Date**) (ar.uValue.fArray.fData) )[i];
		local arr = ffi.cast("PA_Date**", ar.uValue.fArray.fData)[0]
		arr[i].fYear = y
		arr[i].fMonth = m
		arr[i].fDay = d
	end
end

function setArrayDateElementStr(ar, i, dateStr)
	local y = tonumber(dateStr:sub(1, 4)) -- :sub is faster than match
	local m = tonumber(dateStr:sub(6, 7))
	local d = tonumber(dateStr:sub(9, 10))
	setArrayDateElementYMD(ar, i, y, m, d)
end

--- Selections
function recordsInSelection(tbl)
	return c4d.PA_RecordsInSelection(tbl)
end

function recordsInTable(tbl)
	return c4d.PA_RecordsInTable(tbl)
end

function deleteSelection(tbl)
	local err = l "plugin_4d deleteSelection() has not been implemented, table: " .. tostring(tbl)
	print(err)
	-- callMethodNoReturn("_sel DELETE", tbl)
	return err
end

local queryParamCount = 0
local orderParamCount = 0
local queryBySql
do
	local opArray, fldArray, compArray, valueArray, executeArray, nameArray -- ,arrArray
	local orderFldArray, orderDirArray
	local maxQueryParamCount = 1000
	local function queryInit()
		--[[
		ARRAY TEXT(_lx_atQueryArrayParam;$1)
		ARRAY TEXT(_lx_atQueryOpParam;$1)
		ARRAY LONGINT(_lx_alQueryFldParam;$1)
		ARRAY TEXT(_lx_atQueryCompParam;$1)
		ARRAY TEXT(_lx_atQueryValueParam;$1)

		ARRAY LONGINT(_lx_alOrderFldParam;$1)
		ARRAY LONGINT(_lx_alOrderDirParam;$1)
		]]
		callMethodNoReturn("_lx_QUERY_PARAM_RESIZE", maxQueryParamCount)
		-- we MUST get pointers to arrays again after call to _lx_QUERY_PARAM_RESIZE
		opArray = getVariable("_lx_atQueryOpParam")
		fldArray = getVariable("_lx_alQueryFldParam")
		compArray = getVariable("_lx_atQueryCompParam")
		valueArray = getVariable("_lx_atQueryValueParam")
		-- arrArray = getVariable("_lx_atQueryArrayParam")
		orderFldArray = getVariable("_lx_alOrderFldParam")
		orderDirArray = getVariable("_lx_alOrderDirParam")
		executeArray = getVariable("_lx_atQueryExecuteParam")
		nameArray = getVariable("_lx_atQueryNameParam")
	end

	function query(operator, fieldNum, comparison, value)
		print("query", operator, fieldNum, comparison, json.toJson(value))
		if not opArray then
			queryInit()
		end
		if operator == "" then
			queryParamCount = 0
			orderParamCount = 0
		end
		queryParamCount = queryParamCount + 1
		if queryParamCount > maxQueryParamCount then
			maxQueryParamCount = queryParamCount
			callMethodNoReturn("_lx_QUERY_PARAM_RESIZE", maxQueryParamCount)
		end

		local i = queryParamCount
		if operator == "and" then
			operator = "&"
		elseif operator == "or" then
			operator = "|"
		end
		if comparison == "<>" then
			comparison = "#"
		end

		if type(value) == "table" then
			if value.y and value.m and value.d then -- date table
				value = date.ymdToDateString(value.y, value.m, value.d)
			else
				local arrayOperator
				if comparison == "in" then
					arrayOperator = "|"
					comparison = "="
				elseif comparison == "not in" then
					arrayOperator = "&"
					comparison = "#"
				end
				-- recursive calls
				queryParamCount = queryParamCount - 1
				for j, val in ipairs(value) do
					if j == 1 then
						query(operator, fieldNum, comparison, val)
					else
						query(arrayOperator, fieldNum, comparison, val)
					end
				end
				return
				--[[
				value = "query array"
				callMethodNoReturn("_lx_QUERY_PARAM_RESIZE", -(#value))
				arrArray = getVariable("_lx_atQueryArrayParam")
				for j,val in ipairs(value) do
					setArrayStringElement(arrArray, j, tostring(val))
				end
				]]
				-- util.printError(l"Query value of type table can be only date")
				-- queryParamCount = queryParamCount-1
			end
		end
		setArrayStringElement(opArray, i, operator)
		setArrayLongintElement(fldArray, i, fieldNum)
		setArrayStringElement(compArray, i, comparison)
		setArrayStringElement(valueArray, i, tostring(value))
		setArrayStringElement(executeArray, i, "")
	end

	function orderBy(fieldNum, asc)
		if not opArray then
			queryInit()
		end
		orderParamCount = orderParamCount + 1
		if orderParamCount > maxQueryParamCount then
			maxQueryParamCount = orderParamCount
			callMethodNoReturn("_lx_QUERY_PARAM_RESIZE", maxQueryParamCount)
		end
		local i = orderParamCount
		if asc == ">" then
			setArrayLongintElement(orderDirArray, i, 1)
		elseif asc == "<" then
			setArrayLongintElement(orderDirArray, i, -1)
		else
			local errTxt = l "Order operator must be > or <"
			print(errTxt) -- util.printError(errTxt)
			return util.addError(nil, errTxt)
		end
		setArrayLongintElement(orderFldArray, i, fieldNum)
	end

	queryBySql = function(sql, fieldNum)
		-- query by sql
		-- print("plg query by sql: "..sql, json.toJsonRaw(fieldNum))
		if not opArray then
			queryInit()
		end
		if #fieldNum > maxQueryParamCount then
			maxQueryParamCount = #fieldNum
			callMethodNoReturn("_lx_QUERY_PARAM_RESIZE", maxQueryParamCount)
		end

		local query = dsql2.sqlQueryWherePart(sql)
		-- print("plg query by sql query: "..query)
		setArrayStringElement(valueArray, 1, query)
		setArrayStringElement(executeArray, 1, "")

		local firstFldNum = fieldNameToNumber(fieldNum[1])
		if type(firstFldNum) ~= "number" or firstFldNum == 0 then
			local errTxt = l "QuerySql error, first field number is not valid: " .. tostring(firstFldNum) .. ", sql: " .. sql
			print(errTxt) -- util.printError(errTxt)
			return util.addError(nil, errTxt)
		end
		local err = callMethodReturnLong("_lx_QuerySql", firstFldNum)
		if err ~= 0 then
			local errTxt = getStringVariable("_lx_tErr")
			errTxt = l "QuerySql error: " .. err .. ", " .. errTxt
			print(errTxt) -- util.printError(errTxt)
			return util.addError(nil, errTxt)
		end
	end

end

do
	local longArray
	local maxFieldCount = -100
	function selectionToArray4d(fieldNum)
		if #fieldNum > maxFieldCount then -- maxFieldCount is negative in first call, will go here
			maxFieldCount = math.abs(maxFieldCount)
			if #fieldNum > maxFieldCount then
				maxFieldCount = #fieldNum
			end
			callMethodNoReturn("_lx_SEL_TO_ARR_PARAM_RESIZE", maxFieldCount)
			longArray = getVariable("_lx_alCallbackParam")
		end
		for i, val in ipairs(fieldNum) do
			setArrayLongintElement(longArray, i, val)
		end
		callMethodNoReturn("_lx_SEL_TO_ARR", #fieldNum)
	end
end

--- 4D array to Lua Table
function array4dToTable(...)
	local arg = {...} -- array names
	if type(arg[1]) == "table" then
		arg = arg[1]
	end
	local arr = {}
	for i = 1, #arg do -- i,p in ipairs(arg) do
		arr[i] = {}
		arr[i].name = arg[i]
		arr[i].variable = getVariable(arr[i].name)
	end
	local firstArrSize = getSizeOfArray(arr[1].variable)
	local retTbl = {}
	for i = 1, #arg do
		local arrSize = getSizeOfArray(arr[i].variable)
		local name = arr[i].name
		if arrSize ~= firstArrSize then
			util.printError(l "array size is not equal to first array size: " .. name .. " " .. arrSize .. " / " .. arr[1].name .. " " .. firstArrSize)
		end
		retTbl[name] = {}
		for idx = 1, arrSize do
			retTbl[name][idx] = getArrayElement(arr[i].variable, idx)
			-- print(retTbl[name][idx])
		end
	end
	return retTbl
end

--- return 4D arrays so that array name is return table record field name
-- ret = [{"por_asProduct_id":"RUUVI M15","fld2":1},{"por_asProduct_id":"PUT 18MM TERÄS","fld2":2}]
function array4dToRecordTable(...)
	local arg = {...} -- array names
	--[[
	local stringReplaceCallback
	if type(arg[1]) == "table" then
		stringReplaceCallback = arg[2] -- must get 2 before 1
		arg = arg[1] -- arg is now table at arg[1]
	end
	]]
	local arg2
	if arg[2] and type(arg[2]) == "table" then
		arg2 = arg[2] -- tag names, mut get before arg = arg[1]
		arg = arg[1] -- 4d array names
	end
	local arr = {}
	for i, argValue in ipairs(arg) do
		arr[i] = getVariable(argValue)
	end
	local firstArrSize = getSizeOfArray(arr[1])
	local retTbl = util.newTable(firstArrSize, 0)
	for idx = 1, firstArrSize do
		retTbl[idx] = {}
	end
	for i = 1, #arg do
		yield()
		local arrSize = getSizeOfArray(arr[i])
		local name
		if arg2 then
			name = arg2[i]
		else
			name = arg[i]
		end
		if arrSize ~= firstArrSize then
			util.printError(l "array size is not equal to first array size: " .. name .. " " .. arrSize .. " / " .. firstArrSize)
		else
			-- local arrType = getArrayType(arr[i])
			for idx = 1, arrSize do
				retTbl[idx][name] = getArrayElement(arr[i], idx)
				--[[
				if stringReplaceCallback and arrType == "string" then
					retTbl[idx][name] = stringReplaceCallback(retTbl[idx][name])
				end
				]]
				if arrSize >= 2500 and skip(idx, 500) == 0 then
					yield()
				end
			end
		end
	end
	return retTbl, firstArrSize
end

--- return 4D arrays so that array name is return table index and values are in array
-- ret = {"por_asProduct_id":["RUUVI M15","PUT 18MM TERÄS","LE 2MM 100*200 SFS2001"], "fld2":[1,2,3]}
function array4dToArrayTable(arrayNames, tagNames)
	local arr = {}
	if #arrayNames ~= #tagNames then
		util.printError(l("name array size %d is not same as tag array size %d", #arrayNames, #tagNames))
	end
	for i, name in ipairs(arrayNames) do
		if printSql then
			print("array4dToArrayTable array name: " .. name .. ", tag name: " .. tostring(tagNames[i]))
		end
		arr[i] = getVariable(name)
	end
	local firstArrSize = getSizeOfArray(arr[1])
	local retTbl = {}
	for i = 1, #arrayNames do
		yield()
		local arrSize = getSizeOfArray(arr[i])
		local name = tagNames[i]
		if name == nil then
			util.printError(l("tag name is nil, tag names: '%s'", "'" .. table.concat(tagNames, "', '") .. "'"))
		elseif arrSize ~= firstArrSize then
			util.printError(l "array size is not equal to first array size: " .. name .. " " .. arrSize .. " / " .. firstArrSize .. ", array: '" .. tostring(arrayNames[i]) .. "' tag: '" .. tostring(tagNames[i]) .. "'")
		else
			retTbl[name] = util.newTable(arrSize, 0)
			for idx = 1, arrSize do
				retTbl[name][idx] = getArrayElement(arr[i], idx)
				if arrSize >= 2500 and skip(idx, 500) == 0 then
					yield()
				end
			end
		end
	end
	return retTbl, firstArrSize
end

--- return 2 arrays as Lua table so that first array is Lua table index and second array is value
-- local constantTbl = plg.array4dToIndexTable("_res_atConstantName", "_res_alConstantNumber")
function array4dToIndexTable(idx, value)
	local idxVariable = getVariable(idx)
	local valueVariable = getVariable(value)
	local idxArrSize = getSizeOfArray(idxVariable)
	local valueArrSize = getSizeOfArray(valueVariable)
	if idxArrSize ~= valueArrSize then
		util.printError(l "array sizes are not equal: " .. idxArrSize .. " / " .. valueArrSize)
	end
	local retTbl = {}
	for idx = 1, idxArrSize do
		local idxName = getArrayElement(idxVariable, idx)
		retTbl[idxName] = getArrayElement(valueVariable, idx)
	end
	return retTbl
end

do
	local saveFieldArray, saveIdArray, saveValueArray, saveScriptFieldArray
	local maxFieldCount = -100

	function arrayTableToSelection(fldValueTbl, idFld, idFieldArray, scriptFieldArray)
		local errTxt, idField -- , idFldName
		if type(idFld) == "string" then
			-- idFldName = idFld
			if not dfld2 then
				dfld2 = require "db/db_field2"
			end
			idField = dfld2.fieldNameToNumber(idFld)
		else
			-- idFldName = dfld2.fieldConstantName(idFld)
			idField = idFld
		end
		if not idField then
			errTxt = l "plg4d.arrayTableToSelection: unique id field is missing"
		else
			local _, firstValueArr = next(fldValueTbl)
			if #firstValueArr ~= #idFieldArray then
				errTxt = l "value array length is not same as id array length"
				print(errTxt .. " " .. #firstValueArr .. "/" .. #idFieldArray)
				return errTxt
			end
			local fldCount = 0
			local fldArrName = {}
			for fldName, _ in pairs(fldValueTbl) do -- for fldName,fldValueArrX
				fldCount = fldCount + 1
				fldArrName[fldCount] = fldName
			end

			if #idFieldArray > maxFieldCount then -- maxFieldCount is negative in first call, will go here
				maxFieldCount = math.abs(maxFieldCount)
				if #idFieldArray > maxFieldCount then
					maxFieldCount = #idFieldArray
				end
				if fldCount > maxFieldCount then
					maxFieldCount = fldCount
				end
				callMethodNoReturn("_lx_ARR_TO_SEL_PARAM_RESIZE", maxFieldCount)
				saveFieldArray = getVariable("_lx_atSaveFieldParam")
				saveIdArray = getVariable("_lx_atSaveIdParam")
				saveScriptFieldArray = getVariable("_lx_atScriptFieldArray")
			end
			for i, val in ipairs(fldArrName) do
				setArrayStringElement(saveFieldArray, i, val)
			end
			for i, val in ipairs(idFieldArray) do
				setArrayStringElement(saveIdArray, i, tostring(val))
			end

			if fldCount > 0 then
				callMethodNoReturn("_lx_ARR_TO_SEL_PARAM_RESIZE", -(fldCount * #fldValueTbl[fldArrName[1]]))
				saveValueArray = getVariable("_lx_atSaveValueParam")
				local i = 0
				for _, arrayName in ipairs(fldArrName) do -- loop all arrays and all elements, make big value array size = arrays*rows
					for _, val in ipairs(fldValueTbl[arrayName]) do
						i = i + 1
						setArrayStringElement(saveValueArray, i, tostring(val))
					end
				end
			end

			if scriptFieldArray then
				if scriptFieldArray == true then
					scriptFieldArray = fldArrName
				end
				for i, val in ipairs(scriptFieldArray) do
					setArrayStringElement(saveScriptFieldArray, i, val)
				end
			end
			local err = callMethodReturnLong("_lx_ARR_TO_SEL", idField, fldCount, #idFieldArray)
			if err ~= 0 then
				print("plugin_4d.lua - arrayTableToSelection: idField, fldCount, #idFieldArray: ", tostring(idField), tostring(fldCount), tostring(#idFieldArray))
				errTxt = getStringVariable("_lx_tErr")
				print("*** _lx_ARR_TO_SEL error: " .. err .. ", " .. errTxt)
			end
		end
		return errTxt
	end
end

do
	local valueArray, executeArray, nameArray

	local function queryInit()
		callMethodNoReturn("_lx_QUERY_PARAM_RESIZE", 1)
		-- we MUST get pointers with getVariable() to arrays again after call to _lx_QUERY_PARAM_RESIZE
		valueArray = getVariable("_lx_atQueryValueParam")
		executeArray = getVariable("_lx_atQueryExecuteParam")
		nameArray = getVariable("_lx_atQueryNameParam")
	end

	local function setFieldNameToNumber(fldName)
		-- MUST BE SAME IN new plg4d.lua, database-rest4d.lua and here in old v12 plugin_4d.lua
		local extRec = dschema.externalRec(fldName, "4d", "")
		if extRec and extRec.table_rec and extRec.table_rec.table_number then
			if extRec.table_rec.table_number < 10 then
				return "0" .. extRec.table_rec.table_number
			end
			return extRec.table_rec.table_number
		end
		util.printError("setFieldNameToNumber could not find 4D field number for field name: '%s'", tostring(fldName))
		return ""
	end

	local function toSetName(fieldName, isLoadRowsCall)
		-- MUST BE SAME IN new plg4d.lua, database-rest4d.lua and here in old v12 plugin_4d.lua
		if type(fieldName) == "string" then
			if isLoadRowsCall then
				return setFieldNameToNumber(fieldName) .. "_" .. peg.replace(fieldName, ".", "_"):sub(1, 30) .. "_1"
			else
				return peg.replace(fieldName, ".", "_"):sub(1, 30)
			end
		end
		util.printError("toSetName fieldName '%s' is not a string", tostring(fieldName))
		return ""
	end

	function executeSqlIn4d(queryText, numCount, textCount, dateCount, booleanCount, longCount, queryName, fieldName, option)
		if not valueArray then
			queryInit()
		end
		queryName = queryName or ""
		local callNew = false
		if queryName:sub(1, 4) == "new:" then
			callNew = true
			queryName = queryName:sub(5)
		end
		setArrayStringElement(valueArray, 1, queryText)
		setArrayStringElement(executeArray, 1, "")
		setArrayStringElement(nameArray, 1, queryName)
		-- print("plugin_4d/executeSqlIn4d, queryText: ", queryText)
		local trace = -1 -- use 4d <>_lx_lQueryDebug -preference
		local fld = fieldName and table.concat(fieldName, ",") or ""
		if printSql then
			if callNew then
				print("plg4d executeSqlIn4d, query: '" .. queryName .. "':\n ", queryText, fld) -- , json.toJsonRaw(fieldName), json.toJsonRaw(fieldType))
			else
				print("plg4d executeSqlIn4d, query: '" .. queryName .. "' new:\n ", queryText, fld) -- , json.toJsonRaw(fieldName), json.toJsonRaw(fieldType))
			end
		end
		local err
		if callNew then
			err = callMethodReturnLong("_lx_ExecuteSqlNew", numCount, textCount, dateCount, booleanCount, longCount, trace, fld, option and option.set_name)
		else
			err = callMethodReturnLong("_lx_ExecuteSql", numCount, textCount, dateCount, booleanCount, longCount, trace, fld)
		end
		-- print("plugin_4d/executeSqlIn4d, err: ", tostring(err))
		local errTxt = getStringVariable("_lx_tErr")
		if errTxt ~= "" then
			-- errTxt = l("4d execute sql error %s, %s\n %s", err, errTxt, queryText)
			errTxt = l("4d execute sql error %s: %s", err, errTxt)
			-- print(errTxt) -- 4d _lx_ExecuteSql will print this error
			util.addError(nil, errTxt)
		end
		return err, errTxt
	end

	local arrayName4dCache = {}
	local fieldNumber4dCache = {}
	function executeSql(queryText, fieldNameArray, fieldType, queryName)
		if not dfld2 then
			dfld2 = require "db/db_field2"
		end
		if useSetName == nil then
			local dprf = require "db/db_preference"
			local prf = dprf.prf("system/4d.json")
			-- util.printTable(prf, "system/4d.json")
			useSetName = prf.use_set_name
			-- util.print("system/4d.json, useSetName: %s", tostring(useSetName))
		end
		local numCount = 0
		local longCount = 0
		local textCount = 0
		local dateCount = 0
		local booleanCount = 0
		-- if queryText:find("SELECT COUNT") == 1 then
		-- 	arrName = {"_lx_arExe1"}
		-- end
		--[[
		if not fieldType then
			local errTxt = l("field type array does not exist, sql:\n %s", queryText)
			util.printError(errTxt)
			util.addError(nil, errTxt)
			return nil,errTxt
		elseif #fieldNum < 1 then
			local errTxt = l("field number array is empty, sql:\n %s", queryText)
			util.printError(errTxt)
			return nil,util.addError(nil, errTxt)
		end]]
		local arrName = util.newTable(#fieldNameArray)
		if type(fieldType) == "table" then
			arrName = util.newTable(#fieldType)
			for i, fldType in ipairs(fieldType) do
				if printSql then
					print("**** fld: " .. tostring(fieldNameArray[i]) .. " - fldType: " .. fldType)
				end
				if fldType == "number" or fldType == "double" then -- fldType == "number" then
					numCount = numCount + 1
					arrName[i] = "_lx_arExe" .. numCount
				elseif fldType == "integer" or fldType == "time" or fldType == "bigint" then
					longCount = longCount + 1
					arrName[i] = "_lx_alExe" .. longCount
				elseif fldType == "date" or fldType == "datetime" then
					dateCount = dateCount + 1
					arrName[i] = "_lx_adExe" .. dateCount
				elseif fldType == "boolean" then
					booleanCount = booleanCount + 1
					arrName[i] = "_lx_afExe" .. booleanCount
				elseif fldType == "string" then
					textCount = textCount + 1
					arrName[i] = "_lx_atExe" .. textCount
				else
					local errTxt = l("field type '%s' is not supported, sql:\n %s", fldType, queryText)
					util.printError(errTxt)
					return nil, util.addError(nil, errTxt)
				end
			end
		else
			-- for i, fldType in ipairs(fieldType) do
			for i, fld in ipairs(fieldNameArray) do
				local fldTypeDb = dfld2.fieldType(fld)
				local fldType
				if fldTypeDb == "varchar" then
					fldType = "string"
				elseif fldTypeDb == "character varying" then
					fldType = "string"
				elseif fldTypeDb == "text" then
					fldType = "string"
				elseif fldTypeDb == "double" then
					fldType = "number"
				elseif fldTypeDb == "integer" then
					fldType = "number"
				elseif fldTypeDb == "bigint" then
					fldType = "number"
				elseif fldTypeDb == "date" then
					fldType = "date"
				elseif fldTypeDb == "time" then
					fldType = "time"
				elseif fldTypeDb == "boolean" then
					fldType = "boolean"
				elseif fldTypeDb == "blob" then
					fldType = "string"
				else
					fldType = "string"
				end
				if printSql then
					print("**** " .. fld .. " - fldTypeDb: " .. fldTypeDb .. " - fldType: " .. fldType)
				end
				if fldType == "number" or fldType == "double" then
					numCount = numCount + 1
					arrName[i] = "_lx_arExe" .. numCount
				elseif fldType == "integer" or fldType == "time" or fldType == "bigint" then
					longCount = longCount + 1
					arrName[i] = "_lx_alExe" .. longCount
				elseif fldType == "date" or fldType == "datetime" then
					dateCount = dateCount + 1
					arrName[i] = "_lx_adExe" .. dateCount
				elseif fldType == "boolean" then
					booleanCount = booleanCount + 1
					arrName[i] = "_lx_afExe" .. booleanCount
				elseif fldType == "string" then
					textCount = textCount + 1
					arrName[i] = "_lx_atExe" .. textCount
				else
					local errTxt = l("field type '%s' is not supported, sql:\n %s", fldType, queryText)
					util.printError(errTxt)
					return nil, util.addError(nil, errTxt)
				end
			end
		end
		local fieldNumber = {}
		local arrayNameTbl = {}
		if peg.found(peg.parseBefore(queryText, "FROM"), "(") or peg.found(queryText, "FROM _") then
			-- any sql function like "COUNT(*)" or FROM _preference
			arrayNameTbl = nil -- use _lx_alExe
			-- util.print("queryText contains (, %s", queryText)
		end
		if useSetName and arrayNameTbl then
			local extRec, arrayName4d
			for i, fld in ipairs(fieldNameArray) do
				fld = peg.replace(fld, ".", "_")
				if arrayName4dCache[fld] then
					arrayNameTbl[i] = arrayName4dCache[fld]
					fieldNumber[i] = fieldNumber4dCache[fld]
				else
					extRec = dschema.externalRec(fld, "4d", "")
					if extRec == nil or extRec.table_rec == nil then
						util.printError("plg4d executeSql could not find externalRec for field: '%s'", tostring(fld))
						arrayNameTbl = nil
						break
					elseif extRec.field_number == nil then
						util.printError("plg4d executeSql could not find externalRec field_number for field: '%s'", tostring(fld))
						arrayNameTbl = nil
						break
					else
						arrayName4d = callMethodReturnString("_app_ FldArrName", extRec.field_number) -- (_lx_lTable; _lx_alFld{$_lx_lFldIndex})
						if arrayName4d and arrayName4d ~= "" then
							fieldNumber4dCache[fld] = extRec.field_number
							fieldNumber[i] = fieldNumber4dCache[fld]
							arrayName4dCache[fld] = arrayName4d
							arrayNameTbl[i] = arrayName4d
						else
							util.printError("plg4d executeSql could not get 4D array name '%s' for field: '%s'", tostring(arrayName4d), tostring(fld))
							extRec.table_rec = nil
							util.printTable(extRec, "extRec")
							arrayNameTbl = nil
							break
						end
					end
				end
			end
		end

		if queryText:find("SELECT 4D_MORE_ROWS FROM", 1, true) then
			if printSql then
				print("plg4d selectionToArrayTable:\n ", queryText, json.toJsonRaw(fieldNameArray), json.toJsonRaw(fieldType))
			end
			local rows
			if useSetName and arrayNameTbl then
				arrName = arrayNameTbl
				local setName = useSetName and toSetName(peg.replace(fieldNameArray[1], ".", "_"), true) .. ";" .. table.concat(arrName, ",") .. ";" .. table.concat(fieldNumber, ",")
				rows = callMethodReturnLong("_lx_ExecuteSqlLoad", setName)
			else
				rows = callMethodReturnLong("_lx_ExecuteSqlLoad", "")
			end
			return arrName, nil, rows
		end
		if printSql then
			print("plg4d selectionToArrayTable:\n ", queryText, json.toJsonRaw(fieldNameArray), json.toJsonRaw(fieldType))
		end
		local option = {}
		if useSetName and arrayNameTbl then
			arrName = arrayNameTbl
			option.set_name = toSetName(fieldNameArray[1], false) .. ";" .. table.concat(arrName, ",") .. ";" .. table.concat(fieldNumber, ",")
		else
			option.set_name = ""
		end
		queryText = queryText .. "\n INTO :" .. table.concat(arrName, ",:")
		local err, errText = executeSqlIn4d(queryText, numCount, textCount, dateCount, booleanCount, longCount, queryName, fieldNameArray, option)
		if err ~= 0 then
			return nil, errText
		end
		return arrName, errText
	end

	function selectionToRecordTable(sql, fieldName, fieldType, queryName)
		local info = {}
		local queryText
		if type(sql) == "string" then
			queryText = sql
		else
			queryText = sql.queryText
		end
		if printSql then
			print("plg4d executeSql:\n ", queryText, table.concat(fieldName, ", ")) -- , json.toJsonRaw(fieldName), json.toJsonRaw(fieldType))
		end
		local arrName, err = executeSql(queryText, fieldName, fieldType, queryName)
		if not arrName then
			info.error = err
			return nil, info
		end
		local ret, rowCount = array4dToRecordTable(arrName, fieldName) -- create record table from array names
		info.column_name = fieldName
		info.column_count = #fieldName
		info.row_count = rowCount
		info.row_count_total = rowCount
		return ret, info
	end

	local resultSetBlob, blobSize, paBlobVariable, blob, blobHandle, resultUtfJson
	function selectionToArrayTable(sql, fieldName, fieldType, queryName)
		--- returns Lua table so that array name is return table index and values are in array
		-- ret = {"por_asProduct_id":["RUUVI M15","PUT 18MM TERÄS","LE 2MM 100*200 SFS2001"], "fld2":[1,2,3]}
		local info = {}
		local queryText
		if not sql then
			local err = "sql parameter is null"
			info.error = err
			print(err) -- util.printError(err)
			return nil, info
		elseif sql.sql then
			queryText = sql.sql -- sql is cursor table created before by execute()
		elseif type(sql) == "string" then
			queryText = sql
		else
			queryText = sql.queryText
		end
		-- if fieldNum and type(fieldNum[1]) == "string" then
		--	fieldNum = db.fieldNameToNum(fieldNum)
		-- end
		if printSql then
			print("plg4d selectionToArrayTable:\n ", queryText) -- , json.toJsonRaw(fieldName), json.toJsonRaw(fieldType))
		end
		local arrName, err, moreRows = executeSql(queryText, fieldName, fieldType, queryName)
		if err and err ~= "" then
			info.error = err
		end
		if not arrName then
			return nil, info
		end
		info.column_name = fieldName
		info.column_count = #fieldName
		info.row_count_more = moreRows
		local rowCountTotal = getLongVariable("_lx_lResultSetSize")
		info.row_count_total = rowCountTotal
		local resultConvertTime = util.seconds()
		local useResultJson = getLongVariable("_lx_lUseQuerySetJsonResult")
		local ret, rowCount
		if useResultJson == 1 then
			if paBlobVariable == nil then
				paBlobVariable = c4d.PA_GetVariable(toUnichar("_lx_xResultSetBlob"))
			end
			blobSize = 0
			if paBlobVariable.fType == 30 then -- eVK_Blob
				blob = paBlobVariable.uValue.fBlob
				blobHandle = blob.fHandle
				blobSize = blob.fSize
			else
				util.printRed("plg4d selectionToArrayTable _lx_xResultSetBlob fType is not eVK_Blob, sql: %s", queryText)
			end
			if blobSize > 4 then
				resultSetBlob = c4d.PA_LockHandle(blobHandle)
				resultUtfJson = ffi.string(resultSetBlob) -- , blobSize
				c4d.PA_UnlockHandle(blobHandle)
			else
				resultUtfJson = ""
			end
			if resultUtfJson == "" then
				util.printRed("plg4d selectionToArrayTable useResultJson is 1 but resultUtfJson is empty, sql: %s", queryText)
				util.print("plg4d result blob %s handle %s size: %d, resultUtfJson: %d, resultSetBlob %s, paBlobVariable %s: %s...", tostring(blob), tostring(blobHandle), blobSize, #resultUtfJson, tostring(resultSetBlob), tostring(paBlobVariable), tostring(resultUtfJson):sub(1,500))
			else
				local arr = getVariable(arrName[1])
				rowCount = getSizeOfArray(arr)
				info.row_count = rowCount
				if info.row_count_total == nil then
					info.row_count_total = rowCount
				end
				info.return_type = "array 4d json"
				info.arr_ap_json = 1
				info.server_convert_time = util.seconds(resultConvertTime)
				info.server_send_time = util.seconds()
				ret = {data = '{"info":' .. json.toJsonRaw(info) .. ',"data":' .. resultUtfJson .. '}'}
			end
		end
		if ret == nil then
			ret, rowCount = array4dToArrayTable(arrName, fieldName)
			info.row_count = rowCount
			if info.row_count_total == nil then
				info.row_count_total = rowCount
			end
			info.server_convert_time = util.seconds(resultConvertTime)
			info.server_send_time = util.seconds()
		end
		return ret, info
	end

	function execute(conn, sqlExecuteText, param, queryName)
		-- print("plugin_4d/execute: ", sqlExecuteText) -- call from 4d
		if sqlExecuteText:find("SELECT ") == 1 then
			local cursor = {conn = conn, sql = sqlExecuteText}
			return cursor
		elseif true or (sqlExecuteText:find("INSERT INTO ") == 1 or sqlExecuteText:find("UPDATE ") == 1 or sqlExecuteText:find("DELETE FROM ") == 1 or sqlExecuteText == "START" or sqlExecuteText == "ROLLBACK" or sqlExecuteText == "COMMIT") then
			if printSql then
				print("plg4d execute:\n " .. sqlExecuteText)
			end
			local err, errText = executeSqlIn4d(sqlExecuteText, 0, 0, 0, 0, 0, queryName)
			if err ~= 0 then
				return nil, errText
			end
			return 0, errText -- cursor number 0 == don't run anything else
		end
		print("plg execute other than SELECT, UPDATE, INSERT INTO or DELETE FROM (in uppercase) is not supported, sql:\n " .. sqlExecuteText)
		return nil
	end

end

function selectPath(prompt)
	if not prompt then
		prompt = ""
	end
	callMethodNoReturn("_doc FolderChoose", prompt)
	local path = getStringVariable("_doc_tPrevPath")
	if util.isWin() then
		path = peg.replace(path, "\\", "/")
	elseif util.isMac() then
		path = peg.replace(path, ":", "/")
	end
	return path
end

function alert(prompt)
	if not prompt then
		prompt = ""
	end
	callMethodNoReturn("_info_ ALERT", prompt)
end

function confirm(prompt)
	if not prompt then
		prompt = ""
	end
	callMethodNoReturn("_info_ Confirm", prompt)
	local ok = getLongVariable("_info_lbYes")
	return ok == 1
end

function querySqlAlter(param, data, info)
	-- util.printTable(param, "plugin_4d/querySqlAlter/param")
	if param.script == nil or param.script.save_preference == nil then
	elseif param.script.save_preference.run_script == nil or #param.script.save_preference.run_script <= 0 then
	elseif param.script.record_id_table == nil or param.script.record_id == nil then
	else
		-- add rec.record_id -tag
		local rec = util.arrayRecord(param.script.record_id_table, param.script.save_preference.run_script, "table", 1)
		if rec then
			rec.record_id = param.script.record_id
		end
		local paramTxt = json.toJsonRaw({run_script = param.script.save_preference.run_script}) -- util.toJsonRaw
		callMethodNoReturn("_lx_JSON_SCRIPT_RUN", paramTxt)
	end
	return nil
	--[[

		"run_script": [
			{
				"table": "por",
				"record_id": "muistiin-> generoi record_id array datasta",
				"script_field":["por_product_id", "por_order_amount"]
			},
			{
				"table": "po",
				"record_id": "muistiin-> generoi record_id array datasta",
				"script_field":["po_supplier_id"],
				"up_calculate": true
			}
		]
		--]]

	-- callMethodNoReturn("_app_ JSON_SCRIPT_RUN_LUA", paramTxt)
	-- callMethodNoReturn("_doc FolderChoose", prompt)
	-- local path = getStringVariable("_doc_tPrevPath")
end
