+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: can temporary tables be used in PHP? (/showthread.php?tid=83408)
Pages:
1
2
RE: can temporary tables be used in PHP? - JustJohnQ - 09-28-2022
https://stackoverflow.com/questions/4111594/why-always-close-database-connection
Some more testing:
PHP Code:
$sql = "CREATE TEMPORARY TABLE BC_PR_list_temp(taxyear VARCHAR(10))";
$this->db->query($sql);
$sql = "INSERT INTO BC_PR_list_temp (taxyear) VALUES (1984)";
$this->db->query($sql);
$sql = "SELECT * FROM BC_PR_list_temp";
$result = $this->db->query ($sql);
$data = $result->result ();
print_r($data);
This works fine within a function, so the temp table is created. As mentioned earlier, it will be deleted once the db session is closed.
PHP Code:
$this->db->table_exists('BC_PR_list_temp')
table_exists() doesn't work for temporary tables, the result is always false. As a matter of fact, it is not easy to check if a temporary table exists using SQL.
Conclusion: it is not a CI problem, but a MySQL limitation when working with temporary tables. I suggest you find a way to work with normal tables.
RE: can temporary tables be used in PHP? - richb201 - 09-29-2022
table_exists() doesn't work for temporary tables, the result is always false. <<<<
THANK YOU! I use Koolrepport to generate a report. This is the main output of my app. The report is pretty big and consists of all kinds of data sliced and diced. That is the reason I am building the temp files. I have no need to keep them. I also figured that the speed of operations would be increased by using tables in RAM, like the old RAM drives we used to use way back,,,. If I knew table_exists didn't work for temp tables that would have saved me lots of angst!
What I do is build the temp tables for 10 years at the same time. Then a user can chose which year they want a report for. They can then print it and then chose another year without my code recalculating. It worked very smoothly until I started load testing with Jmeter. With jmeter I needed to delay starting reports for 5 or 6 seconds each. I finally realized that my creation of the temp table wasn't working due to some SQL errors. CI3 didn't report those errors. I tested it in mysqlWorkBench and fixed it (the sql error). BUT table_exists still didn't show the table existing!
So, what makes a different session? a different function? How can I check this?
Your code above is in a single function. That should work. I actually have mine split into a two functions in a model. And a user can run a subreport, directly from the first report. Is that a new session? I have no idea. What can I look at to determine if I am still in the original session?
RE: can temporary tables be used in PHP? - JustJohnQ - 09-29-2022
I would forget about using temporary tables in your database and use regular tables instead. It shouldn’t be too difficult to drop the created table at a certain point.
As mentioned earlier, it looks like temporary tables are mostly used in stored procedures or functions within mysql.
RE: can temporary tables be used in PHP? - richb201 - 09-29-2022
Well, it was painful, but i converted from my attempt at using temp tables to using regular tables. Since this is a multiuser system I need to append each "temporary" table with the name of the user. I finally got it done and the system appear to be working. I will try to load test it tomorrow. One strange thing is that I am not seeing the tables that I created on mysqlWorkbench. Yet the code works. Is this possible?
I am starting to investigate switching over to SQLserver (on RDS). Does CI3 support SQLServer? The main reason is the ability to write/debug stored procedures on sql server. I am checking out the pricing.
RE: can temporary tables be used in PHP? - JustJohnQ - 09-30-2022
Yes, CI3 works with MS SQL Server, I am actually using it in one of my projects. You can call stored procedures in SQL Server by using an SQL query.
RE: can temporary tables be used in PHP? - richb201 - 10-01-2022
(09-30-2022, 01:16 AM)>>JustJohnQ Wrote: Yes, CI3 works with MS SQL Server, I am actually using it in one of my projects. You can call stored procedures in SQL Server by using an SQL query.
Thanks. The rds pricing on it seems cheaper than mysql.
I switched all my temp tables to regular tables. I had to name each with the user's id plus a timestamp. This is for my load testing. When the user wants to exit the app, I drop all the tables that start with that user's id+timestamp. BUT, I am creating the 'prefix' in the __construct() of my controller and saving the prefix in a $_SESSION variable.
My plan is to use the prefix+table name to DROP the garbage tables when the user logs out. But is seems that "__construct()" is running numerous times, overwriting my 'prefix'.
What do I do about this? If I write the prefix into a cookie, the next time __construct() runs, it will overwrite the cookie.
BTW, are you using temp tables on SQL Server? In you opinion, will CI3 sql code that works on mysql also work on sqlserver, without changes? I have been playing with the idea of moving the report generation code to a stored procedure for a FEW YEARS. And SSMS seems pretty tooled for this.
RE: can temporary tables be used in PHP? - richb201 - 10-01-2022
I fixed the overwriting of the prefix by sticking the prefix in a session variable and not overwriting unless it doesn't exist.
I'd like to be able to add the word TEMPORARY into a #PRAGMA. Does CI3 have such a structure?
(10-01-2022, 04:02 PM)richb201 Wrote: I fixed the overwriting of the prefix by sticking the prefix in a session variable and not overwriting unless it doesn't exist.
I'd like to be able to add the word TEMPORARY into a #PRAGMA. Does CI3 have such a thing?
RE: can temporary tables be used in PHP? - InsiteFX - 10-02-2022
To keep the database alive use:
PHP Code:
$this->db->reconnect();
RE: can temporary tables be used in PHP? - richb201 - 10-02-2022
Thx Insite. I managed to get the temporary tables working. Here are some important points for others.
1. There is no way to see what temp tables exist.
2. Errors in sql on creation of temp tables don't display.
3. The lack of tools makes this trial and error.
As of now it is working fine. I am having trouble with S3 under load. About 10% of the time the bucket is not found and I get a 500 server error. Not sure how to fix this.
(10-02-2022, 04:26 AM)richb201 Wrote: Thx Insite. I managed to get the temporary tables working. Here are some important points for others.
1. There is no way to see what temp tables exist.
2. Errors in sql on creation of temp tables don't display.
3. The lack of tools makes this trial and error.
As of now it is working fine. I am having trouble with S3 under load. About 10% of the time the bucket is not found and I get a 500 server error. Not sure how to fix this.
I use koolreport and was not able to get kr to directly use images on s3. So when a user logs in I download their images to the server. Not the greatest solution.
RE: can temporary tables be used in PHP? - richb201 - 10-02-2022
(10-02-2022, 04:26 AM)richb201 Wrote: Thx Insite. I managed to get the temporary tables working. Here are some important points for others.
1. There is no way to see what temp tables exist.
2. Errors in sql on creation of temp tables don't display.
3. The lack of tools makes this trial and error.
As of now it is working fine. I am having trouble with S3 under load. About 10% of the time the bucket is not found and I get a 500 server error. Not sure how to fix this.
(10-02-2022, 04:26 AM)richb201 Wrote: Thx Insite. I managed to get the temporary tables working. Here are some important points for others.
1. There is no way to see what temp tables exist.
2. Errors in sql on creation of temp tables don't display.
3. The lack of tools makes this trial and error.
As of now it is working fine. I am having trouble with S3 under load. About 10% of the time the bucket is not found and I get a 500 server error. Not sure how to fix this.
I use koolreport and was not able to get kr to directly use images on s3. So when a user logs in I download their images to the server. Not the greatest solution.
My ci3 app can't find the bucket to bring down the images about 10% of the time and I get a 500 error. I'd like to catch that error and pop open a "try again in 5 minutes" message but I am not sure how to catch an error using the aws api.