Herikstad Solutions: December 2009

Tuesday, December 15, 2009

Getting the auto increment id from SQL insert in Java

Often you need the id or autoincremented values of a row you have inserted into the database. To get this in Java do the following:

String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

The key here is the Statement.RETURN_GENERATED_KEYS. Now you can get the auto incremented keys by doing the following:

rs= stmt.getGeneratedKeys();
rs.next();
auto_id = rs.getInt(1);

It's also possible to do this with regular statements (not prepareStatements) like this:

stmt = conn.createStatement();
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

Solution source: http://stackoverflow.com/questions/1376218/is-ther-a-way-to-retrieve-the-autoincrement-id-from-a-prepared-statement

Yearweek function in SQL

A neat function in SQL which gives you the year and the week of the date it is presented with:
SELECT YEARWEEK('2009-12-23')
returns 200951, that is the 51 week of 2009.

SELECT YEARWEEK('2010-01-02')
returns 200952, the last week in 2009. As one can see the year here is different from the input value, this is because January 2nd 2010 is a Saturday and therefore belongs to the last week in 2009. First week of 2010 begins on Sunday 3rd or Monday 4th depending on how it is defined in your country.

Solution source: http://www.java2s.com/Tutorial/MySQL/0280__Date-Time-Functions/YEARWEEKdateYEARWEEKdatemodereturnsyearandweekforadate.htm

Wednesday, December 9, 2009

Bare LFs in SMTP, escaping newlines in mail

You might have received the page http://cr.yp.to/docs/smtplf.html when trying to send mail using the PHP mail function. The reason is that linebreaks must be escaped in emails.

Usually a linebreak is a \n (or LF, line feed), however when sending mails the standard says this LF must be escaped with a \r (or CR, carriage return). so \n shuold be \r\n

Escaping the email text like the following will do the trick:
$escapedString = str_replace("\r\r\n", "\r\n", str_replace("\n", "\r\n", $unescapedString));

Dynamically add POST form in PHP and javascript

Sometimes you want to send data as POST to a page based on a button clicked. A possibility is to generate this form on the fly using javascript:


<html>
<body>
<script type="text/javascript">
//Create form to send values as post to the update status page.
var form = document.createElement("form");

form.setAttribute("method", "post");
form.setAttribute("action", "computationpage.php");

var hiddenField = document.createElement("input");              
hiddenField.setAttribute("name", "inputValue1");
hiddenField.setAttribute("value", 999);
form.appendChild(hiddenField);

var hiddenField = document.createElement("input");              
hiddenField.setAttribute("name", "inputValue2");
hiddenField.setAttribute("value", "this is a string");
form.appendChild(hiddenField);

//Create hidden div to contain the form.
var div = document.createElement("div");
div.setAttribute("style", "visibility:hidden");
div.appendChild(form);
document.body.appendChild(div);

form.submit()
</script>
</body>
</html>

This code would when call, create a form, add it to a hidden div at the bottom of the page and submit the form.

EDIT: added HTML, BODY, and SCRIPT tags. This is all that should be needed for this script to work standalone (its not valid XHTML 1.0 Strict though :)).

Tuesday, December 8, 2009

Open link in new window

To open a link or result of a form in a new window the target attribute can be used:
<a href="test.html" target="_blank">test</a>
or for forms:
<form target="_blank" ...></form>

Solution source: http://stackoverflow.com/questions/178964/javascript-post-on-form-submit-open-a-new-window

Reloading page using javascript

Two ways to reload a page using javascript:
window.location.reload();
and
window.location.href = window.opener.href;

both will send GET parameters. The former will also send POST parameters (usually you get a pop-up box asking if you would like to re-send post parameters or not do the page reload at all (which can be annoying)).

To set what GET parameters should be used use among other things the split() function on the string window.location.href:

splitArray = window.location.href.split("?");
window.location.href = splitArray[0];

and get parameters by splitting the second part of the splitArray on the &.

Adding opener after window will address the reload to the window which opened the current page:
window.opener.location.href

Solution sources: http://bytes.com/topic/javascript/answers/648770-problems-reloading-same-page-new-parameters

Table cellspacing and cellpadding using CSS

Setting cellspacing and cellpadding through CSS is done using the following attributes:
* padding → cellpadding
* border-spacing → cellspacing
* border-collapse → no HTML equivalent

Solution source: http://stackoverflow.com/questions/339146/why-are-cellspacing-and-cellpadding-not-css-styles