LiveCode Forums

Below is an example a revDataFromQuery command in my system. All of them have stopped working. It pulls a blank, but when I use mysqlWorkbench it works fine and pulls appropriate response. Other database functions are working. I’ve noticed that my insert queries are working to some degree. I have a problem there, but I don’t think it’s related.

Address to db has not changed. There is only one db. Using AWS. Any thoughts? Anyone else having a similar problem? Using Livecode 7.0. Everything was working about two weeks ago.

databaseConnect
put "SELECT Max(DateP) FROM Picks_Analysis;" into LoadSQL
put revDataFromQuery("|",return,gDatabaseID,LoadSQL) into MaxDate
revCloseDatabase gDatabaseID

Is it related to the time to response?
Is it a very huge table?

The table is quite small actually. There are quite a few very large tables in the DB, which I’ve had trouble with before on the time, but this one only takes a few milliseconds to run in MySQL Workbench and only has a couple hundred rows.

Initially, I thought it was an address problem, but other kinds of query functions seem to be working fine, at least from initial observations.

Do you really need to close the connection?
I usually close the connection only when I close the app

So, I ran the below queries. The first two, which are exactly the same, return nothing. The last one which is a different query returns accurate information. All of them work in MySQL Workbench (copied the exact text in all of them from livecode to MySQL Workbench and ran the queries to test).
The Picks_Analysis table only has a couple hundred records and it works fine in MySQL Workbench, but not in livecode. The Over_Under_With_Recents table has thousands of records and works in both. It is a much older piece of code (not sure if that has anything to do with it). I even removed the databaseConnect and CloseDatebase between the queries to see if that was the problem. The one at the bottom which was working still worked in that case and the others didn't.
I changed the variables just in case there was something I missed there, still nothing.

I'm stumped. I have absolutely no idea why this would be happening.

databaseConnect
put "SELECT * FROM Picks_Analysis;" into tSQL
put revDataFromQuery("|",return,gDatabaseID,tSQL) into tTtest
revCloseDatabase gDatabaseID
databaseConnect
put "SELECT * FROM Picks_Analysis;" into LoadSQL
put revDataFromQuery("|",return,gDatabaseID,LoadSQL) into tTtest
revCloseDatabase gDatabaseID
databaseConnect
put "SELECT DISTINCT ID FROM Over_Under_With_Recents GROUP BY Ticker_ID;" into LoadSQL
put revDataFromQuery("|",return,gDatabaseID,LoadSQL) into tTtest
revCloseDatabase gDatabaseID

So, after trying dozens of different things I tried changing the name of the table. For some reason removing the word "Analysis" from the name of the table allowed it to function properly. I have no idea, why that would have been, but it's working fine now.

Try this:

So, apparently it isn't fixed...I'm really not sure what is going on. The queries work exactly as they are written below in Workbench, but when I run them Livecode I only get a returned value for the second one. When I run them in Workbench I get both. I've confirmed that the table does exist in the database; I've rounded numbers out assuming that there could be a character limit for some reason that I don't know about; I've run the query a dozen different ways in both systems; I've rebooted; I've rewritten the code; I've double checked the character types; everything I know to do has been done, but I still can't get this query to operate in LiveCode, but the second one works fine.

The information from the second table is very old. I've recently updated the first one's table (used a "LOAD DATA LOCAL INFILE" IN Workbench to load the data). I'm not sure if that has anything to do with this, but the data looks to be in order in Workbench and functioning. I'm completely at a loss....

regarding the multiple connections to the database, a query similar to this one gets run thousands of times through a loop and if I don't close the connection then the odds that the connection will be lost is much higher. This feature seems to be working just fine with all of the other queries, so I don't see how this could be the problem.

databaseConnect
put "SELECT * FROM Table_Eval ;" into tSQL
put revDataFromQuery("|",return,gDatabaseID,tSQL) into tTtest
revCloseDatabase gDatabaseID

databaseConnect
put "SELECT * FROM Data LIMIT 100;" into LoadSQL
put revDataFromQuery("|",return,gDatabaseID,LoadSQL) into tTtest2
revCloseDatabase gDatabaseID

You may have already went through some of these, but just to brain-storm:

Permissions: does the db user you are using have permissions to connect from your remote location?
I know you said you were working in MySQL Workbench, but Workbench can use SSH connections. IF you are using SSH to connect to your remote database when in Workbench, then your DB server sees that as localhost rather than a remote connection. Your Livecode app will (most likely) be connecting from whatever your IP happens to be, so your database user needs permissions to connect from that IP, or from "anywhere".

Are you working on a Mac or Windows or Linux? if Mac or Linux, can you connect and run queries from the command prompt?

I’m using a Mac. The app runs queries against all other tables in that DB just fine within the livecode environment. It’s literally just this one table. And really I should say it’s just this one set of Data in this table. I’ve created multiple tables with the same data and every query returns blank. So, it recognizes that there is a table there with that name, otherwise it would return an error. The test variable literally just returns nothing, as if there is no information in the table to query, but when I run the same query in Workbench it runs perfect. I feel like it has something to do with the Data in the table.

The table is only three columns and about 300 rows.
Column 1: Date - Format: DATETIME -
EXAMPLE 2009-07-07 00:00:00
Column 2: Name - Format: VARCHAR (45) - EXAMPLE: JAY
Column 3: Value - Format: DOUBLE - EXAMPLE: -1.32

The table was created in excel; info was pasted into textedit; rich text turned off; saved as a CSV and imported via the LOAD DATA LOCAL INFILE command in Workbench.

MaxV, I tried adding a space before the semicolon, still no go though.

I also just tried an insert statement with a revExecuteSQL to insert the information from the table that wasn’t working into a new table in order to eliminate it being just the revDataFromQuery function.

When I did that both queries worked. It inserted into the new table and then pulled the information from the new table. Then I ran it again to see if it would still work and it didn’t do anything. I cleared the tables and tried again, still nothing. Could have something to do with cache?

could you :

-export (SQL format) the structure and the data of this weird table

-and post it here, on the forum, so we can have a look ?

You wrote that by changing the name of this table, it was back to normal, no ?

Furthermore : now you speak about the way you dealt with the content of this table (excel, textedit, csv and then import with Workbench).

Could you start over :

-from excell
-copy past into a field in Livecode
-insert the data in the SQL table, from LiveCode (line by line, and with tab separator)

I didn’t really change the name, I created a new table with a different name and migrated the Data to that table. It worked once initially, but when I tried to run it again a couple days later it returned blank in the variable. Then I few days later I tried to repeat the process again and it worked once, but not a second time. Now when I create a new table with a different name it doesn’t work and migrate the data it doesn’t work. This should be necessary, but I thought it was just a quirky work around, but it isn’t working, so guess not.

The information is returned to LC then I run some analysis on it in excel by just pasting the information and text delimiting it by the designated character ( “|” in this case).
-Once I’ve obtained what I need I paste it into a different workbook
-Reformat the date to match MySQL’s format YYYY-MM-DD HH:MM:SS.
-Concatenate with commas as the column designator and return as the row designating character (csv format).
-Then I paste it to an email and email it to myself.
-Open MAC partition (where LC is) paste from email into TextEdit and save to local file
-Run stored query in MySQL Workbench “LOAD DATA INFILE LOCAL” to the table. I usually truncate or delete and recreate the table first then import.
-After that open livecode and run test query.

And I’ll see what I can do on getting the table uploaded here.

Are data just text data or are binary data? (images, sounds, etc.)