Take the 2-minute tour ×
TeX - LaTeX Stack Exchange is a question and answer site for users of TeX, LaTeX, ConTeXt, and related typesetting systems. It's 100% free, no registration required.

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}
share|improve this question
1  
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

1 Answer 1

up vote 4 down vote accepted

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

and AFTER: enter image description here

\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
}
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.