SOLUTION for MS-SQL and ACCESS truncating long posts at 4kb

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
klumsy
Registered User
Posts: 5
Joined: Sat Jun 01, 2002 11:52 pm
Contact:

SOLUTION for MS-SQL and ACCESS truncating long posts at 4kb

Post by klumsy » Thu Sep 26, 2002 5:32 am

the problem...
when you post using ms-sql server or access, it seems to cut the message off at about 4000 characters (or 2000 characters if using chinese etc)
however when investigating i found that the database did contain the large post.. it was just getting truncated on the reselect... bug tracker says its not fixable.. but below is my answer to that. whether this gets offically in, or whether its made availible as a mod or hack , is up to whoever.. i'm just happy i can get it working for me now, and want to share it with others who were struggling with the same issue


i believe it is fixable (both for access and for ms-sql server), you see the posting actually puts in the bigger post.. its just the selecting is limiting to about 4k... this is a limit of the ODBC library.. rahther than the database (either sql server 7, 2000 or access ).. there are a few ways to get around this.. one is on the whole server side in php.ini but for me i don't own my web host, but get hosting from a hosting company, so i made the following changes they work very very well..

i change both
db/msaccess.php
db/mssql-odbc.php

in both of these files, i added the following lines insidethe function "sql_query "
after the line "$this->result = odbc_exec($this->db_connect_id, "SELECT $query");"

i added the following
------------------------------------------------
odbc_binmode($this->result, ODBC_BINMODE_PASSTHRU);
odbc_longreadlen($this->result, 16384);
------------------------------------------------
very simple , i tested it against both mssql7 and an access database , it works good (the access database even seems to run faster) there are techniques to do this for native sql server (without going through ODBC) but i can't get that access with my sql server to bother researching and testing it..
in this case i set the new limit to 16k, you can make it much much much bigger than that.(many megs). i think i myself would limit a post to about 65k myself.

Peter The Great
Registered User
Posts: 6
Joined: Tue Oct 22, 2002 8:31 am
Location: UK, Middlesbrough
Contact:

Post by Peter The Great » Tue Oct 22, 2002 8:47 am

Typical!

Just when I've converted the database (Access) for a MySQL upgrade mostly due to this problem someone fixes it!!

(Well done by the way, I was looking at that and gave up after a while)
Up The Irons!

Wizard's Tower
Registered User
Posts: 7
Joined: Mon Oct 28, 2002 2:03 am
Contact:

Re: Posting

Post by Wizard's Tower » Mon Oct 28, 2002 2:42 am

Well done and bless you! I knew right away this had to do with the retrieval and most likely the default reading of only one page of data, but didn't know enough about PHP to be able to make the appropriate settings.

I only wish I had found this right away and hadn't spent the last two weeks tinkering with this! (mental note: Use broader searches) :)

SER
Registered User
Posts: 1308
Joined: Fri Sep 06, 2002 6:09 am
Location: N 34 3 8 / W 118 14 33

Post by SER » Mon Oct 28, 2002 3:58 am

Cool, I'll give this a shot and report back.
It was truncating my main rules and regs, so I had to leave some stuff out, now I might be able to put everything I wanted to say in there. 8)
Visual Guide to Avatar Galleries
The Unoficial Staff Forum For The Unofficial Support Team | Join me at HauntX Remember, if it's Haunted, it's here!
"Support helps those who help themselves." | "You can lead a fool to wisdom, but you cannot make him think."

SER
Registered User
Posts: 1308
Joined: Fri Sep 06, 2002 6:09 am
Location: N 34 3 8 / W 118 14 33

Post by SER » Mon Oct 28, 2002 4:23 am

Beautiful.
I more than doubled my orignal rules that I had edited down by a couple of lines so that it would all show up, and it's all there.
You are wonderful human being.
I'm getting no errors and it works great.
Thanks a lot!!!!!!
I bow down before the master.
ImageI'm not worthy!Image
Visual Guide to Avatar Galleries
The Unoficial Staff Forum For The Unofficial Support Team | Join me at HauntX Remember, if it's Haunted, it's here!
"Support helps those who help themselves." | "You can lead a fool to wisdom, but you cannot make him think."

The Tunester
Registered User
Posts: 21
Joined: Sun Oct 27, 2002 1:39 pm

Thats great, but.....

Post by The Tunester » Thu Oct 31, 2002 8:43 pm

I'm happy for you all, but I'm using a native sql7 server. You've mentioned that you couldn't research a fix for it....does anyone else know?
- Tunester

Image

Wizard's Tower
Registered User
Posts: 7
Joined: Mon Oct 28, 2002 2:03 am
Contact:

Post by Wizard's Tower » Fri Nov 01, 2002 12:18 am

Tunester, from doing a little digging, you can set the text size for each instance of the connection if you're using native SQL Server (not through ODBC, though it might work there, too, I might have to try it).

Add the following line in file 'mssql.php', function 'sql_db':

mssql_query("set textsize 76800");

This should be placed just before the 'return $this->db_connect_id;' statement. This basically sets the long text return size for the life of the connection, in this case the life of the script page.

As the server I'm working with doesn't have PHP compiled with mssql support, I can't test this, so let me know how it works or if it works, will you? If this works, I'm going to push them to recompile PHP for me with MSSQL support, because I *HATE* ODBC. Just like an onion, you have to get rid of the top layers to get to the good parts. :)

[EDIT: I should have said that you can change the 76800 to any number you want as a max, I just picked 75K. ]

The Tunester
Registered User
Posts: 21
Joined: Sun Oct 27, 2002 1:39 pm

Thanks!

Post by The Tunester » Fri Nov 01, 2002 1:34 am

Thanks Wizard. I'm about at the end of my rope. Of course, I placed the script where I thought it should go - didn't work. So I'm pasting the original function and asking nicely for you to place the script where it should go, because I'm no scripting superstar. Here it is..

function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)
{
$this->persistency = $persistency;
$this->user = $sqluser;
$this->password = $sqlpassword;
$this->server = $sqlserver;
$this->dbname = $database;

$this->db_connect_id = ( $this->persistency ) ? mssql_pconnect($this->server, $this->user, $this->password) : mssql_connect($this->server, $this->user, $this->password);

if( $this->db_connect_id && $this->dbname != "" )
{
if( !mssql_select_db($this->dbname, $this->db_connect_id) )
{
mssql_close($this->db_connect_id);
return false;
}
}

return $this->db_connect_id;

Thanks!
- Tunester

Image

SER
Registered User
Posts: 1308
Joined: Fri Sep 06, 2002 6:09 am
Location: N 34 3 8 / W 118 14 33

Re: Thanks!

Post by SER » Fri Nov 01, 2002 6:42 am

The Tunester wrote: function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)
{
$this->persistency = $persistency;
$this->user = $sqluser;
$this->password = $sqlpassword;
$this->server = $sqlserver;
$this->dbname = $database;

$this->db_connect_id = ( $this->persistency ) ? mssql_pconnect($this->server, $this->user, $this->password) : mssql_connect($this->server, $this->user, $this->password);

if( $this->db_connect_id && $this->dbname != "" )
{
if( !mssql_select_db($this->dbname, $this->db_connect_id) )
{
mssql_close($this->db_connect_id);
return false;
}
}

mssql_query("set textsize 76800");

return $this->db_connect_id;
Visual Guide to Avatar Galleries
The Unoficial Staff Forum For The Unofficial Support Team | Join me at HauntX Remember, if it's Haunted, it's here!
"Support helps those who help themselves." | "You can lead a fool to wisdom, but you cannot make him think."

Wizard's Tower
Registered User
Posts: 7
Joined: Mon Oct 28, 2002 2:03 am
Contact:

Post by Wizard's Tower » Fri Nov 01, 2002 7:20 am

What Ser said. :)

I'm just dieing to know if it works.

The Tunester
Registered User
Posts: 21
Joined: Sun Oct 27, 2002 1:39 pm

Sorry

Post by The Tunester » Fri Nov 01, 2002 1:56 pm

Thanks for th input, but no go. Same situation.
- Tunester

Image

Wizard's Tower
Registered User
Posts: 7
Joined: Mon Oct 28, 2002 2:03 am
Contact:

Post by Wizard's Tower » Fri Nov 01, 2002 6:21 pm

ok, that's a bummer, but I'm not out of ideas. :) I think I'll give some background information, though, from the results of my digging into this, so maybe it'll give someone else an idea. What's basically happening is that SQL Server and Access store long text as pages of data ( remember that 4096?). Also, these DB's are set by default to only return the first page of data unless told otherwise. When you're working strictly with Microsoft products, all the relevant interfaces compensate for this by overriding this default setting and setting it to 2Gb, which is the maximum storage in these long text datatypes.

Now, after reading the SQL docs on the "SET TEXTSIZE" T-SQL statement, it says that this is set at 'execution' for a 'session'. Now, this may not mean the life of a connection, but perhaps per transaction or per individual execution of a SQL statement. This being the case, I have two more ideas for you. :)

First idea, move the statement you added above into the beginning of the 'sql_query' function like so:

Code: Select all

function sql_query($query = "", $transaction = FALSE)
{
//
// Remove any pre-existing queries
//
unset($this->result);
unset($this->row);

if ( $query != "" )
{
$this->num_queries++;

if ( $transaction == BEGIN_TRANSACTION && !$this->in_transaction )
{
if ( !mssql_query("BEGIN TRANSACTION", $this->db_connect_id) )
{
return false;
}

mssql_query("set textsize 76800");  <--- INSERTED STATEMENT

$this->in_transaction = TRUE;
}
//
// Does query contain any LIMIT code? If so pull out relevant start and num_results
This will perform this statement after the beginning of each transaction.

Second idea, change the SELECT statement processing itself, so each execution of a SELECT statement is affected, in ~line 140, like so:

Code: Select all

$this->result = mssql_query("set textsize 76800 SELECT $query", $this->db_connect_id); 
If neither of these work, then I really am out of ideas unless I can play with a native SQL environment. Since I'm running PHP under Windows, though, I would need to find a Windows build that includes MSSQL support, and so far I haven't. Anyone know where I can find one? (hopefully complete with install; I don't ask for MUCH) :)

Just out of curiosity, Tunester, have you gone through the php.ini settings recommended elsewhere in these forums? If neither of the above work, then those could be your culprit.

The Tunester
Registered User
Posts: 21
Joined: Sun Oct 27, 2002 1:39 pm

Thanks again

Post by The Tunester » Fri Nov 01, 2002 6:51 pm

Thanks for all the work, Wizard. I feel bad, but still no go. What should I be doing in the php.ini file?
- Tunester

Image

The Tunester
Registered User
Posts: 21
Joined: Sun Oct 27, 2002 1:39 pm

Call Off the dogs

Post by The Tunester » Fri Nov 01, 2002 7:16 pm

Eternal thank yous to Wizard, who piqued my curiousity about the php.ini file. Found it on the server, and lo and behold, it was set mssql_textsize default to 4096. Changed it up - worked like a charm. So.....thanks to all who assisted - you really helped.

Just as an aside, will there be any 'fallout' on my board due to this change?
- Tunester

Image

Wizard's Tower
Registered User
Posts: 7
Joined: Mon Oct 28, 2002 2:03 am
Contact:

Post by Wizard's Tower » Fri Nov 01, 2002 9:04 pm

From what happened to me when I fixed it in ODBC, you'll see that some people made long posts, saw that they didn't fit, then decided to add more, shorter posts to fit all their content. NOW, all of what they originally posted will be visible (if the limit is high enough), so you might have some older, screwy-looking posts (love those technical terms). :)

Locked

Return to “2.0.x Support Forum”