[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP to MSSQL DB: mssql_query(): Cannot insert the value NULL into column...

Posted on 2011-04-19
8
Medium Priority
?
1,218 Views
Last Modified: 2012-05-11
Below is the code I am trying to use to insert some vlaues into an MS SQL table.  Below that is a log of errors I am receiving.  This is done on Ubuntu 10.04, PHP5, unknown MS SQL version (althouhg I can find that out if it matters).
<?php
error_reporting(E_ALL);

//database credentials
$db_host = "xxx";
$db_user = "xxx";
$db_pass = "xxx";
$db = "xxx";
$table = "yyy";

//define variables
$type = $_GET['type'];
$ext = $_GET['ext'];
$did = $_GET['indid'];
$job = $_GET['job'];
//cid_name = $_GET['cidName'];
$cid_number = $_GET['cidNumber'];

// Connect to MSSQL
$link = mssql_connect($db_host, $db_user, $db_pass);

if (!$link || !mssql_select_db($db, $link)) {
    die('Unable to connect or select database!');
}
var_dump($link);

// Perform the query
$sql = "INSERT INTO $table (callID, calltype, extension, phonenumber, calldatetime) VALUES ($job, $type, $ext, $cid_number, CURRENT_TIMESTAMP)";
$result = mssql_query($sql);
//$row = mssql_fetch_array($result);

//echo $row[0];

?>

Open in new window


And the error:
[Tue Apr 19 16:40:23 2011] [error] [client 192.168.1.90] PHP Warning:  mssql_query(): message: Cannot insert the value NULL into column 'callID', table 'xxx.yyy'; column does not allow nulls. INSERT fails. (severity 16) in /var/www/cdr_sql.php on line 29
[Tue Apr 19 16:40:23 2011] [error] [client 192.168.1.90] PHP Warning:  mssql_query(): General SQL Server error: Check messages from the SQL Server (severity 16) in /var/www/cdr_sql.php on line 29
[Tue Apr 19 16:40:23 2011] [error] [client 192.168.1.90] PHP Warning:  mssql_query(): Query failed in /var/www/cdr_sql.php on line 29

Open in new window

0
Comment
Question by:lorsungcu
8 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35428665
looks like you are not passing any values to page

$job = $_GET['job'];

--> try this to see if message changes...

$job = $_REQUEST['job'];
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35428671
if message changes, replace all GET with REQUEST
0
 
LVL 6

Expert Comment

by:scifo_dk
ID: 35428694
The problem is that $job does not contain anything, therefore NULL.

Depending on what datatype it is, you should do something like:
If(isset($_GET['job'])) {
$job = $job = $_GET['job'];
} else {
$job = "Some default value";
}

Open in new window


That way, $job will always have a value. Depending on what you expect the users to write, its always a good idea to validate the fields before doing a query. Common saying is NEVER trust the user! :)

//Scifo_dk
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 2

Author Comment

by:lorsungcu
ID: 35428992
Ive replaced GET with REQUEST with no change to the error.  I will implement scifo's suggestion and let you know.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35429224
"I will implement scifo's suggestion"

he just say put a default value, but your problem is not default value... you expect the value from request...

how do you call this page or how do you use? you cannot call the page directly, some application or page should call this page... anyway, to test it modify your code like

#$job = $_REQUEST['job'];
$job = 1;

and try again...
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 35431445
replace the line
"$sql = "INSERT INTO $table (callID, calltype, extension, phonenumber, calldatetime) VALUES ($job, $type, $ext, $cid_number, CURRENT_TIMESTAMP)";"
 With
"$sql = "INSERT INTO $table (callID, calltype, extension, phonenumber, calldatetime) VALUES ("+$job+", "+$type+", "+$ext+", "+$cid_number+", CURRENT_TIMESTAMP)";"

In this way you will be passing the values as these variables which you are passing means nothing in SQL. Add quotes & type conversion where required.


0
 
LVL 9

Accepted Solution

by:
crazedsanity earned 1500 total points
ID: 35433613
If the page is called without any variables, or with variables missing, you'll get NULL values.  Your script should check for that; it seems that all variables are required for it to work properly.

if(isset($_GET['type'])) {
 $type = $_GET['type'];
}
else {
 $type = 'default value';
}
//... rinse, wash, repeat.

Open in new window

0
 
LVL 2

Author Closing Comment

by:lorsungcu
ID: 35722791
Ended up with something like this.

Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question