0

I am reading a SQL Server rowversion column into a Pandas Dataframe. The dataframe looks like this:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1 entries, 0 to 0
Data columns (total 1 columns):
 #   Column            Non-Null Count  Dtype 
---  ------            --------------  ----- 
 0   rowversion  1 non-null      object
dtypes: object(1)
memory usage: 136.0+ bytes

Then create a variable

rowversion = df.iloc[0]['rowversion']

Then inject that rowverison variable into another SQL query

sql = pd.read_sql_query(
'''
SELECT *
FROM [dbo].[Table]
WHERE [RowVersion] > [%s]
ORDER BY [RowVersion] ASC
''' %(rowversion), source_engine)
df = pd.DataFrame(sql_query)

However this results in an error

[SQL Server]Incorrect syntax near '\\x00'.

That's because the query being ran is

rowversion > b'\x00']

I am guessing this is because Pandas handles binary differently from how SQL Server is expecting it. If so, how do I convert the binary back into Hex for injecting into a SQL Server query?

UPDATE:

Applying the below code seems to fix the problem, but I'm not sure if it's the correct way of fixing the issue?

rowversion = rowversion.hex()
rowversion = '0x' + rowversion
Stakky
  • 1
  • 1
  • Sounds like you are injecting instead of using parameters. A `rowversion` is just an 8-byte array, or `binary(8)`, so you should be passing that in as a byte-array parameter. We need to see the code where you put this into the SQL query – Charlieface Nov 25 '21 at 20:33
  • Aside... Is the column you're trying to insert the rowversion value into also a `rowversion` data type? SQL Server won't let you do that and will error with something similar to: `Msg 273, Level 16, State 1, Line 1 Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.` [The error says `timestamp`, but it's actually a `rowversion` column.] – AlwaysLearning Nov 26 '21 at 02:23
  • @AlwaysLearning Yes, it's RowVersion – Stakky Nov 26 '21 at 09:46
  • 1
    Does this answer your question? [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) – Charlieface Nov 26 '21 at 10:13
  • You should be parameterizing this. Something like `pd.read_sql_query(''' SELECT * FROM [dbo].[Table] WHERE [RowVersion] > @rv ORDER BY [RowVersion] ASC ''', source_engine, params = { 'rv' : rowversion })` not sure exact syntax – Charlieface Nov 26 '21 at 10:15

0 Answers0