<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=iso-8859-1"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><base href="x-msg://1400/"><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Vorformatiert Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.apple-style-span
{mso-style-name:apple-style-span;}
span.apple-converted-space
{mso-style-name:apple-converted-space;}
span.E-MailFormatvorlage19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.HTMLVorformatiertZchn
{mso-style-name:"HTML Vorformatiert Zchn";
mso-style-priority:99;
mso-style-link:"HTML Vorformatiert";
font-family:"Courier New";}
span.sql1-space
{mso-style-name:sql1-space;}
span.sql1-reservedword1
{mso-style-name:sql1-reservedword1;
color:blue;
font-weight:bold;}
span.sql1-tablename1
{mso-style-name:sql1-tablename1;
color:fuchsia;}
span.sql1-symbol1
{mso-style-name:sql1-symbol1;
color:blue;}
span.sql1-identifier1
{mso-style-name:sql1-identifier1;
color:olive;}
span.sql1-function1
{mso-style-name:sql1-function1;
color:navy;
font-weight:bold;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=DE link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Hi Tim,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>please find attached the log file, the last two entries are the critical ones.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Timespan error appears only when running the whole sql statement (with some joins). The other error (columns) only when running a short sql statement (without joins). In both cases no data can be provided via IPT.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>The problem is not the database, since we have tested it with several other databases and different machines too and it didn’t work.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><pre><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>This is a small sql script that didn’t work: </span><span class=sql1-reservedword1><span lang=EN-US>SELECT</span></span><span class=sql1-space><span lang=EN-US style='color:black'> </span></span><span class=sql1-tablename1><span lang=EN-US>dnabanknumbers</span></span><span class=sql1-symbol1><span lang=EN-US>.</span></span><span class=sql1-identifier1><span lang=EN-US>ID_DNA</span></span><span class=sql1-symbol1><span lang=EN-US>,</span></span><span class=sql1-space><span lang=EN-US style='color:black'> </span></span><span class=sql1-tablename1><span lang=EN-US>dnabanknumbers</span></span><span class=sql1-symbol1><span lang=EN-US>.</span></span><span class=sql1-identifier1><span lang=EN-US>DNA_Bank_Number</span></span><span class=sql1-symbol1><span lang=EN-US>,</span></span><span class=sql1-space><span lang=EN-US style='color:black'> </span></span><span class=sql1-tablename1><span lang=EN-US>dnabanknumbers</span></span><span class=sql1-symbol1><span lang=EN-US>.</span></span><span class=sql1-identifier1><span lang=EN-US>GUID</span></span><span class=sql1-space><span lang=EN-US style='color:black'> </span></span><span class=sql1-reservedword1><span lang=EN-US>from</span></span><span class=sql1-space><span lang=EN-US style='color:black'> </span></span><span class=sql1-tablename1><span lang=EN-US>dnabanknumbers<o:p></o:p></span></span></pre><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Attached is a small sample table.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I’ve changed all datetime-columns to timestamp now but the same error appears in the log.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Best,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Gabi<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>Von:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Tim Robertson [GBIF] [mailto:trobertson@gbif.org] <br><b>Gesendet:</b> Donnerstag, 17. Oktober 2013 14:57<br><b>An:</b> Dröge, Gabriele<br><b>Cc:</b> ipt@lists.gbif.org<br><b>Betreff:</b> Re: [IPT] IPT bug -> MySQL<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Hi Gabi,<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thanks for reporting the issue. To make it easy for us to help you as quickly as possible could you please provide the following?<o:p></o:p></p></div><div><p class=MsoNormal> i) the actual log lines from the IPT logs<o:p></o:p></p></div><div><p class=MsoNormal> ii) the SQL statement you are using<o:p></o:p></p></div><div><p class=MsoNormal> iii) a small sample table so we can recreate the issue<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>This is a wild guess, but I suspect tat dateTime formats is not being handled gracefully. If possible can you please try timestamp SQL column format instead of dateTime to see if that narrows down the cause? (I understand this is not a viable fix, but might help identify the root of the issue)<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thanks,<o:p></o:p></p></div><div><p class=MsoNormal>Tim<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p><div><div><div><p class=MsoNormal>On Oct 17, 2013, at 2:44 PM, Dröge, Gabriele wrote:<o:p></o:p></p></div><p class=MsoNormal><br><br><o:p></o:p></p><div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Dear all,<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> <o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>I’ve downloaded the latest IPT version 2.0.5-r4398 and tried to map my data.</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>I want to use a MySQL database, but there seems to be a bug in IPT concerning MySQL.</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>IPT is not able to handle the SQL statement even though it is correct (tested on the MySQL server directly)</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Log file says:</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div style='margin-left:36.0pt'><p class=MsoNormal style='text-indent:-18.0pt'><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>1.</span><span lang=EN-US style='font-size:7.0pt'> <span class=apple-converted-space> </span></span><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Error in SQL (there is definitely no error in the sql statement)</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div style='margin-left:36.0pt'><p class=MsoNormal style='text-indent:-18.0pt'><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>2.</span><span lang=EN-US style='font-size:7.0pt'> <span class=apple-converted-space> </span></span><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>IPT expects one more column than the sql statement queries for (unclear why)</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div style='margin-left:36.0pt'><p class=MsoNormal style='text-indent:-18.0pt'><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>3.</span><span lang=EN-US style='font-size:7.0pt'> <span class=apple-converted-space> </span></span><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>IPT can’t handle datetime (unclear why)</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>A CSV file and a connection to a MS SQL Server did work.</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Preview looks like this:</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><image001.jpg><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>It would be great if someone could fix those bugs.</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Thanks in advance!</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Best wishes,</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Gabi</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>-----------------------------------------------------------------</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Gabriele Droege</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Coordinator - DNA Bank Network</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Global Genome Biodiversity Network (GGBN)</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Berlin-Dahlem DNA Bank</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Deputy Women's Officer ZE BGBM</span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> </span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Botanic Garden and Botanical Museum Berlin-Dahlem<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Freie Universität Berlin<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Koenigin-Luise-Str. 6-8<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>14195 Berlin<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Germany<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> <o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>+49 30 838 50 139<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> <o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><a href="http://www.dnabank-network.org">www.dnabank-network.org</a><o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><a href="http://www.ggbn.org">www.ggbn.org</a><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><a href="http://www.bgbm.org">www.bgbm.org</a></span><span style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:13.5pt;font-family:"Helvetica","sans-serif"'>_______________________________________________<br>IPT mailing list<br><a href="mailto:IPT@lists.gbif.org">IPT@lists.gbif.org</a><br><a href="http://lists.gbif.org/mailman/listinfo/ipt">http://lists.gbif.org/mailman/listinfo/ipt</a><o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p></div></div></div></body></html>