StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAfter SP Insert, next page have an empty result until reloaded
text
Body
copied!<p>I have a Stored Procedure (SP from now on) that inserts data to the database (SaveClient, see below). When the SP is done I redirect the PHP page to a different PHP page that lists the entries (FetchObjectList, see below). The list does not return the newly created record until I then reload/refresh the page.</p> <p>The stored procedure has a <code>COMMIT</code> at the end, I close the database connection in the PHP code after the SP is called and there is a check for errors but nothing goes wrong.</p> <p>The page itself returns a 200 statuscode which means it isn't cached so can't be browserrelated either.</p> <p>The current workaround is a <code>sleep(1)</code> in the PHP code but when the code goes live I have no idea if it will suffice. I'd ofcourse rather have MySQL dish out the correct resultset. </p> <p><em>EDIT: I'm using the MySQLi object interface of PHP, might be useful to know. ;)</em></p> <p>My devcomputer got PHP 5.2.17, MySQL 5.0.51a (InnoDB) and Apache 2.2.17 installed and running on Windows 7 x64.</p> <p><strong>UPDATE</strong></p> <p>Added the following line <code>CALL FetchObjectList('client_tbl', NULL, NULL, 1, 'client_tbl.name ASC', NULL, NULL);</code> to the end of SaveClient. The resultset does not have the newly created client in the presented resultset.</p> <p><strong>UPDATE 2</strong></p> <p>I tried using the <code>SQL_NO_CACHE</code> as seen <a href="https://stackoverflow.com/questions/181894/mysql-force-not-to-use-cache-for-testing-speed-of-query">here</a> but to no avail.</p> <p>I will now try the same SQL directly in PHP instead of calling the SPs.</p> <p><strong>UPDATE 3 - 20 september</strong></p> <p>I've tried any reasonable answer/comment I've got so far without any luck. I tried to update my PHP and MySQL version today (since I today learned that the live server will run on PHP 5.3.something and MySQL 5.1.something) but did not get it to work. I need to update the PHP to get a more recent <code>php_mysqli.dll</code>/<code>libmysql.dll</code> since the one I got has only supports up to 5.0.51a and there might be my problem since nothing in the actual DB has worked. I tried the <code>libmysql.dll</code> from the MySQL install to no avail. </p> <p>Note that I also changed the PHP code that I've included since I actually copied the wrong one that was calling the <code>user_tbl</code> and not the <code>client_tbl</code> and also simplified it (removed multiqueries) but still the same result.</p> <p>I don't know what will happen to the bounty, if it reverts back to me I'll add it again.</p> <p><strong>Stored Procedure SaveClient</strong></p> <pre class="lang-sql prettyprint-override"><code>DELIMITER // DROP PROCEDURE IF EXISTS work.SaveClient// CREATE PROCEDURE work.SaveClient( IN ObjectID INT, IN UserID INT, IN ClientName VARCHAR(60), IN VersionFrom DATETIME, IN VersionTo DATETIME) root:BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; /* Default values --------------------------------------------------------------------------------------------------------- */ # Used to block INSERT/UPDATEs SET @DoChanges = TRUE; SET @Fields = '*'; SET @Version = NULL; SET @UserVersion = NULL; SET @DateNow = NOW(); SET @VersionActive = CONCAT( '( ( NOW() BETWEEN ', 'version_from AND ', 'version_to ) OR ( ', 'version_from < NOW() AND ', 'version_to IS NULL ) )' ); IF VersionFrom IS NULL THEN SET VersionFrom = @DateNow; END IF; /* Search for client ------------------------------------------------------------------------------------------------------ */ IF ObjectID IS NOT NULL THEN SET @Client = CONCAT( 'SELECT version INTO @Version FROM client_tbl WHERE object_id = ', ObjectID, ' AND ', @VersionActive ); PREPARE stmt FROM @Client; EXECUTE stmt; DEALLOCATE PREPARE stmt; # Check if there are any changes IF @Version IS NOT NULL THEN SELECT name INTO @Name FROM client_tbl WHERE name = ClientName AND version = @Version; IF @Name = ClientName THEN SET @errorMsg = "Duplicate entry"; SET @errorCode = "S0000002"; SELECT @errorCode, @errorMsg; LEAVE root; END IF; END IF; END IF; /* Search for user --------------------------------------------------------------------------------------------------------- */ # Create this as a function IF UserID IS NOT NULL THEN SET @User = CONCAT( 'SELECT version INTO @UserVersion FROM user_tbl WHERE object_id = ', UserID, ' AND ', @VersionActive ); PREPARE stmt FROM @User; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; IF @UserVersion IS NULL THEN SET @errorMsg = "User is missing"; SET @errorCode = "U0000099"; SELECT @errorCode, @errorMsg; LEAVE root; END IF; /* Add the client --------------------------------------------------------------------------------------------------------- */ # Close the current version IF @Version IS NOT NULL THEN IF @DoChanges = TRUE THEN CALL UpdateVersion( ObjectID, UserID, @Version, @DateNow, 'client_tbl' ); SET @Version = @Version + 1; END IF; ELSE SET @Version = 1; END IF; IF @DoChanges = TRUE THEN IF ObjectID IS NULL THEN INSERT INTO object_tbl ( object_class_id, created, created_by ) VALUES( 2, NOW(), UserID ) ; SET ObjectID = LAST_INSERT_ID(); END IF; INSERT INTO client_tbl ( object_id, version, version_from, version_to, changed, changed_by, name ) VALUES( ObjectID, @Version, VersionFrom, NULL, @DateNow, UserID, ClientName ) ; END IF; COMMIT; END // DELIMITER ; </code></pre> <p><strong>Stored Procedure FetchObjectList</strong></p> <pre class="lang-sql prettyprint-override"><code>DELIMITER // DROP PROCEDURE IF EXISTS work.FetchObjectList// CREATE PROCEDURE work.FetchObjectList( IN ObjectType VARCHAR(60), IN ObjectSubType VARCHAR(60), IN ObjectSubID INT, IN IsActive INT, IN OrderBy VARCHAR(100), IN SetStart INT, IN MaxResults INT) root:BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; # Allow the "JSON" output be a max of 8kb SET GLOBAL group_concat_max_len = 8096; /* Default values --------------------------------------------------------------------------------------------------------- */ SET @Fields = '*'; SET @VersionWhere = '1'; # Get everything SET @Special = ''; SET @OrderBy = ''; SET @SetStart = ''; SET @MaxResults = ''; SET @JoinIn = ''; IF IsActive = 1 THEN SET @VersionWhere = CONCAT( '( NOW() BETWEEN ', ObjectType, '.version_from AND ', ObjectType, '.version_to OR ( ', ObjectType, '.version_from < NOW() AND ', ObjectType, '.version_to IS NULL ) )' ); END IF; IF OrderBy != '' THEN SET @OrderBy = CONCAT( 'ORDER BY ', OrderBy ); END IF; /* Specials for each type ------------------------------------------------------------------------------------------------- */ /* - Clients ------------ */ IF ObjectType = 'client_tbl' THEN SET @Fields = ' *, client_tbl.object_id AS object_id, ( SELECT COUNT(*) AS Total FROM client_user_privilege_tbl cup WHERE cup.client_id = client_tbl.object_id ) AS usercount '; END IF; /* - Configuration ------------ */ IF ObjectType = 'configuration_tbl' THEN SET @Fields = ' * '; END IF; /* Add upp the query to run ----------------------------------------------------------------------------------------------- */ SET @Query = CONCAT( 'SELECT ', @Fields, ' FROM ', ObjectType, ' ', @JoinIn, ' WHERE ', @VersionWhere, ' ', @Special, @OrderBy ); PREPARE stmt FROM @Query; EXECUTE stmt; DEALLOCATE PREPARE stmt; COMMIT; END // DELIMITER ; </code></pre> <p><strong>PHP CODE SNIPPET (Updated 20 september)</strong></p> <pre class="lang-php prettyprint-override"><code>$query = "CALL FetchObjectList('client_tbl', NULL, NULL, 1, NULL, NULL, NULL)"; addTrace($query); $rs = $db->query($query); if( $rs ) { addTrace('Query done -> Results: ' . $rs->num_rows); while($r = $rs->fetch_assoc()){ $fetchArray[] = $r; } $count = $rs->num_rows; $rs->close(); $db->next_result(); } else { addTrace('Query failed -> ' . $db->error); flushTrace(); exit; } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload