Forum

Kognitio Console issues, advice, etc.
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

How do I use results of a query in Kog scripting?

by MikeAtkinson » Tue Jun 04, 2013 10:59 am

We've been asked about how to use the results of a query within a Kog script. Can variables be used in a similar way to SQL scripts?
Reply with quote Top
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: How do I use results of a query in Kog scripting?

by MikeAtkinson » Wed Jun 05, 2013 8:15 am

For Kog scripts there are a couple of options to get at the results of a query. Take a simple query like:

Code: Select all

select * from ipe_user;
Then the variables Col1, Col2, Col3, etc. will have been set to the values of the columns in the first row returned. This is included mainly for compatibility with old style SQL scripts, but may be still be useful when a query returns a single value or single row of values.

The second method is more versatile. The results of a query may be assigned to a variable.

Code: Select all

t = select * from ipe_user;
Then t is a Lua table with t.numRows, t.numCols, t.status are set and there are two sub-tables t.rows and t.colNames.
t.rows is indexed from 1 (Lua tables have the start index of 1 by default), so the first row is t.row[1], similarly t.colNames[1] is the name of the first column.
Within a row the column values are indexed by the column names so t.rows[1].name is the user name for the above query.

Kog script uses $ within the statements to perform substitution. So

Code: Select all

names = {'ipe_user', 'ipe_schema'}
for i,v in pairs(names) do
    t = select * from $v;
end;
does the two statements "select * from ipe_user" and "select * from ipe_schema".
$() may be used to enclose any Lua expression (e.g. function calls).

The following Kog script shows all this:

Code: Select all

t = select * from sys.ipe_user;

print("Col1="..Col1.."\n")
print("Col2="..Col2.."\n")
print("Col3="..Col3.."\n")
print("Col4="..Col4.."\n")
print("Col5="..Col5.."\n")
print("Col6="..Col6.."\n")
print("Col7="..Col7.."\n")

print("\n\n\n")
for i,v in pairs(t) do
    print(i, v, " \n")
end

print("\n\n\n")
for i,v in pairs(t.colNames) do
    print(i, v, " \n")
end

print("\n\n\n")
for i,v in pairs(t.rows) do
    print(i, v, " \n")
end

print("\n\n\n")
for i,v in pairs(t.rows[1]) do
    print(i, v, " \n")
end

names = {'ipe_user', 'ipe_schema'}
for i,v in pairs(names) do
    t = select * from $v;
end;

function name() return 'ipe_privs' end
t = select * from $(name());
Reply with quote Top
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: How do I use results of a query in Kog scripting?

by MikeAtkinson » Wed Jun 05, 2013 8:30 am

Note that unlike SQL scripts Kog script variables are case sensitive. This means that the automatic variable names are as below.

These variables are set by a query:
  • Col1
  • Col2
  • Col3
  • Col4
  • Col5
  • Col6
  • Col7
  • CompileTime
  • ExecuteTime
  • FirstRowTime
  • NumColumns
  • NumRows
  • QueryNumber
  • SQLState
  • TotalTime
  • WCSerror
  • line_number
These variables are not set by queries, they contain system information
  • sysver
  • withinConsole
  • cliver
These variables are used to control the script, as they are just plain Lua variables they may be set by assignment in the script as well:
  • error_mode
  • history_group
  • history_mode
  • script_error_mode
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron