1

I'm ingesting data from Salesforce to Azure SQL Database using Python with pyodbc.

I make a first connexion with Salesforce as shown bellow:

cnxn = pyodbc.connect('DRIVER={Devart ODBC Driver for Salesforce};User ID=xxx;Password=xxx;Security Token=xxx')

Then I import Salesforce data, as shown bellow:

cursor = cnxn.cursor()
cursor.execute("select * from X where Y > 'VALUE'")
row = cursor.fetchall()

After that I make a second connexion with the destination which is Azure SQL Database, as shown bellow:

cnxn = pyodbc.connect('DRIVER={Devart ODBC Driver for SQL Azure};Server=xxx;Database=xxx;Port=1433;User ID=xxx;Password=xxx')

Until now, everything is working fine. But when I try to insert the output that I got from Salesforce (in variable row) I face data type problems, from which we can cite:

  • Tabulations "\t"
  • Back to the line sign (in Azure SQL Database is CHAR(13) + CHAR(10) +)
  • Charaters that contains quote (e.g. "big data's technology")

Here is how I launch the insertion query:

cursor.executemany('INSERT INTO dbo.Account (Column_a,Column_b,Column_c) VALUES (?,?,?,?)', row)
cursor.commit()

Here is the first error I get:

pyodbc.Error: ('HY000', '[HY000] [Devart][ODBC][Microsoft SQL Azure]Statement(s) could not be prepared.\r\nMust declare the scalar variable "@_39".\r\nLine 1: Specified scale 14 is invalid. (0) (SQLExecDirectW)')
DSEB
  • 67
  • 1
  • 7
  • For the Azure connection can you try using Microsoft's "ODBC Driver 17 for SQL Server" and see if that works any better? The problem with strings that include single quotes, even when using a parameterized query, makes me suspect a bug in Devart's driver. – Gord Thompson Dec 25 '20 at 00:31
  • 1
    Note also that using `INSERT INTO table_name VALUES …` (without a column list) is considered bad form. You really should be using `INSERT INTO table_name (col_a, col_b, col_c, col_d) VALUES (?,?,?,?)` – Gord Thompson Dec 25 '20 at 00:36

1 Answers1

0

This issue was apparently caused by a defect in the

Devart ODBC Driver for SQL Azure

Using Microsoft's

ODBC Driver 17 for SQL Server

solved the problem .

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418