SQL query command crash the script

[Grovkillen]

The code below explains my problem. The lower command in the query will crash the script. The same query works just fine when using a SQL tool.

Code: Select all

Let>SQL_query=SELECT * FROM cad_file_data WHERE folder_='k:\se\0d140';
Let>DB_connection=Provider=MSDASQL.1;Persist Security Info=False;Data Source=seeeds_server
DBConnect>DB_connection,dbH
DBQuery>dbH,SQL_query,DB_r,DB_n,DB_f,1
DBClose>dbH

//the () or lower() will crash the script
Let>SQL_query=SELECT * FROM cad_file_data WHERE lower(folder_)=lower('k:\se\0d140');
Let>DB_connection=Provider=MSDASQL.1;Persist Security Info=False;Data Source=seeeds_server
DBConnect>DB_connection,dbH
DBQuery>dbH,SQL_query,DB_r,DB_n,DB_f,1
DBClose>dbH

[Grovkillen]

I'm using a PostgreSQL server by the way.

Tried this (which also work using a query tool but will crash the script:

Code: Select all

SELECT * FROM cad_file_data WHERE folder_  ~* '\\se\\0d140(|\\)';

[Grovkillen]

If I do a simple count to get all the rows in that table everything is working just fine...

Code: Select all

Let>SQL_query=SELECT COUNT(*) FROM cad_file_data;
Let>DB_connection=Provider=MSDASQL.1;Persist Security Info=False;Data Source=seeeds_server
DBConnect>DB_connection,dbH
DBQuery>dbH,SQL_query,DB_r,DB_n,DB_f,1
DBClose>dbH

So my guess is now NOT the () but how MS is interpreting the results from PostgreSQL. Please advice me what to do.

[Grovkillen]

Code: Select all

SELECT * FROM cad_file_data WHERE folder_='K:\SE\0D140' LIMIT 1;

Code: Select all

SELECT * FROM cad_file_data WHERE folder_ LIKE '%\0D140';

Code: Select all

SELECT * FROM cad_file_data WHERE folder_ ILIKE '%\0d140';

These all fail. I don't know how to investigate this further since I don't have any error messages to read up on. The editor just freezes.

[Grovkillen]

Ok, so this work (when I only return the folder column), I will look through all the columns now and see if one (or multiple) is make the script crash. If so maybe it's the contents off that particular column.

Code: Select all

Let>SQL_query=SELECT full_path_ FROM cad_file_data WHERE LOWER(folder_)=LOWER('k:\se\0d140');
Let>DB_connection=Provider=MSDASQL.1;Persist Security Info=False;Data Source=seeeds_server
DBConnect>DB_connection,dbH
DBQuery>dbH,SQL_query,DB_r,DB_n,DB_f,1
DBClose>dbH

Edit, this folder worked so yes it's one of the columns that is messing up the script handling.

Code: Select all

Let>SQL_query=SELECT * FROM cad_file_data WHERE LOWER(folder_)=LOWER('k:\se\0d142');
Let>DB_connection=Provider=MSDASQL.1;Persist Security Info=False;Data Source=seeeds_server
DBConnect>DB_connection,dbH
DBQuery>dbH,SQL_query,DB_r,DB_n,DB_f,1
DBClose>dbH

[Grovkillen]

I may have found the problem. One of the columns was added later and have NULL values and empty values together with "normal" values. This seems to crash the script. In the query of the folder, which doesn't crash the script, this column is made up of only empty values.

[Grovkillen]

For your information I will update the database but for future releases you may want to look into why this happens. To test this yourself:

1. Install PostgreSQL
2. Create a simple table
3. Add data to the table
4. Add a new column
5. Add new data to the table which also adds data to this new column

If you have rows with data in all columns except the new column together with rows with data in all columns you should have the same bug happening to you when you SELECT * all data from that table.

[Marcus Tettmar]

Macro Scheduler does no processing. Just sends the SQL to the connection driver and returns the results. So I don't think there's much we can do here. Sounds like a bug with the ODBC/ADO driver. We can look to see if there's anything we can do to prevent it causing a crash though.

[Grovkillen]

Yes it would be great if the crash was prevented.