Re: [python-win32] python win32com.client; reverse engineer sql server timestamp
On Dec 18, 2018, at 1:05 PM, Anrik Drenth wrote: > > Couple of good insights there. Would you have example python code that shows > the Unicode-to-ASCII conversion in action? > > So 0x0189CF01 to 003F49 > > The current code is written in Python 2.7, its selecting the data from SQL > Server and writing it into Access 97. > As Access 97 is somewhat dated we want to get rid of this code and hand > responsibility back to the client. > > The sql server timestamp field is a rowid. It was used back in the day under > the mistaken assumption it was a datetime field. It is not, subsequently the > field exists but has never been used. Aha. Here's my alternate theory, then. I'll bet that field in the Access table is declared as "AutoNumber". In that case, Access would be assigning its own unique row number to the field, totally ignoring the incoming field from SQLServer. That would make a certain amount of sense, but you'd have to look at the other values in that field to be sure. > We still have to re-produce the same exact same value in the field on the off > chance it is used by the clients system. Don't you have the code that originally created those fields? It is absolutely pointless for us to guess how this was done. — Tim Roberts, t...@probo.com Providenza & Boekelheide, Inc. ___ python-win32 mailing list python-win32@python.org https://mail.python.org/mailman/listinfo/python-win32
Re: [python-win32] python win32com.client; reverse engineer sql server timestamp
On Dec 17, 2018, at 5:20 PM, Anrik Drenth wrote: > > I'm looking for the mapping table / logic that will render sql server > timestamp values as win32com.client does. OK, then why did you not tell us what the win32com.client does? > I'm currently importing a text file into Access. The text file contains a > column with a timestamp value which has been extracted from sql server. The > values for the (Access String 50) column look something like > "0x0189CF01". That's a 32-bit integer. You need to be storing this as a 32-bit integer, not as a string. Or, best of all, as an Access date/time datatype. Exactly which data type in the SQL Server column? How was the text file generated? In virtually every case, SQL Server will export date and time columns as date time strings (that is, "2018-12-17". How did you get a hex representation? > Using VBA I want to convert these to the exact same value that win32com > creates. > > In python (sample code below) the timestamp value is stored as an 8 byte, > bytearray. Which then gets interpreted by Access. Which version of Python are you using? You are taking binary data and shoving it through a Unicode-to-ASCII conversion. That produces garbage. That's not a string. It's an integer. > Below is a sample of how the sql server timestamp value is stored in Access: > > SQL Server (Timestamp) | Access (Text 50) 0x0189CF01 | 003F49 > 0x0189D001 | 003F69 0x01B54DFF | 003F6D > 0x01F74701 | 003F4E 0x0189C003 | 003F70 > So it tries to do a mapping, sometimes 1 to 1, sometimes not as page 5 of the > below link demonstrates Right. You're producing garbage. The "3F" in there is the "?" character, which means "this character does not exist in the current 8-bit code page". It's not a Unicode string, don't try to treat it like one. — Tim Roberts, t...@probo.com Providenza & Boekelheide, Inc. ___ python-win32 mailing list python-win32@python.org https://mail.python.org/mailman/listinfo/python-win32
[python-win32] python win32com.client; reverse engineer sql server timestamp
I'm looking for the mapping table / logic that will render sql server
timestamp values as win32com.client does.
I'm currently importing a text file into Access. The text file contains a
column with a timestamp value which has been extracted from sql server.
The values for the (Access String 50) column look something like "
0x0189CF01".
Using VBA I want to convert these to the exact same value that win32com
creates.
In python (sample code below) the timestamp value is stored as an 8 byte,
bytearray. Which then gets interpreted by Access.
Below is a sample of how the sql server timestamp value is stored in Access:
*SQL Server (Timestamp) | Access (Text 50)* 0x0189CF01 | 003F49
0x0189D001 | 003F69 0x01B54DFF | 003F6D
0x01F74701 | 003F4E 0x0189C003 | 003F70
So it tries to do a mapping, sometimes 1 to 1, sometimes not as page 5 of
the below link demonstrates
https://scripts.sil.org/cms/scripts/render_download.php?format=file_id=UnicodeWord=unicodeissuesinword97-2000.pdf
I'd appreciate it if anyone has logic or a link to the mapping tables.
*Sample Python code *
MSAccessconnect = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=test.mdb;'
SQLServerconnect = '[ODBC;Driver=SQL
Server;SERVER=server1;Database=test;UID=%s;PWD=%s]' %('user', 'pass')
strsql = "SELECT * FROM %s.%s;" % (SQLServerconnect, "sqltesttable")
print strsql
from win32com.client import Dispatch
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = MSAccessconnect
oConn.Open()
oRS = Dispatch('ADODB.RecordSet')
oRS.ActiveConnection = oConn
oRS.Open(strsql)
while not oRS.EOF:
print oRS.Fields.Item("Stamp").Value
The text displayed is ▒▒▒
___
python-win32 mailing list
python-win32@python.org
https://mail.python.org/mailman/listinfo/python-win32
