1

I have below dynamic SQL script which runs on SQL Server with no issues.

DECLARE 
@file_type_id int = 1,
@filing_id bigint = 57,
@created_at datetime = GETDATE(),
@created_by bigint = 2,
@is_required bit = 1,
@insertquery nvarchar(MAX),
@Filepath nvarchar(MAX) = 'C:\SampleTestFiles\MyWordDoc.doc';

SET @insertquery = 
'DECLARE @Document AS VARBINARY(MAX);
 SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK ' + QUOTENAME(@filepath,'''') +', SINGLE_BLOB ) AS Doc; 
 INSERT INTO [TEST].[dbo].[MyTable]  ( [file_type_id], [file],  [file_name], [filing_id], [created_at], [created_by], [is_required])
 VALUES (@file_type_id, @Document, @file_name, @filing_id , @created_at, @created_by, @is_required);';

EXEC sp_executesql 
@insertquery, 
N'@file_name varchar(100),@file_type_id int,@filing_id bigint,@created_at datetime, @created_by bigint,@is_required bit',
@file_name, @file_type_id, @filing_id, @created_at, @created_by, @is_required;

I am trying to execute the same SQL using Python but it doesn't runs and gives lot of syntax errors in Python IDE. Please can someone help me in correcting the Python code.

filepath = 'C:\SampleTestFiles\MyWordDoc.doc'
file_type_id = 1
file_name = 'test'
filing_id = 57
created_at = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
created_by = 1
is_required = 1

Query = '''
SET @insertquery = 
DECLARE @Document AS VARBINARY(MAX);
SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK \' + QUOTENAME(?,\'\'\'\') +\', SINGLE_BLOB) AS Doc;
INSERT INTO[TEST].[dbo].[MyTable]([file_type_id], [file], [file_name], [filing_id], [created_at], [created_by], [is_required])
VALUES(?,@Document, ?, ?, ?, ?, ?)
        '''
values = (filepath, file_type_id, file_name, filing_id, created_at, created_by, is_required)

# Execute SQL Insert Query
cursor = conn.cursor()
cursor.execute(Query, values)
cursor.close()
conn.commit()
print("File inserted..")
user2961127
  • 963
  • 2
  • 17
  • 29
  • 1
    If you're getting syntax issues, you should be telling us what those are. – Thom A Nov 21 '19 at 21:13
  • @Larnu: This the same question which you solved early today:https://stackoverflow.com/questions/58977442/conversion-failed-when-converting-the-varchar-value-error-in-sql I am trying to execute the same SQL code using Python. – user2961127 Nov 21 '19 at 21:16
  • 1
    I recognise my own style of Dynamic SQL, don't worry. But that doesn't tell us the errors you're getting. – Thom A Nov 21 '19 at 21:18
  • This is the error, I am receiving: cursor.execute(Query, values) pyodbc.ProgrammingError: ('The SQL contains 6 parameter markers, but 7 parameters were supplied', 'HY000') – user2961127 Nov 21 '19 at 21:21
  • here 6 parameters: VALUES(?,@Document, ?, ?, ?, ?, ?), but here 7 parameters: values = (filepath, file_type_id, file_name, filing_id, created_at, created_by, is_required). – Rufat Nov 21 '19 at 21:32
  • The 7th parameter, I am using at:` QUOTENAME(?,\'\'\'\')` – user2961127 Nov 21 '19 at 21:33
  • sorry you are right – Rufat Nov 21 '19 at 21:35
  • Why is `sp_executesql` in your python script? – Thom A Nov 21 '19 at 21:39
  • it is not in Python script. I am using cursor.execute(Query, values) – user2961127 Nov 21 '19 at 21:40
  • 1
    I think here is error: filepath = 'C:\SampleTestFiles\MyWordDoc.doc', should be filepath = 'C:\\SampleTestFiles\\MyWordDoc.doc' – Rufat Nov 21 '19 at 21:43
  • That won't work, @user2961127 . You're missing the point of a dynamic statement. This is why it's failing; you're trying to parametrise a value in a literal string. – Thom A Nov 21 '19 at 21:46
  • 1
    It worked!. I hardcoded the path in BULK statment as this: ( BULK 'C:\\SampleTestFiles\\MyWordDoc.doc', SINGLE_BLOB) Now I am trying to see if I can replace the path with variable in python. – user2961127 Nov 21 '19 at 21:53
  • Which API are you using in Python (please include connection line -redact user/pwd as needed)? You may not be able to run multiple queries in one `cursor.execute` call. Did all work with hard-coding which I answered a while back that you must [hard code names](https://stackoverflow.com/a/47534232/1422451) with `BULK`? – Parfait Nov 22 '19 at 03:27

1 Answers1

0

the error makes sense:

Every question mark "?" is a parameter. In 

Query = '''
SET @insertquery = 
DECLARE @Document AS VARBINARY(MAX);
SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK \' + QUOTENAME(?,\'\'\'\') +\', SINGLE_BLOB) AS Doc;
INSERT INTO[TEST].[dbo].[MyTable]([file_type_id], [file], [file_name], [filing_id], [created_at], [created_by], [is_required])
VALUES(?,@Document, ?, ?, ?, ?, ?)

you have 6 of them. values = (filepath, file_type_id, file_name, filing_id, created_at, created_by, is_required) provide the list of parameters and it contains 7 values.

if you ask me 

VALUES(?,@Document, ?, ?, ?, ?, ?)

should 

VALUES(?,?, ?, ?, ?, ?, ?)

And then the count of parameters markers will match the count of those provided

zip
  • 3,938
  • 2
  • 11
  • 19