Re: LuaTeX/ConTeXt connect to SQL Server

[Hans Hagen <pragma@wxs.nl>]

On 5/26/2014 6:38 PM, Matt Baker wrote:
> FROM MY POSTING AT:
> http://tex.stackexchange.com/questions/180018/luatex-context-connect-to-sql-server?noredirect=1#comment415824_180018
>
> I am looking to connect ConTeXt to a SQL Server database. I have looked
> through the manuals, and even read pragma-ade's SQL PDF (which, for me,
> seems a bit abstract). I am not extremely familiar with Lua but have read
> up on the language (I am more familiar with JS). For LaTex I used nlatexdb
> which did the trick, but LaTex isn't able to do what ConTeXt can.
>
>     -
>
>     The connection needs to use Windows Authentication.
>     -
>
>     The document is set up as a letter with an environment that it pulls
>     from.
>     -
>
>     It would be preferable to have the connection and queries be seperate
>     from the letter itself, and be able to place variables into the letter that
>     can be populated through the queries (much like nlatexdb) if possible.
>     - example: Dear ??first ??last, Thank you for your interest in
>        ??product... etc.
>
> I am rather new to ConText and just starting to get the hang of it, long
> time latex user, the transition starts off rather confusing, but once it
> makes sense it opens up a whole toolbox of available tools which is great.
> Just need this one last tool.
>
> Any help would be much appreciated.
>
> EDIT:: Honestly I haven't gotten to any point that I felt I could attempt
> trying the connection. Looking at the SQL PDF from pragma-ade it should
> look something like:
>
>      local presets = {
>      server = "XXX\XXX",
>      Database = "Customer",
>      Integrated Security = "True",
>      }

I have no clue where get that setup from as the manual says:

local presets = {
     database = "test",
     username = "root",
     password = "none",
     host     = "localhost",
     port     = 3306,
}

or something like that. The same kind of connection info that the mysql 
client needs.

>      presets.id = "XXX"

?

>      local template = [[
>      SELECT
>      'Control_Content'
>      FROM
>      'Customer.dbo.Report_Client_Data'
>      WHERE
>      'Control_Name' LIKE 'Long_Name' AND 'Client_Id' = 154;
>       ]]
>
>      local data, keys = utilities.sql.execute {
>      presets = presets,
>      template = template,
>      variables = {},
>      }

should work given a proper preset table

> I can't seem to find where or how the connection is being made, except
> perhaps where utilities.sql.execute is called, but I can't find any
> information anywhere about such a call in lua or context...

you can best start with using the command line client approach:

   sql.setmethod("client")

because the other methods assume a library that matches your luatex 
(this should works ok with regular mysql or swiglib bindings but it adds 
a level of complication)

Hans


-----------------------------------------------------------------
                                           Hans Hagen | PRAGMA ADE
               Ridderstraat 27 | 8061 GH Hasselt | The Netherlands
     tel: 038 477 53 69 | voip: 087 875 68 74 | www.pragma-ade.com
                                              | www.pragma-pod.nl
-----------------------------------------------------------------
___________________________________________________________________________________
If your question is of interest to others as well, please add an entry to the Wiki!

maillist : ntg-context@ntg.nl / http://www.ntg.nl/mailman/listinfo/ntg-context
webpage  : http://www.pragma-ade.nl / http://tex.aanhet.net
archive  : http://foundry.supelec.fr/projects/contextrev/
wiki     : http://contextgarden.net
___________________________________________________________________________________