Insert using odbc driver

ellchr3 · May 2, 2013

Hey all. I'm having trouble getting an insert to work using an odbc driver. I have to use the odbc connection because I'm connecting to Sybase IQ. Below is the error and code. The second set of code is the page where the record is actually entered.

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\provider_database\provider_insert_odbc.php on line 78

<?php //================================================================ // Configure connection parameters $db_host = "X"; $db_server_name = "X"; $db_name = "X"; $db_file = 'X'; $db_conn_name = "X; $db_user = "X"; $db_pass = "X"; //================================================================ $connect_string = "Driver={SQL Anywhere 11};". "CommLinks=tcpip(Host=$db_host);". "ServerName=$db_server_name;". "DatabaseName=$db_name;". "DatabaseFile=$db_file;". "ConnectionName=$db_conn_name;". "uid=$db_user;pwd=$db_pass"; // Connect to DB $conn = odbc_connect($connect_string,'',''); if (!$conn) {exit("Connection Failed: " . $conn);} // sybase_select_db("crr1", $con); $sql = "INSERT INTO `providers_bk` ( `dea_number` , `facility_application_name` , `facility_provider_number` , `npi_number` , `pager_number` , `provider_classification_code` , `provider_department` , `provider_division` , `provider_first_name` , `provider_last_name` , `provider_middle_name` , `provider_name_suffix` , `provider_specialty` , `provider_subspeciality` , `provider_full_name` , `provider_status` , `practice_group_name` , `provider_id` , `active_date_id` , `inactive_date_id` , `admitting_privileges_indicator` , `source_system_type` , `provider_number` , `hdw_status` , `hdw_creation_time` , `hdw_modified_time` , `hdw_deleted_time` , `provider_age` , `appointment_date` , `date_of_birth` , `fellowship` , `gender` , `is_employed` , `license` , `medical_school` , `or_specialty_group` , `practice_address` , `practice_city` , `practice_state` , `practice_zip` , `residency` , `title` , `todays_date` ) VALUES ( '($_POST['dea_number'])' ,'($_POST['facility_application_name'])' ,'($_POST['facility_provider_number'])' ,'($_POST['npi_number'])' ,'($_POST['pager_number'])' ,'($_POST['provider_classification_code'])' ,'($_POST['provider_department'])' ,'($_POST['provider_division'])' ,'($_POST['provider_first_name'])' ,'($_POST['provider_last_name'])' ,'($_POST['provider_middle_name'])' ,'($_POST['provider_name_suffix'])' ,'($_POST['provider_specialty'])' ,'($_POST['provider_subspeciality'])' ,'($_POST['provider_full_name'])' ,'($_POST['provider_status'])' ,'($_POST['practice_group_name'])' ,'($_POST['provider_id'])' ,NULL ,NULL ,'($_POST['admitting_privileges_indicator'])' ,'Web Interface' ,'($_POST['provider_number'])' ,'active' ,GETDATE() ,NULL ,NULL ,'($_POST['provider_age'])' ,'($_POST['appointment_date'])' ,'($_POST['date_of_birth'])' ,'($_POST['fellowship'])' ,'($_POST['gender'])' ,'($_POST['is_employed'])' ,'($_POST['license'])' ,'($_POST['medical_school'])' ,'($_POST['or_specialty_group'])' ,'($_POST['practice_address'])' ,'($_POST['practice_city'])' ,'($_POST['practice_state'])' ,'($_POST['practice_zip'])' ,'($_POST['residency'])' ,'($_POST['title'])' ,'($_POST['todays_date'])' ) " ; $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} echo '<center>'; echo "1 record added"; echo '<br><br>'; echo '<a href="localhost\index.html">Click here to enter another record</a>'; echo '</center>'; odbc_close($conn); ?>


<?php
require_once('calendar/classes/tc_calendar.php');
?>
<html>
<head>


<link href="calendar/calendar.css" rel="stylesheet" type="text/css" />
<script language="javascript" src="calendar/calendar.js"></script>



</head>
<body bgcolor="white">
<center><table bgcolor="#CCCCCC" width="30%" border="0" cellpadding="2" cellspacing="0">
<form action="provider_insert_odbc.php" method="post">
 <img src ="NewLogo.png">
 <center>
<font size = "12">Provider Database </font>
</center>
<tr>
<td align="right">Warehouse ID: </td><td><input type="text" size="35" tabindex="1" name="provider_id" /></td>
</tr>
<tr>
<td align="right">First Name: </td><td><input type="text" size="35" tabindex="1" name="provider_first_name" /></td>
</tr>
<tr>
<td align="right">Middle Name: </td><td><input type="text" size="25" tabindex="2" name="provider_middle_name" /></td>
</tr>
<tr>
<td align="right">Last Name: </td><td><input type="text" size="35" tabindex="3" name="provider_last_name" /></td>
</tr>
<tr>
<td align="right">Suffix: </td><td><input type="text" size="35" tabindex="3" name="provider_name_suffix" /></td>
</tr>
<tr>
<td align="right">Full Name: </td><td><input type="text" size="50" tabindex="4" name="provider_full_name" /></td>
</tr>
<tr>
<td align="right">Title: </td><td><input type="text" size="5" tabindex="5" name="title" /></td>
</tr>
<tr>
<td align="right">Practice Address: </td><td><input type="text" size="50" tabindex="6" name="practice_address" /></td>
</tr>
<tr>
<td align="right">Practice City: </td><td><input type="text" tabindex="7" name="practice_city" /><br></td>
</tr>
<tr>
<td align="right">Practice State: </td><td><input type="text" tabindex="8" name="practice_state" /><br></td>
</tr>
<tr>
<td align="right">Practice Zip: </td><td><input type="text" tabindex="9" name="practice_zip" /><br></td>
</tr>
<tr>
<td align="right">DEA Number: </td><td><input type="text" size="35" tabindex="10" name="dea_number" /></td>
</tr>
<tr>
<td align="right">NPI Number: </td><td><input type="text" size="35" tabindex="11" name="npi_number" /></td>
</tr>
<tr>
<td align="right">License Number: </td><td><input type="text" size="35" tabindex="12" name="license" /></td>
</tr>
<tr>
<td align="right">Facility Name: </td><td><input type="text" size="35" tabindex="13" name="facility_application_name" /></td>
</tr>
<tr>
<td align="right">Facility Number: </td><td><input type="text" size="35" tabindex="14" name="facility_provider_number" /></td>
</tr>
<tr>
<td align="right">Pager Number: </td><td><input type="text" size="35" tabindex="15" name="pager_number" /></td>
</tr>
<tr>
<td align="right">Classification Code: </td><td><input type="text" size="35" tabindex="16" name="provider_classification_code" /></td>
</tr>
<tr>
<td align="right">Department: </td><td><input type="text" size="35" tabindex="17" name="provider_department" /></td>
</tr>
<tr>
<td align="right">Division: </td><td><input type="text" size="35" tabindex="18" name="provider_division" /></td>
</tr>
<tr>
<td align="right">Specialty: </td><td><input type="text" size="35" tabindex="19" name="provider_specialty" /></td>
</tr>
<tr>
<td align="right">SubSpecialty: </td><td><input type="text" size="35" tabindex="20" name="provider_subspeciality" /></td>
</tr>
<tr>
<td align="right">Status: </td><td><select name="provider_status" tabindex="21"><option value='Aligned'>Aligned</option>
															 <option value='Unaffiliated'>Unaffiliated</option>
															 <option value='UMG'>UMG</option>
								   </select />
</td>
<tr>
<td align="right">Group Name: </td><td><input type="text" size="35" tabindex="22" name="practice_group_name" /></td>
</tr>
<tr>
<td align="right">Admitting Privileges Indicator: </td><td><input type="text" size="35" tabindex="23" name="admitting_privileges_indicator" /></td>
</tr>
<tr>
<td align="right">Provider Number: </td><td><input type="text" size="35" tabindex="24" name="provider_number" /></td>
</tr>
<tr>
<td align="right">Provider Age: </td><td><input type="text" size="35" tabindex="25" name="provider_age" /></td>
</tr>
<tr>
<td align="right">Appointment Date: </td><td><input type="text" size="35" tabindex="26" name="appointment_date" /></td>
</tr>
<tr>
<td align="right">Date of Birth: </td><td><input type="text" size="35" tabindex="27" name="date_of_birth" /></td>
</tr>
<tr>
<td align="right">Fellowship: </td><td><input type="text" size="35" tabindex="28" name="fellowship" /></td>
</tr>
<tr>
<td align="right">Gender: </td><td><select name="gender" tabindex="29"><option value='Male'>Male</option>
															 <option value='Female'>Female</option>
															 <option value='Other'>Other</option>
								   </select />
</td>
</tr>
<tr>
<td align="right">Is Employed: </td><td><input type="text" size="35" tabindex="30" name="is_employed" /></td>
</tr>
<tr>
<td align="right">Medical School: </td><td><input type="text" size="35" tabindex="31" name="medical_school" /></td>
</tr>
<tr>
<td align="right">OR Specialty Group: </td><td><input type="text" size="35" tabindex="32" name="or_specialty_group" /></td>
</tr>
<tr>
<td align="right">Residency: </td><td><input type="text" size="35" tabindex="33" name="residency" /></td>
</tr>
<tr>
<td align="right">Todays Date: </td><td><input type="text" size="35" tabindex="34" name="todays_date" /></td>
</tr>
<tr><td align="right">Todays Date: </td><td>
<?php
					  $myCalendar = new tc_calendar("date5", true, false);
					  $myCalendar->setIcon("calendar/images/iconCalendar.gif");
					  //$myCalendar->setDate(date('d'), date('m'), date('Y'));
					  $myCalendar->setPath("calendar/");
					  $myCalendar->setYearInterval(2000, 2015);
					  $myCalendar->dateAllow('2008-05-13', '2015-03-01');
					  $myCalendar->setDateFormat('j F Y');
					  //$myCalendar->setHeight(350);
					  //$myCalendar->autoSubmit(true, "form1");
					  $myCalendar->setAlignment('left', 'bottom');
					  $myCalendar->setSpecificDate(array("2011-04-01", "2011-04-04", "2011-12-25"), 0, 'year');
					  $myCalendar->setSpecificDate(array("2011-04-10", "2011-04-14"), 0, 'month');
					  $myCalendar->setSpecificDate(array("2011-06-01"), 0, '');
					  $myCalendar->writeScript();
					  ?>
</td></tr>



<tr>
<td><td><input type="submit" /></td></td>
</tr>
</table>
<a href="http://192.168.1.20/index.html">Click here to go back </a>
</center>
</form>
</body>
</html>

ellchr3 · May 2, 2013

Sorry for the first set of code showing as text only. I've added it here too for easier readability.

<?php
//================================================================

  // Configure connection parameters
  $db_host        = "X";
  $db_server_name = "X";
  $db_name        = "X";
  $db_file        = 'X';
  $db_conn_name   = "X";
  $db_user        = "X";
  $db_pass        = "X";

//================================================================
  $connect_string = "Driver={SQL Anywhere 11};".
                    "CommLinks=tcpip(Host=$db_host);".
                    "ServerName=$db_server_name;".
                    "DatabaseName=$db_name;".
                    "DatabaseFile=$db_file;".
                    "ConnectionName=$db_conn_name;".
                    "uid=$db_user;pwd=$db_pass";

  // Connect to DB
  
  $conn = odbc_connect($connect_string,'','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
  
 
// sybase_select_db("crr1", $con);


$sql = "INSERT INTO `providers_bk` (
  `dea_number`
, `facility_application_name`
, `facility_provider_number`
, `npi_number`
, `pager_number`
, `provider_classification_code`
, `provider_department`
, `provider_division`
, `provider_first_name`
, `provider_last_name`
, `provider_middle_name`
, `provider_name_suffix`
, `provider_specialty`
, `provider_subspeciality`
, `provider_full_name`
, `provider_status`
, `practice_group_name`
, `provider_id`
, `active_date_id`
, `inactive_date_id`
, `admitting_privileges_indicator`
, `source_system_type`
, `provider_number`
, `hdw_status`
, `hdw_creation_time`
, `hdw_modified_time`
, `hdw_deleted_time`
, `provider_age`
, `appointment_date`
, `date_of_birth`
, `fellowship`
, `gender`
, `is_employed`
, `license`
, `medical_school`
, `or_specialty_group`
, `practice_address`
, `practice_city`
, `practice_state`
, `practice_zip`
, `residency`
, `title`
, `todays_date`
)        
VALUES (
 '($_POST['dea_number'])'
,'($_POST['facility_application_name'])'
,'($_POST['facility_provider_number'])'
,'($_POST['npi_number'])'
,'($_POST['pager_number'])'
,'($_POST['provider_classification_code'])'
,'($_POST['provider_department'])'
,'($_POST['provider_division'])'
,'($_POST['provider_first_name'])'
,'($_POST['provider_last_name'])'
,'($_POST['provider_middle_name'])'
,'($_POST['provider_name_suffix'])'
,'($_POST['provider_specialty'])'
,'($_POST['provider_subspeciality'])'
,'($_POST['provider_full_name'])'
,'($_POST['provider_status'])'
,'($_POST['practice_group_name'])'
,'($_POST['provider_id'])'
,NULL
,NULL
,'($_POST['admitting_privileges_indicator'])'
,'Web Interface'
,'($_POST['provider_number'])'
,'active'
,GETDATE()
,NULL
,NULL
,'($_POST['provider_age'])'
,'($_POST['appointment_date'])'
,'($_POST['date_of_birth'])'
,'($_POST['fellowship'])'
,'($_POST['gender'])'
,'($_POST['is_employed'])'
,'($_POST['license'])'
,'($_POST['medical_school'])'
,'($_POST['or_specialty_group'])'
,'($_POST['practice_address'])'
,'($_POST['practice_city'])'
,'($_POST['practice_state'])'
,'($_POST['practice_zip'])'
,'($_POST['residency'])'
,'($_POST['title'])'
,'($_POST['todays_date'])'
)
"
;
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
		

		
		echo '<center>';
		echo "1 record added";
		echo '<br><br>';
		echo '<a href="localhost\index.html">Click here to enter another record</a>';
		echo '</center>';
		
		
		odbc_close($conn);
		
		?>

DavidAM · May 2, 2013

The problem is with the way you have included variables in that string:

# This is incorrect
VALUES (
 '($_POST['dea_number'])'

# This is correct -- use curly braces
VALUES (
 '{$_POST['dea_number']}'

You will have to change all of those embedded variables.

ellchr3 · May 2, 2013

Awesome. That did take care of that error. Now I'm getting :

Warning: odbc_exec() [function.odbc-exec]: SQL error: [sybase][ODBC Driver][sybase IQ]Syntax error near '`' on line 1, SQL state 37000 in SQLExecDirect in C:\provider_database\provider_insert_odbc.php on line 124

Error in SQL

Would that have to do with the literals I'm attempting to insert? I tried to follow some examples I've found but they vary so much. I'm still VERY new to PHP so I apologize. Line 124 is my $rs=odbc_exec($conn,$sql); statement.

Also, I use notepad++ to write my code. Is there something that would work better as far as parsing out /debugging code?

Thanks,

Chris

DavidAM · May 2, 2013

Using back-ticks ( ` ) to surround column and table names, is something I have only ever seen in mySql. I don't know if it is supported in any other database server. It is ONLY necessary in mySql if the column/table name matches a reserved word. I never use it unless I am forced to work with a database that I can't change, and then only when the specific column name presents a problem. I would take them out of that code and see where that leads.

When working in Windows, I prefer Notepad++, myself. There are plenty of other editors that support highlighting, etc. And there are several IDE's that support debugging and so forth. I don't have any experience with any of them, so I can't make a recommendation.

ellchr3 · May 2, 2013

David,

I worked through a few other smaller errors I was able to figure out but now am getting the one below. It still seems something is off in my sql statement. I added the code as it appears after the changes that have been made so far.

Warning: odbc_exec() [function.odbc-exec]: SQL error: [sybase][ODBC Driver][sybase IQ]Syntax error near ',' on line 50, SQL state 37000 in SQLExecDirect in C:\provider_database\provider_insert_odbc.php on line 125

  $conn = odbc_connect($connect_string,'','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
  
 
// sybase_select_db("crr1", $con);


$sql = "INSERT INTO provider_dim_bk (
  dea_number
, facility_application_name
, facility_provider_number
, npi_number
, pager_number
, provider_classification_code
, provider_department
, provider_division
, provider_first_name
, provider_last_name
, provider_middle_name
, provider_name_suffix
, provider_speciality
, provider_subspeciality
, provider_full_name
, provider_status
, practice_group_name
, provider_id
, active_date_id
, inactive_date_id
, admitting_privileges_indicator
, source_system_type
, provider_number
, hdw_status
, hdw_creation_time
, hdw_modified_time
, hdw_deleted_time
, provider_age
, appointment_date
, date_of_birth
, fellowship
, gender
, is_employed
, license
, medical_school
, or_specialty_group
, practice_address
, practice_city
, practice_state
, practice_zip
, residency
, title
, todays_date
)        
VALUES (
 '{$_POST['dea_number']}'
,'{$_POST['facility_application_name']}'
,{$_POST['facility_provider_number']}
,'{$_POST['npi_number']}'
,'{$_POST['pager_number']}'
,'{$_POST['provider_classification_code']}'
,'{$_POST['provider_department']}'
,'{$_POST['provider_division']}'
,'{$_POST['provider_first_name']}'
,'{$_POST['provider_last_name']}'
,'{$_POST['provider_middle_name']}'
,'{$_POST['provider_name_suffix']}'
,'{$_POST['provider_specialty']}'
,'{$_POST['provider_subspeciality']}'
,'{$_POST['provider_full_name']}'
,'{$_POST['provider_status']}'
,'{$_POST['practice_group_name']}'
,'{$_POST['provider_id']}'
,NULL
,NULL
,'{$_POST['admitting_privileges_indicator']}'
,'Web Interface'
,'{$_POST['provider_number']}'
,'active'
,GETDATE()
,NULL
,NULL
,'{$_POST['provider_age']}'
,'{$_POST['appointment_date']}'
,'{$_POST['date_of_birth']}'
,'{$_POST['fellowship']}'
,'{$_POST['gender']}'
,'{$_POST['is_employed']}'
,'{$_POST['license']}'
,'{$_POST['medical_school']}'
,'{$_POST['or_specialty_group']}'
,'{$_POST['practice_address']}'
,'{$_POST['practice_city']}'
,'{$_POST['practice_state']}'
,'{$_POST['practice_zip']}'
,'{$_POST['residency']}'
,'{$_POST['title']}'
,'{$_POST['todays_date']}'
)
"
;
//$rs=
odbc_exec($conn,$sql);
//if (!$rs)
  //{exit("Error in SQL");}
		

		
		echo '<center>';
		echo "1 record added";
		echo '<br><br>';
		echo '<a href="localhost\index.html">Click here to enter another record</a>';
		echo '</center>';
		
		
		odbc_close($conn);

mac_gyver · May 3, 2013

you need to echo out $sql so that you can see what it actually is at the point where the error is being reported.

i'll guess the value in $_POST['facility_provider_number'] is empty, resulting in sql syntax of 'some value',,'some value'

ellchr3 · May 3, 2013

You were correct on that. I removed the single quotes around {$_POST['facility_provider_number']} because I was getting "...Cannot convert to a int(4) (column facility_provider_number)..." though. Adding the single quotes back and populating it with an integer worked. Thanks!

Sign In

Insert using odbc driver

Recommended Posts

ellchr3

Link to comment

Share on other sites

ellchr3

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

ellchr3

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

ellchr3

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

ellchr3

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information