Re: database

[Taco Hoekwater <taco@elvenkind.com>]

[-- Attachment #1: Type: text/plain, Size: 1362 bytes --]



Hi,

What you are asking for (MySQL I/O) cannot be done. The context
command you refer to intended solely for syntax highlighting of
SQL statements.

However, you got me thinking. :)

See attached context module and test file. It's extremely crude
and more of a proof of concept than a full module, but it seems
to work OK on the simple tests I threw at it. All of the commands
and macros it provides are in the demonstration file.

Stuff to keep in mind:

- \write18 had better be enabled

- the statement is fed to a 'mysql' in batchmode during \MySQLexecute

- The result is saved to a file named \jobname-[Queryname]-result.dat

- You always have to re-execute before you attempt to process the
  the results

- The 'Countexecutable' is supposed to return a single line that starts with
  the number of lines of the \jobname-[Queryname]-result.dat file.

- No error checking whatsoever is done. Spefically, nothing is done
  about MySQL's output format, so you can expect problems with 
  embedded newlines and other specials characters

The module is a joke, ok? But a fun one :-)

Greetings, Taco


On Wed, 23 Apr 2003 20:55:16 +0200, LEGUEDOIS, wrote:

> but I don't know how to use this (how to use the result of the query, where
> I write database connection's parameters...). Do you know where can I find a
> manual or any help?


-- 
groeten,

Taco

[-- Attachment #2: t-mysql.tex --]
[-- Type: application/octet-stream, Size: 4234 bytes --]

%D \module
%D   [       file=t-mysql,
%D        version=2003.04.25,
%D          title=\CONTEXT\ Contrib macros,
%D       subtitle=MySQL I/O (alfa),
%D         author=Taco Hoekwater,
%D           date=\currentdate,
%D      copyright={Taco Hoekwater}]


\def\setupMySQL#1[#2]{%
  \getparameters[My][Host=,Database=,User=,Password=,Executable=mysql,Countexecutable=wc,#2]%
}

\long\def\startMySQL#1[#2]#3\stopMySQL{\setxvalue{MySQLquery#2}{#3}}

\newread\MySQLread
\newcount\MySQLscratchcounter
\newbox\MySQLscratchbox

\long\def\MySQLgetrows#1\relax{%
  \global\MySQLscratchcounter=0 
  \setbox\MySQLscratchbox\hbox{\global\MySQLscratchcounter#1}%
}

\def\executeMySQL[#1]{%
        \ifx\MyPassword\empty \def\MyPas{ } \else \edef\MyPas{\space-p\MyPassword \space}\fi
        \ifx\MyHost    \empty \def\MyHos{ } \else \edef\MyHos{\space-h\MyHost     \space}\fi
        \ifx\MyUser    \empty \def\MyUse{ } \else \edef\MyUse{\space-u\MyUser     \space}\fi
        \immediate \write18{\MyExecutable\space-e'\getvalue{MySQLquery#1}'
                            \MyHos\MyUse\MyPas\MyDatabase\space > \jobname-#1-result.dat}%
        \immediate \write18{\MyCountexecutable\space
                            \jobname-#1-result.dat > \jobname-#1-result-rows.dat}%
        \immediate\openin\MySQLread= \jobname-#1-result-rows.dat
        \def\mymac{}%
        \read\MySQLread to \mymac
        \expandafter\MySQLgetrows\mymac\relax
        \advance\MySQLscratchcounter - 1 % for column headers
        \setxvalue{MySQLrows#1}{\the\MySQLscratchcounter}%
        \immediate\closein\MySQLread
        \immediate\openin\MySQLread= \jobname-#1-result.dat
        \read\MySQLread to \mymac
        \expandafter\processMySQLfirstline\mymac\relax
	\setxvalue{MySQLcols#1}{\the\MySQLscratchcounter}%        
	\setxvalue{MySQLcurrent}{#1}%
}

\gdef\processMySQLfirstline#1\relax{%
\MySQLscratchcounter=0
\doprocessMySQLfirstline#1^^I%note the trailing \t
\advance\MySQLscratchcounter-1
}

\long\gdef\doprocessMySQLfirstline#1^^I{%
 \advance\MySQLscratchcounter 1
 \xdef\whatever{#1}%
 \ifx\whatever\empty
    \let\next\relax
 \else
    \setxvalue{MySQLcolumnname\the\MySQLscratchcounter}{#1}%
    \let\next\doprocessMySQLfirstline
 \fi 
 \next
}

{\catcode`\^^I=12
\gdef\processMySQLline#1\relax{%
 \MySQLscratchcounter=0
 \def\currentMySQLlist{}%
 \catcode`\^^I=12
 \doprocessMySQLline#1^^I%note the trailing \t
 %
}

\long\gdef\doprocessMySQLline#1^^I{%
 \advance\MySQLscratchcounter 1
 \xdef\whatever{#1}%
 \ifnum\MySQLscratchcounter=\MySQLcolcount
    \let\next\relax
 \else
   \let\next\doprocessMySQLline
 \fi  
 \ifx\currentMySQLlist\empty
   \edef\currentMySQLlist{\getvalue{MySQLcolumnname\the\MySQLscratchcounter}={#1}}%
 \else
   \edef\currentMySQLlist{\currentMySQLlist,\getvalue{MySQLcolumnname\the\MySQLscratchcounter}={#1}}%
 \fi
 \next
}

\gdef\processMySQLlinesimple#1\relax{%
 \MySQLscratchcounter=0
 \def\currentMySQLlist{}%
 \catcode`\^^I=12
 \doprocessMySQLlinesimple#1^^I%note the trailing \t
 %
}

\long\gdef\doprocessMySQLlinesimple#1^^I{%
 \advance\MySQLscratchcounter 1
 \xdef\whatever{#1}%
 \ifnum\MySQLscratchcounter=\MySQLcolcount
    \let\next\relax
 \else
   \let\next\doprocessMySQLlinesimple
 \fi  
 \ifx\currentMySQLlist\empty
   \edef\currentMySQLlist{{#1}}%
 \else
   \edef\currentMySQLlist{\currentMySQLlist,{#1}}%
 \fi
 \next
}

%\gdef\fetchMySQLarray#1[#2]#3{%
%\catcode`\^^I=12
%\edef\MySQLcolcount{\getvalue{MySQLcols#2}}%
%\read\MySQLread to \mymac
%\expandafter\processMySQLlinesimple\mymac\relax
%\let#3\currentMySQLlist
%\catcode`\^^I=10 }
\gdef\fetchMySQLarray#1{%
\catcode`\^^I=12
\edef\MySQLcolcount{\getvalue{MySQLcols\MySQLcurrent}}%
\bgroup \endlinechar = -1\def\t{ }\def\n{ }\global\read\MySQLread to \mymac
\expandafter\processMySQLlinesimple\mymac\relax
\global\let#1\currentMySQLlist \egroup
\catcode`\^^I=10 }

\gdef\fetchMySQLhash#1{%
\catcode`\^^I=12
\edef\MySQLcolcount{\getvalue{MySQLcols\MySQLcurrent}}%
\bgroup \endlinechar = -1\def\t{ }\def\n{ }\global\read\MySQLread to \mymac
\expandafter\processMySQLline\mymac\relax\relax
\global\let#1\currentMySQLlist \egroup
\catcode`\^^I=10 }
}

\def\MySQLrows{\getvalue{MySQLrows\MySQLcurrent}}

\def\MySQLcolumns{\getvalue{MySQLcols\MySQLcurrent}}


[-- Attachment #3: sqltest.tex --]
[-- Type: application/octet-stream, Size: 597 bytes --]


\usemodule[mysql]

\setupMySQL[Host=tullamore,
	    User=taco,
	    Password=,
	    Database=test,
            %Executable=mysql,
            %Countexecutable=wc -l,
 ]

\def\printlist#1{\message{#1}}

\startMySQL[Users]
SELECT * from tst;
\stopMySQL

\starttext
	
\executeMySQL[Users]

\dorecurse{\MySQLcolumns}{
	\message{\getvalue{MySQLcolumnname\recurselevel}}
}

\dorecurse{\MySQLrows}{
  \fetchMySQLhash\userlist
  \processcommacommand[\userlist]\printlist
}

\executeMySQL[Users]

\dorecurse{\MySQLrows}{
  \fetchMySQLarray\userlist
  \processcommacommand[\userlist]\printlist
}

\stoptext