How to solve "There is a syntactical error in the SQL statement."

Please double-check the queries, sometimes white space or hidden spaces cause these issues.

Hi @Alejandra Bonilla​ ,

Few tips for working with SQL Statements, build the Queries using SSMS and try to copy the Query from SSM to A360 and try running the bot. Keep a message box and check the values before DB commands to make sure values are available in the respective variables before hitting the DB commands.

What is the solution for this? What is the correct insert query statement

Hello,

i ran the update query in compiler, but still not working in AA :(

UPDATE [Dx_Orders$$] SET "Material issue Date" = '$vPostingDate$' WHERE "Sales Document" = '$vSalesDocMaster$'

Getting error as -

 There is a syntactical error in the SQL statement.

- SQL statement got timed out.

Can someone help ?

Thanks

Hi,

Same issue, INSERT query was working fine but caused only once and keeps on failing after that.

Hi @rohit.k1,

Could you please share your INSERT query and the output that you are getting from it?

It is a best practice to keep a message box just Infront of the INSERT query through which you can identify the issue with the syntax and ensure the data is populating correctly or not.

Dear @AlejandraBonilla,

There could be several reasons why you are experiencing an error when trying to perform insert queries using Automation Anywhere 360 and an Access database. Here are a few things you can try to troubleshoot the issue:

1. Make sure that you have the necessary permissions to insert data into the database. If you do not have permission to insert data, you will receive an error when trying to perform an insert query.

2. Make sure that the syntax of your insert query is correct. Access uses a specific syntax for insert queries, and any errors in the syntax could cause the query to fail.

3. Make sure that the data you are trying to insert is valid. If you are trying to insert data that is not in the correct format or exceeds the maximum length of the field, you may receive an error.

4. Check the log files for any error messages that may provide more information about the cause of the issue. You can find the log files by going to the "Logs" tab in Automation Anywhere 360 and selecting the appropriate bot from the list.

Regards,

Problem: Database Insert command not working in Community Edition.

Error: There is a syntactical error in the SQL statement. - SQL statement got timed out.

I am using the AA Community edition. I am trying to Insert data into MS Access db. from excel.xlsx but getting an error.

My MS  access db. is connecting. I tested my DSN connection. Only the INSERT query not working. Value are populating in all variables and SQL query.

INSERT INTO Test Values(‘$Report_Name$’,‘$RPA_Operator$’,‘$RPA_Operator_Email$’)

-MS Access data types are correct . MS Access data types are

Report_Name = Short Text

RPA_Operator= Short Text

RPA_Operator_Email= Long Text

- Using MS Office 2016 professional edition, 32-bit.

- ODBC driver successfully installed and connected 32-bit.

I successfully ran the same query in the Query design in MS Access which ran successfully and inserting data into db. table field.  

Please let me know if you have any questions. Thank you.

I also emailed on "apeopleopsteam@automationanywhere.com" to get solution/ access, still waiting for a reply. Please help. Thank you.

Have you tried putting the column names in as well? There are no apostrophes in any of the fields?

i have same issue and it’s working fine in sql manager but not in AA!!!,

There is a syntactical error in the SQL statement. 
- SQL statement got timed out.

insert into insights ( prioritynum , start ) values ( '22' , 'test' )

both are varchar

There’s something here, fine people. I have encountered this today. I created is a logger using Sqlite as my file system. It works fine in Community Edition (CE), but it fails in Enterprise Edition (EE). The sql statement is as follows:

INSERT INTO ApplicationSessions (ApplicationName,ServerName,StartDateTime,LoggedBy) VALUES ('CreateApplicationSession','mfdaabc3','2024-08-19 14:25:12.833','niedo');

And the table structure is as follows:

CREATE TABLE IF NOT EXISTS "ApplicationSessions"
(
    Id              INTEGER
        constraint ApplicationSessions_pk
            primary key autoincrement,
    ApplicationName TEXT collate NOCASE not null,
    ServerName      TEXT collate NOCASE not null,
    StartDateTime   TEXT collate NOCASE,
    EndDateTime     TEXT collate NOCASE,
    LoggedBy        Text default ''
);

CREATE TRIGGER IF NOT EXISTS StandardizeApplicationSession
   AFTER INSERT ON ApplicationSessions
    BEGIN
        UPDATE ApplicationSessions SET 
            ApplicationName = Trim(ApplicationName)
            , ServerName = Trim(ServerName)
            , LoggedBy = Upper(Trim(LoggedBy)) 
        WHERE ROWID = new.ROWID;
    END;

The insert statement also works from the sqlite3 command line app. But when I test the system using my account in EE, then I get “There is a syntactical error in the SQL statement. - SQL statement got timed out.”

Things I have tried:
_ setting time out seconds
_ making sure there is no unnecessary whitespace in the insert command
_ making sure there was a semi-colon at the end of the statement
_ reworking the create table syntax so there was no unnecessary whitespace in that (looking for anything)
_ i dropped the trigger
_ destroying the database and building it by hand ahead of bot testing

I’ll add more detail, trial and errors as I continue to work this. Real head scratcher.

Replying to my own post. Unfortunately, it is not a solution. It just more details of what I have attempted.

I abandoned my bot framework and just cobbled together a simple bot that has just the necessary statements to open, insert, end. (My thought process was that maybe the database session variable was getting corrupted being passed around to the various bots.) But it results with the same time out error:

I have confirmed the following: the database file does exist (no brainer), the table does exist, the actual insert does work within sqlite3.exe. There are no permissions with a sqlite database unlike server based databases. If you can connect to it, you can use it.

Note in the image above there are two “Database: Insert...” tasks. The first one executes; it is a PRAGMA statement for database control. It executes fine. The second one is the INSERT failure.

I have also connected to the database file using JetBrains sqlite driver and there are no issues.

Again, my problems are with the Enterprise Edition and not Community Edition. It appears to be the jdbc adapter that is employed by AA. I can’t think of any other reason for the problem. There appears to be a real problem, here. What URL should I use to log an official bug report with Automation Anywhere?

Thank you all for your time and consideration, dvn

I am to blame for my problems. Forget everything I said previously. Let me illustrate what I done and how the problem came to be:

• My application opens with a bot to create a connection to the database
• This bot calls into a child bot to look for an existing database or create one
  • This child bot did not find the database
  • Child bot assemble the file name with elements passed to it and a global environment variable
  • Child bot created and initialized db with all the table structures
  • Control is returned to the caller
• The caller (create a connection) then attempts connection to the db (or so I thought)
  • It built the file name, but the file name lacked the environment
  • sqlite would create an empty db in memory, but not commit it to the file system
  • My bot then tried to insert into a table that did not exist; this caused the time out
  • Because there was no change to the db, sqlite did not save it to file system. So the errant db was not visible.

So, basically, I was looking in the db (environment in file name) and everything would look fine. There was no copy of the errant db because it would disappear. I had to catch it. This was slipped by me, even when debugging line by line.

The fix was to move all file name elements (credentials) into the child bot and the child would send back to the caller the file name it had assembled. The caller would then connect to the db and initialize the database session correctly.

I was to blame.