can temporary tables be used in PHP? |
https://stackoverflow.com/questions/4111...connection
Some more testing: PHP Code: $sql = "CREATE TEMPORARY TABLE BC_PR_list_temp(taxyear VARCHAR(10))"; 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.
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?
proof that an old dog can learn new tricks
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.
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.
proof that an old dog can learn new tricks
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.
(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.
proof that an old dog can learn new tricks
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.
proof that an old dog can learn new tricks
To keep the database alive use:
PHP Code: $this->db->reconnect(); What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
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.
proof that an old dog can learn new tricks
(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.
proof that an old dog can learn new tricks
|
Welcome Guest, Not a member yet? Register Sign In |