Not receiving all data through ODBC Connection

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
phpMSQL
Forum Newbie
Posts: 7
Joined: Mon Jun 04, 2007 9:55 am

Not receiving all data through ODBC Connection

Post by phpMSQL »

I use ODBC connections to my MS SQL 2000 database, and it appears that I am not getting all the information back that I need to. I have the connection set to 2gb, I am not aware of any size limit. I have searched the WWW and have found nothing.

I am running IIS 5 and have restarted the services, because I have tried to set the odbc connection 0 (pass through, which I know can cause failures), and then back to 2gb, which is a text blob in mssql2k.

Still the same daggon results. data is getting cuttoff.

Under pressure. Help soon would be great.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you show the code you are using to make the connection (without the credentials please) as well as the query? And if it uses a stored proc, can you post the code of the proc as well?
phpMSQL
Forum Newbie
Posts: 7
Joined: Mon Jun 04, 2007 9:55 am

Post by phpMSQL »

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Code: Select all

<?php
/*
This file should never be called from any UI file; only from a class.
The class should include this with "<[include][require]> once"
*/
class dbToolBox{
	//cms variables
	public $sql;
	public $Admin;

	//x-cut variables
	private $dbConnectString;
	private $publicUserName;
	private $publicPassword;
	private $odbc_error_message_public;

	public function __construct(){
		$this->Admin = false;
		$this->odbc_error_message_public = 'The Web site is down due to upgrades and will be back up shortly. Please check back soon.';
		$this->setDBConnData("local_gedit_public");
	}
	
	private function setDBConnData($name){
		if (file_exists("dbConn.xml")) {
			$dbConnFile = "dbConn.xml";
		}elseif(file_exists("../dbConn.xml")){
			$dbConnFile = "../dbConn.xml";
		} else {
			exit('The site is currently down for maintenance. Please check back soon.');
		}
		if (file_exists($dbConnFile)) {
			$oDBData = @simplexml_load_file($dbConnFile);
			$oDBData->asXML();
			foreach($oDBData->dbConn as $dbConnFile){
				if($dbConnFile->name == $name){
					$this->dbConnectString = trim($dbConnFile->connStr);
					$this->publicUserName = trim($dbConnFile->user);
					$this->publicPassword = trim($dbConnFile->pass);
				}
			}
		}
	}
	
	public function executeSQL(){
		error_reporting(0);
		if($this->Admin){
			$this->setDBConnData("local_gedit_admin");
		}
		try{
			//first establish the connection
			if(!$this->dbConnect = odbc_pconnect($this->dbConnectString, $this->publicUserName, $this->publicPassword)){
				throw new Exception(odbc_errormsg($this->dbConnect));
			}
			//then try to execute the SQL
			if (!$rs = odbc_exec($this->dbConnect, $this->sql)){
				throw new Exception(odbc_errormsg($this->dbConnect));
			}
			if (odbc_num_rows($rs) == -1){
				return $rs;
			}else{
				return 0;
			}
		}catch (Exception $e){
			return 0;
		}
		error_reporting(E_ALL);
	}
	
	public function formatText_SQL_Input($text){
		if (get_magic_quotes_gpc()) { $text = stripslashes($text); }			
		$text = str_replace('"', """, $text);
		$text = str_replace("'", "''", $text);
		return $text;
	}
	
	public function formatText_SQL_Output($text){
		$text = str_replace(""", '"', $text);
		$text = str_replace("''", "'", $text);
		return $text;
	}
	
	public function __destruct(){
		error_reporting(0);
		try{
			odbc_free_result($this->dbConnect);
			odbc_close_all;
			return 1;
		}catch (Exception $e){
			return 0;
		}
		error_reporting(E_ALL);
	}
	
}

?>

Code: Select all

<?
//FUCTION THAT CALLS THE DBASE PROCEDURE
	public function contentRecord($gTask, $userID, $siteID){
		$oDBTB = new dbToolBox;
		$oDBTB->Admin = true;

		if($gTask =="create"){
			$oDBTB->sql = "spAdmin_ContentRecord_Insert";
		}else{
			$oDBTB->sql = "spAdmin_ContentRecord_Update $this->contentID, ";
		}
		$oDBTB->sql .= " '$this->name', '" . stripslashes($this->descriptionCleaned) . "', $this->linkBackToContent, '" . stripslashes($this->contentCleaned) . "', $this->luContentTypeID, $userID, $siteID";
		$rs = $oDBTB->executeSQL();
		if($rs!= 0){
			if($gTask =="create"){$this->contentID = odbc_result($rs, "contentID");}
			$this->externalContentID = odbc_result($rs, "externalContentID");
			$this->name = odbc_result($rs, "name");
			$this->descriptionBLOB = odbc_result($rs, "description");
			if(odbc_result($rs, "linkBackToContent") == 1){
				$this->linkBackToContent = " checked";
			}else{
				$this->linkBackToContent = "";
			};
			$this->dateCreated = odbc_result($rs, "dateCreated");
			$this->dateModified = odbc_result($rs, "dateModified");
			$this->lastModifiedBy = odbc_result($rs, "lastModifiedBy");
			$this->luContentTypeID = odbc_result($rs, "luContentTypeID");

			$oDBTB->sql = "sp_Public_ContentOnly_Select $this->contentID";
			$rs2 = $oDBTB->executeSQL();
			$this->contentBLOB = odbc_result($rs2, "content");
			return 0;
		}else{
			return 1;
		}
	}
?>

Code: Select all

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Admin_ContentRecord_Insert]
	@name VARCHAR(150),
	@description VARCHAR(2000),
	@linkBackToContent INT,
	@content TEXT,
	@luContentTypeID INT,
	@userID INT,
	@siteID  INT
AS
	DECLARE @error INT
	INSERT INTO libContent
		(name, description, content, linkBackToContent, modifiedBy, siteID, luContentTypeID)
	VALUES
		(@name, @description, @content, @linkBackToContent, @userID, @siteID, @luContentTypeID)
	SET @error = @@ERROR
	IF @error <> 0 GOTO failed
	EXEC spAdmin_ContentRecord_Select  @@IDENTITY
	failed:
	RETURN @error

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spAdmin_ContentRecord_Update]
	@contentID INT,
	@name VARCHAR(150),
	@description VARCHAR(2000),
	@linkBackToContent INT,
	@content TEXT,
	@luContentTypeID INT,
	@userID INT,
	@siteID  INT -- this is here because it is called from the same function as the INSERT SP, and to simplify that, they both use the same parameters.
AS
	DECLARE @error INT
	UPDATE libContent SET
		name = @name,
		description = @description,
		content = @content, 
		linkBackToContent = @linkBackToContent,
		dateModified = getDate(),
		modifiedBy = @userID,
		luContentTypeID = @luContentTypeID
	WHERE id = @contentID
	
	SET @error = @@ERROR
	IF @error <> 0 GOTO failed
	
	EXEC spAdmin_ContentRecord_Select  @contentID
	failed:
	RETURN @error

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spPublic_ContentOnly_Select]
	@id INT
AS
	SELECT content FROM libContent WHERE id = @id

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

When you run that exact same select proc in Enterprise Manager, what do you get (in terms of count)? Also, this little bit:

Code: Select all

$oDBTB->sql = "sp_Public_ContentOnly_Select $this->contentID";
$rs2 = $oDBTB->executeSQL();
$this->contentBLOB = odbc_result($rs2, "content");
Might now be necessary since the update and insert procs appear to be returning that row anyway. Or am I not reading the code correctly?
phpMSQL
Forum Newbie
Posts: 7
Joined: Mon Jun 04, 2007 9:55 am

Post by phpMSQL »

Sorry about not using the tags correctly. I will read that.


The proc "sp_Admin_ContentRecord_Insert" and "sp_Admin_ContentRecord_Update"

and then those procs call "spAdmin_ContentRecord_Select @contentID" which returns one record, with 10 columns, but not the content blob.

Then the PHP calls "sp_Public_ContentOnly_Select $this->contentID" to get the blob.

It seems that it is cutting things off at about 17200 chars (6 pages), which isn't much. I was thinking that if it where over a size amout I should try to chunk it or somthing???
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Now I see what it is doing. Is there any chance that there is a limit on what is returned from the DB server? Can you run this proc in your client admin utility (like Enterprise Manager) to make sure that the DB server is returning what it is supposed to when that proc is called?
phpMSQL
Forum Newbie
Posts: 7
Joined: Mon Jun 04, 2007 9:55 am

Post by phpMSQL »

WHile working with you I have been trying things and I stumble across the issue. I was submitting the content with the rest of the data??? rather than separately. now it submits and returns the content... when it doesn't timeout!

I had thought the ODBC has a size limit.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Timeout on the database side or code side?
phpMSQL
Forum Newbie
Posts: 7
Joined: Mon Jun 04, 2007 9:55 am

Post by phpMSQL »

I think the code. THe page just freezes after a while, but I a testing it with probably 100-200 pages of content.

I will scale it back to something I know is practicle for the application.
Post Reply