[SOLVED]Uncaught exception 'com_exception'

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

[SOLVED]Uncaught exception 'com_exception'

Post by ryuuka »

k this problem is going together with my previous one:

i keep getting the following error:
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.' in D:\wwwroot\gispen.net\gispen\periculum\dbaseconvert.php:43 Stack trace: #0 D:\wwwroot\gispen.net\gispen\periculum\dbaseconvert.php(43): com->Execute('INSERT INTO dbo...') #1 {main} thrown in D:\wwwroot\gispen.net\gispen\periculum\dbaseconvert.php on line 43
this happens with the following code:

Code: Select all

$rs = $objConn->Execute("SELECT DATEPART (mm, datum) AS maand, 
                                           DATEPART(week, datum) as weeknummer, DATEPART(yyyy, datum)AS jaar
			FROM dbo.gispen_printer_stats
			WHERE (naam = 'PR0150') AND (datum = '2006-7-25')");
	  
	  while (!$rs->EOF) //starting loop
      {
	      // getting data from the query
	      $weeknummer  = $rs->Fields['weeknummer']->value;
	      $maand             = $rs->Fields['maand']->value;
	      $jaar		= $rs->Fields['jaar']->value;
	      
	      echo "$maand <br>";            // output is 7
	      echo "$weeknummer<br>"; // output is 3
	      echo "$jaar<br>";               // output is 2006
	      
	$rsc = $objConn->Execute("INSERT INTO dbo.gispen_printer_stats (MaandNummer, Weeknummer, Jaar) 
	       		VALUES ('$maand', '$weeknummer', '$jaar') 
	       		WHERE (naam = 'PR0150') AND (datum = 2006-7-25)"); // Line 43		
			
	      $rs->MoveNext(); // end loop
      }
the query in question is the second one. it's supposed to be near the where
but for as far as i can tell all the code is correct.
can any of you see my mistake here?
Last edited by ryuuka on Fri Nov 03, 2006 3:45 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

INSERT queries cannot have a WHERE clause.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

For a start, if the method may throw an exception you should be ready to catch it irregardless:

Code: Select all

try {
    $rsc = $objConn->Execute("INSERT INTO dbo.gispen_printer_stats (MaandNummer, Weeknummer, Jaar)
                 VALUES ('$maand', '$weeknummer', '$jaar')
                 WHERE (naam = 'PR0150') AND (datum = 2006-7-25)");
} catch (com_exception $e) {
    echo "Exception running query: " . $e->getMessage();
}
Onto the actual cause. You can't have a WHERE clause in an INSERT statement. Are you maybe needing UPDATE?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

k i used the UPDATE command
and something interresting happend:

it worked as intended for the first record but the following are all wrong :?

the date: 3-8-2006
the first had:
the month number 8(good)
the week number 31(good)
and year number 2006 (good)

the second and every single one after that had:
the month number 7 (wrong)
the week number 30(wrong)
the year number 2006(right)

the week/month/year numbers were all NULL BTW

and it's like that for every single date 8O :? :x

revised code:

Code: Select all

$rs = $objConn->Execute("SELECT DATEPART (mm, datum) AS maand, 
                                DATEPART(week, datum) as weeknummer, 
                                DATEPART(yyyy, datum)AS jaar
		FROM dbo.gispen_printer_stats
		WHERE (MaandNummer IS NULL)");

while (!$rs->EOF)
{
  //$datum           = $rs->Fields['datum']->value;
  $weeknummer  = $rs->Fields['weeknummer']->value;
  $maand	           = $rs->Fields['maand']->value;
  $jaar	           = $rs->Fields['jaar']->value;
	      
      
$rsc = $objConn->Execute("UPDATE gispen_printer_stats SET 
				MaandNummer = $maand, 
				Weeknummer = $weeknummer, 
				Jaar = $jaar 
				WHERE (MaandNummer IS NULL)");

	      $rs->MoveNext();
      }
any thoughts on this?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

"UPDATE gispen_printer_stats SET
MaandNummer = $maand,
Weeknummer = $weeknummer,
Jaar = $jaar
WHERE (MaandNummer IS NULL)"
This updates all records having MaandNummer IS NULL.
Therefore the first time the body of while (!$rs->EOF) is executed the fields of all matching records are updated. At the next iteration there is no record with MaandNummer IS NULL anymore.
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

ah been wondering about that for a long time

any suggestions on how to fix this(meaning correct for each individual post)?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Code: Select all

UPDATE
	gispen_printer_stats
SET
	MaandNummer = DATEPART (mm, datum),
	Weeknummer = DATEPART(week, datum),
	Jaar = DATEPART(yyyy, datum)
WHERE
	(MaandNummer IS NULL)
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

ah thanks that did the trick

all is well in dbase country
Post Reply