Hi everyone,
I am working on a project where I need to insert SAP data into a vendor SQL Server database. To do so, my script will need to read a pipe delimited file down, do lookups in multiple other tables, and insert new rows into three tables. Most of the data I need uses normal tables with a key so it is pretty easy. However, one of the configuration tables needed uses a field that has an image field type. The field contains hex code. The hex string in each is over 8000 characters. I can't find a good way to parse this field out, though. Somehow, when the application users make changes or add a new record, it stores it in hex format which seems crazy but I confirmed this with the vendor. I can only guess that other languages do this more easily than TSQL. I have tried many variations of convert or cast to do this but am coming up empty.
This websitehttps://www.csoft.co.uk/developer/support/hex-converter does an ok job of converting the string but it has a bunch of garbage in it. So, using this listhttp://www.theasciicode.com.ar/ascii-table-characters.pdf, I created a little FoxPro program to read two characters at a time and store this to a variable. I could duplicate this using TSQL if it worked. Anyway, doing this is a little better than the converter sites but still not really useable. There are codes like: NULL, BEL, STX, ACK, SOH, STX, EOT, BS, HT, etc. that I'm not sure what to do with. I tried putting a space or dash in for these but that does not help. I would like to parse this hex string into a new table.
Any suggestions?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Sent from my iPhone
On Nov 17, 2017, at 6:06 PM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:
Hi everyone,
I am working on a project where I need to insert SAP data into a vendor SQL Server database. To do so, my script will need to read a pipe delimited file down, do lookups in multiple other tables, and insert new rows into three tables. Most of the data I need uses normal tables with a key so it is pretty easy. However, one of the configuration tables needed uses a field that has an image field type. The field contains hex code. The hex string in each is over 8000 characters. I can't find a good way to parse this field out, though. Somehow, when the application users make changes or add a new record, it stores it in hex format which seems crazy but I confirmed this with the vendor. I can only guess that other languages do this more easily than TSQL. I have tried many variations of convert or cast to do this but am coming up empty.
This websitehttps://www.csoft.co.uk/developer/support/hex-converter does an ok job of converting the string but it has a bunch of garbage in it. So, using this listhttp://www.theasciicode.com.ar/ascii-table-characters.pdf, I created a little FoxPro program to read two characters at a time and store this to a variable. I could duplicate this using TSQL if it worked. Anyway, doing this is a little better than the converter sites but still not really useable. There are codes like: NULL, BEL, STX, ACK, SOH, STX, EOT, BS, HT, etc. that I'm not sure what to do with. I tried putting a space or dash in for these but that does not help. I would like to parse this hex string into a new table.
Any suggestions?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Most all SQL code is transactional - thus acts on Large sets of - and NOT Single rec's! I believe it's the Cursor related command. Thus U can work a Single record at a time & control Ur data manipulations down to the record And Field level - which may B what U need...
-K-
Sent from my iPhone
On Nov 17, 2017, at 6:06 PM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:
Hi everyone,
I am working on a project where I need to insert SAP data into a vendor SQL Server database. To do so, my script will need to read a pipe delimited file down, do lookups in multiple other tables, and insert new rows into three tables. Most of the data I need uses normal tables with a key so it is pretty easy. However, one of the configuration tables needed uses a field that has an image field type. The field contains hex code. The hex string in each is over 8000 characters. I can't find a good way to parse this field out, though. Somehow, when the application users make changes or add a new record, it stores it in hex format which seems crazy but I confirmed this with the vendor. I can only guess that other languages do this more easily than TSQL. I have tried many variations of convert or cast to do this but am coming up empty.
This websitehttps://www.csoft.co.uk/developer/support/hex-converter does an ok job of converting the string but it has a bunch of garbage in it. So, using this listhttp://www.theasciicode.com.ar/ascii-table-characters.pdf, I created a little FoxPro program to read two characters at a time and store this to a variable. I could duplicate this using TSQL if it worked. Anyway, doing this is a little better than the converter sites but still not really useable. There are codes like: NULL, BEL, STX, ACK, SOH, STX, EOT, BS, HT, etc. that I'm not sure what to do with. I tried putting a space or dash in for these but that does not help. I would like to parse this hex string into a new table.
Any suggestions?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Find out if there is a BOD available and fill that instead. Then pass the BOD to the listener and have it do its voodoo. A BOD is an XML object is a simple explanation.
On Fri, Nov 17, 2017 at 5:06 PM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:
Hi everyone,
I am working on a project where I need to insert SAP data into a vendor SQL Server database. To do so, my script will need to read a pipe delimited file down, do lookups in multiple other tables, and insert new rows into three tables. Most of the data I need uses normal tables with a key so it is pretty easy. However, one of the configuration tables needed uses a field that has an image field type. The field contains hex code. The hex string in each is over 8000 characters. I can't find a good way to parse this field out, though. Somehow, when the application users make changes or add a new record, it stores it in hex format which seems crazy but I confirmed this with the vendor. I can only guess that other languages do this more easily than TSQL. I have tried many variations of convert or cast to do this but am coming up empty.
This websitehttps://www.csoft.co.uk/developer/support/hex-converter does an ok job of converting the string but it has a bunch of garbage in it. So, using this listhttp://www.theasciicode. com.ar/ascii-table-characters.pdf, I created a little FoxPro program to read two characters at a time and store this to a variable. I could duplicate this using TSQL if it worked. Anyway, doing this is a little better than the converter sites but still not really useable. There are codes like: NULL, BEL, STX, ACK, SOH, STX, EOT, BS, HT, etc. that I'm not sure what to do with. I tried putting a space or dash in for these but that does not help. I would like to parse this hex string into a new table.
Any suggestions?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
OTTOMH - If you are just inserting fields from other existing tables why get involved in converting stuff - why not just pick the fields up as is - e.g.: INSERT INTO TableNew (PriKey, flda, fldb) SELECT ForeignKey, fldx, fldb FROM SomeView;
or possibly:
SELECT ForeignKey, fldx, fldb INTO TableNew FROM SomeTblView tv WHERE tv.ForeignKey = m.lookupkey
On 18-Nov-2017 4:36 AM, Matt Wiedeman wrote:
Hi everyone,
I am working on a project where I need to insert SAP data into a vendor SQL Server database. To do so, my script will need to read a pipe delimited file down, do lookups in multiple other tables, and insert new rows into three tables. Most of the data I need uses normal tables with a key so it is pretty easy. However, one of the configuration tables needed uses a field that has an image field type. The field contains hex code. The hex string in each is over 8000 characters. I can't find a good way to parse this field out, though. Somehow, when the application users make changes or add a new record, it stores it in hex format which seems crazy but I confirmed this with the vendor. I can only guess that other languages do this more easily than TSQL. I have tried many variations of convert or cast to do this but am coming up empty.
This websitehttps://www.csoft.co.uk/developer/support/hex-converter does an ok job of converting the string but it has a bunch of garbage in it. So, using this listhttp://www.theasciicode.com.ar/ascii-table-characters.pdf, I created a little FoxPro program to read two characters at a time and store this to a variable. I could duplicate this using TSQL if it worked. Anyway, doing this is a little better than the converter sites but still not really useable. There are codes like: NULL, BEL, STX, ACK, SOH, STX, EOT, BS, HT, etc. that I'm not sure what to do with. I tried putting a space or dash in for these but that does not help. I would like to parse this hex string into a new table.
Any suggestions?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]