Unable to update external data source

The 1C:Enterprise developers forum

#1
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

Hi,

I'm having an issue while trying to upd ate a record in external data source.
1C platform generates wrong TSQL code and fails with syntax error.

Could you please let me know if there a way to avoid the error by changing anything on my side?

Connection string for the external data source:
DRIVER={SQL Server};SERVER= < server name >;Trusted_Connection=yes;DATABASE=DBA;

TSQL query generated by 1C:Platform (obviously it is wrong):

Code
UPD ATE "Meta"."TableName" T1  
SE T databaseName = 'a', databaseServerRoleNonProd = 's', databaseServerRoleProd = 's', isActiveNonProd = 1, isActiveProd = 1
WHERE T1.id = 1


Proper query:
Code
UPDATE T1  
SE T databaseName = 'a', databaseServerRoleNonProd = 's', databaseServerRoleProd = 's', isActiveNonProd = 1, isActiveProd = 1
FR OM "Meta"."TableName" T1
WH ERE T1.id = 1



Code
Platform: 1C:Enterprise 8.3, training version (8.3.15.1489)
Configuration: Configuration
Mode: File (without compression)
Application: Thin client
Localization: Infobase: English (United States), Session: English
Interface mode: Taxi

Errors:
--------------------------------------------------------------------------------
11/22/2019 2:23:45 PM
External database error:
error executing query
, reason:
ODBC error. SQLSTATE: 42000
Error number: 102
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'T1'.

 
#2
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

One more error while trying to ins ert records:

Code
INS ERT IN TO "Meta"."table"  (id,databaseName,databaseServerRoleNonProd,databaseServerRoleProd,isActiveNonProd,isActiveProd) VALUES(23,'a','a','a',TRUE,TRUE)


Code
ODBC error. SQLSTATE: 42S22
Error number: 207
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'TRUE'.



`TRUE` is not a valid value for SQL Server. It should be 1/0 values.

Edited: Alexey Bochkov - Nov 23, 2019 01:40 AM
 
#3
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

Same issues with different driver:

Code
ODBC error. SQLSTATE: 42000
Error number: 102
Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'T1'.

 
#4
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

Same issue with 8.3.16.1063 (which is not available to download here yet):

Code
Platform: 1C:Enterprise 8.3, training version (8.3.16.1063)
Configuration: Configuration
Mode: File (without compression)
Application: Thin client
Localization: Infobase: English (United States), Session: English
Interface mode: Taxi

Errors:
--------------------------------------------------------------------------------
11/22/2019 3:16:51 PM
External database error:
error executing query
, reason:
ODBC error. SQLSTATE: 42000
Error number: 102
Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'T1'.

 
#5
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Hi Alexey!

Can you send me your 1C code? And, if possible, SQL dump?

Aleksandr.

 
#6
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

Aleksandr,

Here it is. You need SQL 2017 to restore the sql database.

 
#7
People who like this:1Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Alexey, thanks!

I`ll reply in a few days

 
#8
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Alexey,

unfortunately, I was not able to simulate the error that occurs to you.

Can you do it on version 8.3.14?

 
#9
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

By the way, there is another way to update external data:
you can create a stored procedure on SQL Server, and this procedure will update the necessary data.

 
#10
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

Aleksandr,


Thanks for your reply.
Is there anything I can do to help you track down this issue? Maybe enable and collect tech log? With what configuration?

Since you've released an ability to update an external data source, I've tried it on different platforms and computers but every time had these errors.
Was hope 8.3.16 is finally stable.. but I don't discount an option that I'm doing something wrong.

Edited: Alexey Bochkov - Dec 03, 2019 11:05 PM
 
#11
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

Quote
By the way, there is another way to update external data:
I'm not that desperate... this will defeat the whole purpose of having simple 1C UI on top of external data source and make code unnecessary more complex.

Edited: Alexey Bochkov - Dec 03, 2019 11:03 PM
 
#12
People who like this:0Yes/0No
Active user
Rating: 3
Joined: Apr 2, 2012
Company: BizzSimple.com

Aleksandr,

I turned out that it was my fault indeed - seems that `DBMS type` parameter is mandatory. It was set in Designer..
Anyway, it works now.

Thank a lot!

Edited: Alexey Bochkov - Dec 04, 2019 09:58 PM
 
#13
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Hi Alexey,

Glad to hear it! It`s fine!

 
#14
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Quote
I'm not that desperate... this will defeat the whole purpose of having simple 1C UI on top of external data source and make code unnecessary more complex.

I agree with you that this will complicate the work. But such a solution will not depend on possible 1C bugs :-)

 
Subscribe