# How can I create my own listings style/format?

I'm trying to format some SQL statements using the listings package. It looks OK for small statements but the long ones look bad.

select * from Customers where C_nID=10;


to

select *
from customers
where C_nID=10;


So I tried to create my own style

this works :

\lstdefinelanguage{mojsql}
{keywords={SYSDATETIME,sysdatetime,the,list,is,too,long%
sensitive=true,%
comment=[l]{\#},%
literate= {ä}{{a}}1 {\\}{{}}0 {?}{{\textcolor{red}{\textbf{?}}}}1,
string=[b]",%
string=[b]'%
}

\lstset{
language=mojsql, %% Troque para PHP, C, Java, etc... bash é o padrão
extendedchars=true,
breaklines=true, breakatwhitespace=false,
numbers=left,
frame=single
}


Then I wanted to format the statement (for example a new line before every where and so on). I have found the \lstdefineformat{} command but it doesn't work (command not defined error). I also tried the \usepackage[format]{listings} for importing, but I get an option clash for package listings error

So how can I make sure that a new line is inserted before every where or other keywords?

UPDATE:

Ok I've found the problem and I can execute the function but unfortunately new problems rose. I have described them in the working example.

\documentclass[12pt,oneside,a4paper]{book}
\usepackage[formats]{listings}
\usepackage{color}
\lstdefinelanguage{mojsql}
{keywords={select,top,from,too,long,for,a,minimal,example},%
sensitive=false,%
morecomment=[s]{/*}{*/},%
morecomment=[l]{//},%
literate= {ä}{{a}}1 {\\}{{}}0 {?}{{\textcolor{red}{\textbf{?}}}}1 {1}{{\textcolor{red}{\textbf{1}}}}1,%I tried something like {AND}{\newline AND}3 the text was repleaced bud the new line command was ignored ...
string=[b]",%
string=[b]'%
}
%The command works now (I had a typo and an older version of listings Im not sure which one fixed the problem)
\lstdefineformat{mojsqlFormat}{
\{ =\newline\string,%this works fine ( to test replace "{" with "(" there are no { in the statement
%I want the exact same thing with the WHERE keyword but where=\newline\string does not work -> ! LaTeX Error: Missing \begin{document}. Is there simple solution for this?
%,where=\newline\string
}
\lstset{
language=mojsql, % Troque para PHP, C, Java, etc... bash é o padrão
format=mojsqlFormat,
extendedchars=true,
breaklines=true, breakatwhitespace=false,
numbers=left,
frame=single
}
\begin{document}
%long unformated sql statement (im a script to inject it before the compilation)
\begin{lstlisting}
SELECT (SELECT TOP 1 a.id FROM vAnalysesHistory AS a WHERE a.companyid = n.stockid ORDER BY a.chosendatetime DESC) AS id, n.name, (SELECT TOP 1 a.chosendatetime FROM vAnalysesHistory AS a WHERE a.companyid = n.stockid ORDER BY a.chosendatetime DESC) AS chosendatetime FROM vStockNames AS n
\end{lstlisting}
\end{document}

-
Could you produce a full minimal example that highlights the errors you get with \lstdefineformat and with the option clash? Also, you seem to be asking several things: (1) How do I avoid these errors I'm getting (2) How do I get listings to break a line at a keyword? These are perhaps distinct questions worth asking separately… –  Seamus Oct 19 '11 at 10:22
I try. I need to format the sql statements it doesent realy matter how i just posted what i already tried –  sherif Oct 19 '11 at 11:03

Using \ WHERE=\newline\string (note the space following the backslash) seems to work. Here is the BEFORE:

and AFTER:

\lstdefineformat{mojsqlFormat}{
\{ =\newline\string,%this works fine ( to test replace "{" with "(" there are no { in the statement
%I want the exact same thing with the WHERE keyword but where=\newline\string does not work -> ! LaTeX Error: Missing \begin{document}. Is there simple solution for this?
,\ WHERE=\newline\string
}

-
Thanks :) Im just curious what does the "\ " do ? –  sherif Oct 20 '11 at 8:46
Honestly, I don't know. I suspect that when you use \WHERE TeX tries to figure out what \W is, which it doesn't know. –  Peter Grill Oct 20 '11 at 16:32