Page 1 of 1

Not receiving all data through ODBC Connection

Posted: Mon Jun 04, 2007 10:21 am
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.

Posted: Mon Jun 04, 2007 10:32 am
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?

Posted: Mon Jun 04, 2007 10:59 am
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]

Posted: Mon Jun 04, 2007 11:22 am
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?

Posted: Mon Jun 04, 2007 12:04 pm
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???

Posted: Mon Jun 04, 2007 12:27 pm
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?

Posted: Mon Jun 04, 2007 1:46 pm
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.

Posted: Mon Jun 04, 2007 2:02 pm
by RobertGonzalez
Timeout on the database side or code side?

Posted: Mon Jun 04, 2007 2:23 pm
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.