Never has a half-time song at Hampden felt more relevant than the Tartan Army belting out the words "We're half way there, livin' on a prayer".

Scotland were level at 0-0 following a demanding first half of one-way traffic against the French. They were one of the best teams in the world and had enjoyed complete dominance, even hitting the woodwork, before the break.

The supporters in the stand were happy to settle for a goalless draw with the World Cup runners-up. But what was about to unfold next exceeded all hopes and dreams inside the national stadium.

Like Scotland, France also faced a challenge in qualifying for Euro 2008. They also had to overcome the likes of world champions Italy and quarter-finalists Ukraine if they were to make up for their heartache in Berlin that summer.

They'd painfully missed out on 2006 World Cup glory, losing the final on penalties, but they were out to prove a point and show they could match the side that triumphed in Euro 2000.

There have been some special memories at Hampden over the years but few come close to the day - to the minute - Gary Caldwell sunk the side who were expected to rip us apart on our own turf.

"We believed we could get something from the game," Caldwell told STV. "Walter Smith had given us a new found belief. He'd come in at a time the country was at a low and he gave us the confidence back."

It may be 10 years to the day but this started long before that. Let's turn the clock back to March 2002. It was Berti Vogts' first game in charge and France had demolished Scotland 5-0 in Paris.

In a haunting image Caldwell was taunted by David Trezeguet demonstrating with his fingers how many goals France had scored...and how few Scotland had scored. It not only epitomised the French team of that era - dominant, controlling but also arrogant in victory - it showed Scotland for what they really were too - weak, bullied and lacking in character.

Four-and-a-half years later and with two wins out of two in the Euro 2008 qualifiers, fans filled Hampden in typically optimistic, yet realistic, mood. The longer Scotland kept the French at bay, though, the more the confidence grew.

They had come to appreciate the style Smith had imposed on the side. It may not always have been pretty but it was strong and clinical. And they knew it had to be a similar stuffy display if they were to pull off any kind of shock result.

"That day the fans were fantastic," Caldwell recalled. "We got in 0-0 at half time and they sensed something could happen. They could see France were getting frustrated. From half time onwards the belief in the crowd grew."

The fans, the players, the atmosphere - we were building up to something.

Then just after the hour mark, and a matter of minutes after Trezeguet was subbed off, Scotland's moment arrived.

"It was two corners in quick succession," Caldwell explained. "It was the two minutes we'd actually got out of our own half in the whole game, we were defending for large periods and got a bit of respite getting into their half.

"It was the first real bit of pressure we'd put them under in the game. Paul Hartley swung it in and I just remember wrestling with my marker, I think it was Abidal, and managing to get free from him.

"I managed to catch it on my left foot as it was bouncing and I said at the time it was more like a tackle. I just had to get some contact on the ball and thankfully it went in.

"Then all hell breaks loose at Hampden at that point, everyone's so happy and excited. I went to the corner and you don't know what you're thinking at that point. It's an out-of-body experience, you've lost control and everyone celebrating in the corner was incredible.

"You get about 10-20 seconds of that and then your professional head switches back on."

A united sense of disbelief was sweeping around the stadium. Could we actually do it?

France had their superstars - Trezeguet, Thierry Henry, Lilian Thuram, Patrick Vieira - but Scotland had a team of fighters who were standing strong together until the final whistle.

An agonising 23 minutes, plus stoppage time, stood between Scotland and a remarkable three points.

We were on the verge of arguably the greatest result at Hampden in a generation. It has still to be exceeded even to this day.

But while everyone was gripped with hope and worry, desperately watching the clock tick, Caldwell was enjoying a new-found sense of confidence.

"By that point the French had tried everything," he continued. "They knew they were up against a determined and organised team. They'd run out of ideas near the end so it was probably the easier part of the game because we were in control.

"We knew we had the goal and we could see the game out by sitting deep. They didn't actually create many great chances within that period so although we were under pressure we didn't look like conceding in that spell."

Seconds remained. No one blinked. Hands covered mouths. Waiting. Wishing away the seconds.

Then came the long free kick from just inside Scotland's half. It was headed goalwards from the 18-yard line. But it went straight into Craig Gordon's hands.

It's an iconic moment as the keeper fell to the ground, holding the ball, to the sound of the referee's whistle. His baggy gold top blew in the wind as he jumped to his knees celebrating.

His raised arms and cries of joy were echoed around the stadium. Everyone was lost in the same moment of euphoria.

"It was almost a bit of disbelief at the full time whistle, you think 'is it over?'" Caldwell said. "To beat a team of France's stature at that time and make such a great start to the campaign, there was firstly relief and then the enjoyment.

"It was an incredible feeling you don't get too often, winning games of that size and stature, you remember it forever."

Supporters were singing, cheering, some even crying as they danced on their seats. Normally an act associated with unrest or animosity, but this was about children wanting to see and adults wanting to see more.

It was one of the standout results from the campaign where Scotland, the dark horses, came so close to ending their wait for qualification. Unlike other times where we've narrowly missed out, this one is remembered both fondly and with sentimental heartache.

Caldwell, now in charge of Wigan Athletic, says the players owe everything to Smith for pulling off the shock result.

"Now that I'm in that position and I can see what he had to think about and what he would have went through in the build up to that game, I think the manager deserves tremendous credit," he said.

"How he prepared us defensively, the detail he went into stopping them playing was incredible. It's probably the one game where everything the manager said would happen did happen.

"He spoke about defending deep, denying them space in behind, hitting them on the counter and as the game wore on getting more opportunities. He even mentioned scoring from a set play.

"For everything to fall into place, I know now it doesn't happen very often when you prepare for a game, so for Walter to plan the way he did and for everything to happen like that he deserves tremendous credit.

"Thanks to him we had a defensive structure that made us hard to beat first and foremost and then as we got results ou r confidence grew and we started winning more games.

"Especially playing at Hampden, Scotland has always exceeded expectations playing at home over the years. When I think back through my career the best games I played were home internationals at Hampden."

Caldwell was never a player you would call a Scotland hero - he wasn't an inspirational figure or the headline grabber. His loyalty and commitment were never in doubt though and he went on to win a special place in the hearts of the Tartan Army that Saturday evening.

"I think it's the best moment in my Scotland career," he admitted. "It came quite early and I said I hoped I would eclipse it, and getting to a major finals would have done that, but I didn't manage to do that.

"When you finish playing you look back on certain things you did and playing for my country was always the highlight for me. It was always the pinnacle of my career to play any game for my country but to score the winning goal at Hampden is definitely one of the highlights of my career."

Ask any Scotland fan under the age of 30 what the greatest result at home has been in their lifetime and most will say beating France.

It may not have had any long term impact in terms of reaching a major tournament but try telling every supporter inside Hampden that afternoon that the goal, the result and the emotions that pumped through them didn't mean anything.