I’ve been using cubeSQL server for many years, going as far back as when it was RealSQL Server. However, I’ve got an ongoing issue with the latest release (5.9.6) that might force me to look elsewhere for a solution. Marco is doing his best to help me out, however, I have to get a solid solution back in place pretty soon, so I must press on with finding an alternative.
I’ve seen PostgreSQL mentioned on here many times and people do generally seem to like it - I worry that’s its free software, whereas cubeSQL is a paid for license.
For info, it’s the BLOB column in a cubeSQL database table that’s causing me the trouble.
Anyone able to talk to me about their experiences using PostgreSQL in an environment with about 5 large-ish databases, with around 50 to 80 concurrent connections?
Also keen to here if anyone has gone through the process of moving from cubeSQL to PostgreSQL and how much is involved?
I have! I converted Xojo’s CubeSQL database to PostgreSQL. You will have zero issues with performance. It’s a fantastic database that will absolutely handle what you’re looking for.
The trouble you’ll run into is with your SQL. PostegreSQL is very strict. This strictness is a good thing, but it’ll force you to break bad habits.
One example is the following query: SELECT primary_key, MAX(some_column) FROM table; that CubeSQL/SQLite would have no trouble with. They would return the primary key of the row with the greatest value. PostgreSQL will not let you do this because it’s ambiguous. What happens if you have two rows with the same maximum value, for example?
The other issue with PG that you’re likely to run into is data types. PG is strict about this too. In SQLite, you want to store 12 characters in a 10 character field, you can do that. SQLite doesn’t care. PG will throw an error. So you need to define your columns correctly and make sure your code is storing intended values.
This transition will take you time and lots of testing. I recommend you do it, but not right now. You’re probably better of waiting for Marco to issue a fix so you can do the transition on your terms.
I too converted from cube sql to postgres. Based on Norman speaking so highly of pg. I wrote out the cube tables as text. Then created new tables in xojo. Next I read the text files back in to the newly created pg tables. I had no problems at all.
But as Thom said pg is strongly typed so queries that worked in sqlite may take some minor tweaking to get them to work in pg. For example: working like ‘%yes%’ has to be written as: lower(working) like ‘%yes%’
The other issue was how pg handles NULL compared to sqlite. I have to use “coalesce” when concating two fields together, because one of the fields might be NULL.
It took very little time, less than 4 hours, to write out the sqlite tables and read them into pg. Db size is about 345 megabytes.
There is a Pg Admin? program that is free but I found it much better to buy the dBeaver data base manager. Makes life so much simpler.
Feel free to message me any postgreqsql questions. I have been using it on linux servers for over a decade. I usually install PostgreSQL on linux on digitalocean.com . If you want digital ocean to manage more of PostgreSQL for you you can pick create PostgreSQL server instead of linux and it will do all the setup for you.
I suggest getting tableplus from https://tableplus.com to connect to PostgreSQL (or MySQL or any SQL really). See a video on what table plus can do https://www.youtube.com/watch?v=7V_CJBPZPes
The free version is fully functional but limits the number of open windows.
Thanks for mentioning Postico. I’m going to check it out. dBeaver can work with different databases at the same time. So I can test something quick and dirty in sqlite and then move it to Postgres.
Not cheap though. $299 for the enterprise version.
I have been using RazorSQL for many years and it will connect to a wide variety of DBs. Like dBeaver it uses the java database drivers. It is $129 (US) for a single user license.
[edit]
If you like to have diagrams of your database then dbschema is excellent. It also had query functions built into it and uses java database drivers as well for connectivity. It will even generate sample data! It is a tad pricier though: $196 (US) for personal license and $294 (US) for a business license.
CubeSQL has no problems with BLOBs, but @David_Yeaman is doing something in his code that triggers a severe error condition.
I am more than willing to help him or to provide a fix if we find that the issue is in CubeSQL, but the main problem here is that the user cannot share the code, the database, or a way to reproduce the condition, so I literally have no way to assist him.
Not completely accurate there Macro - I have previously explained to you that once it ‘breaks’, pulling the record using SQLite Manager, with the exact same SELECT SQL, also fails with the same error message. Restarting the server is the only way to clear the error. This would indicate (to me) that’s it’s not the Xojo code that’s causing the issue. For the time being we have installed an older version of the server component to get by. Regards, Dave.
Thanks for the clarification, David.
Again, if I have a way to reproduce the issue, I can get it fixed quickly.
Please note that Xojo and SQLiteManager share the same exact CubeSQL plugin, so the issue should be there.
No problem, for ref, I don’t think the code I’m using in Xojo is anything but less than exceptional - if anything, probably as simple as it can possibly get:
Var someRS As RowSet
someRS = App.nDatabase.SelectSQL(“SELECT fileData FROM filesData WHERE recID = 123456”)
Are you using that code inside a preemptive threat or inside a Timer or in a ListBox event?
Can you give me some context?
Please provide me as much information as you can.
