Welcome Guest, Not a member yet? Register   Sign In
can temporary tables be used in PHP?
#11

(This post was last modified: 09-29-2022, 12:18 AM by JustJohnQ.)

https://stackoverflow.com/questions/4111...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.
Reply
#12

(This post was last modified: 09-29-2022, 06:29 AM by richb201.)

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
Reply
#13

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.
Reply
#14

(This post was last modified: 09-29-2022, 06:18 PM by richb201.)

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
Reply
#15

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.
Reply
#16

(This post was last modified: 10-01-2022, 10:22 AM by richb201.)

(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. 
proof that an old dog can learn new tricks
Reply
#17

(This post was last modified: 10-01-2022, 04:10 PM by richb201.)

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?
proof that an old dog can learn new tricks
Reply
#18

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 )
Reply
#19

(This post was last modified: 10-02-2022, 04:29 AM by richb201.)

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.
proof that an old dog can learn new tricks
Reply
#20

(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.
proof that an old dog can learn new tricks
Reply




Theme © iAndrew 2016 - Forum software by © MyBB