search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.93k stars 562 forks source link

Long byte string / regular string not binding with geometry function when fast_executemany=True #868

Closed ww2406 closed 1 year ago

ww2406 commented 3 years ago

Please first make sure you have looked at:

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

Issue

Using the following table:

CREATE TABLE geomTest (
    OBJECTID INT,
    Attr1 varchar(30),
    Shape geometry
);

inserting into a geometry column using geometry::STGeomFromBinary or geometry::STGeomFromText fails when fast_executemany=True. 

import pyodbc
from shapely.geometry import Point
from shapely import wkb, wkt

conn: pyodbc.Connection = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};Server=localhost;Database=test;Trusted_Connection=Yes;")

cur: pyodbc.Cursor = conn.cursor()
cur.fast_executemany = True

strWkt = """
MULTILINESTRING ((-9261149.6037 4838312.9676,-9260417.8851 4838751.429,-9260114.2814 4838934.1061,-9259842.8895 4839094.1943,-9259492.0113 4839305.9591,-9258761.6829 4839743.2902,-9258570.0851 4839856.7705,-9258559.3412 4839863.1338,-9258389.8833 4839961.7159,-9258267.5384 4840026.2165,-9258114.2646 4840101.9233,-9257972.4499 4840165.6426,-9257822.3257 4840225.8185,-9257702.8447 4840271.2441,-9257578.9731 4840313.8769,-9257392.6692 4840370.5445,-9257231.0474 4840413.204,-9257055.6474 4840454.8559,-9256848.8158 4840491.7562,-9256723.3732 4840510.9186,-9256713.0754 4840512.1247,-9256587.4414 4840526.837,-9256454.7894 4840539.4125,-9256319.9065 4840548.1315,-9256166.274 4840553.6667,-9255986.1681 4840554.4123,-9255853.3966 4840550.3912,-9255639.5794 4840535.3371,-9255414.7128 4840515.3806,-9255278.8457 4840502.2799,-9255243.8497 4840498.9052,-9255147.4068 4840491.2971,-9255029.1244 4840481.462,-9254779.2608 4840460.733,-9254626.2678 4840452.9629,-9254584.4191 4840453.2599,-9254452.596 4840454.1942,-9254292.9354 4840464.1662,-9254169.1012 4840477.755,-9254015.58 4840499.2922,-9253866.502 4840526.3262,-9253764.2202 4840550.2663,-9253627.8695 4840586.0557,-9253481.6729 4840631.8652,-9253333.3332 4840686.5367,-9252999.95 4840825.9871,-9252767.0154 4840923.2382,-9252763.1332 4840924.859,-9252331.5108 4841105.9801,-9251603.7254 4841412.8827,-9251082.4353 4841632.0817,-9250962.3394 4841682.9673,-9250788.0046 4841756.8312,-9250594.0887 4841838.3173,-9250487.5611 4841889.9086,-9250385.4652 4841945.341,-9250332.8051 4841977.3862,-9250309.1988 4841991.7511,-9250246.1248 4842033.6497,-9250227.5634 4842046.4077,-9250224.1886 4842048.7273,-9250208.8286 4842060.0992,-9250133.734 4842115.6953,-9250066.8773 4842169.2328,-9250003.3331 4842223.8541,-9249947.4809 4842275.659,-9249884.5266 4842336.3599,-9249806.0575 4842427.0388,-9249723.1495 4842526.3171,-9249656.5587 4842616.7891,-9249618.7631 4842672.3655,-9249590.1064 4842717.664,-9249583.2288 4842728.5356,-9249559.6387 4842768.0398,-9249553.9934 4842777.5571,-9249548.6726 4842786.5734,-9249457.5603 4842940.9581,-9249374.7187 4843076.8919,-9249327.5192 4843158.86,-9249316.7403 4843176.846,-9249116.2451 4843511.3911,-9249069.6853 4843591.0574,-9249033.7068 4843652.6181,-9248948.556 4843787.2669,-9248886.1434 4843879.537,-9248802.9348 4843996.206,-9248742.5491 4844078.1807,-9248653.9287 4844189.8538,-9248588.9837 4844269.9884,-9248570.8563 4844290.2616,-9248503.9236 4844365.1168,-9248399.2374 4844477.1649,-9248326.902 4844550.0136,-9248316.5684 4844560.4206,-9248199.5089 4844673.0421,-9248129.9923 4844739.2637,-9247981.8887 4844880.3444,-9247685.7316 4845162.4533,-9247507.5557 4845331.7882,-9247504.5099 4845334.6828,-9247200.9734 4845625.1819,-9247158.5311 4845665.8182,-9246990.2743 4845826.9114,-9246784.0003 4846024.1701,-9246472.7618 4846319.3418,-9246384.9156 4846403.3896,-9246294.0386 4846490.3356,-9246151.2061 4846627.0895,-9245965.4646 4846818.2712,-9245856.9901 4846942.8194,-9245791.0802 4847018.4945,-9245691.5542 4847143.7121,-9245594.6346 4847275.5154,-9245522.2995 4847381.5651,-9245427.8411 4847527.204,-9245349.5988 4847656.1397,-9245337.2436 4847678.355,-9245291.9406 4847759.8113,-9245229.1344 4847882.2682,-9245204.7499 4847930.7466,-9245019.4164 4848295.0792,-9245014.6395 4848304.4694,-9244934.2224 4848464.1697,-9244839.6109 4848652.0547,-9244800.4391 4848728.1721,-9244751.4734 4848823.3194,-9244703.4861 4848916.5645,-9244572.33 4849165.7498,-9244473.4559 4849345.7707,-9244360.8941 4849532.93,-9244234.9586 4849724.7105,-9244193.7422 4849787.4755,-9244080.9486 4849942.7333,-9244015.5852 4850032.7018,-9243924.7445 4850152.1563,-9243853.0779 4850245.0775,-9243850.5261 4850248.4608,-9243844.1464 4850256.9194,-9243816.6802 4850293.3351,-9243805.1442 4850308.7131,-9243739.6226 4850394.0971,-9243604.3156 4850570.5734,-9243436.5822 4850789.6653,-9243406.0758 4850829.5531,-9243291.6747 4850979.1327,-9243253.9603 4851029.1463,-9243153.3869 4851162.5167,-9243001.2788 4851360.8862,-9242897.3348 4851496.4396,-9242699.0248 4851757.6385,-9242637.7776 4851836.0583,-9242635.1207 4851839.4601,-9242617.8509 4851861.5724,-9242373.2326 4852179.7637,-9242105.7979 4852531.4309,-9241813.4879 4852913.4883,-9241602.2136 4853189.6219,-9241389.0832 4853467.8893,-9241345.6917 4853528.6221,-9241276.8191 4853620.972,-9241215.7855 4853717.3337,-9241160.6342 4853814.5119,-9241116.2824 4853905.2432,-9241101.4983 4853935.487,-9241038.654 4854083.1277,-9240985.1539 4854245.6224,-9240954.4039 4854364.7638,-9240927.0535 4854491.5715,-9240891.3411 4854710.0686,-9240748.0405 4855609.6636,-9240669.6804 4856094.1433,-9240597.4899 4856537.5292,-9240594.4957 4856555.9188,-9240551.0375 4856844.4836,-9240549.5877 4856913.5355,-9240551.6902 4856979.6739,-9240557.557 4857039.1121,-9240573.4415 4857108.0658,-9240591.0766 4857164.9547,-9240608.7074 4857212.4811,-9240639.8783 4857276.9528,-9240698.8449 4857385.7453,-9240708.2479 4857404.7415,-9240775.0104 4857539.6154,-9240934.2429 4857856.7989,-9241027.154 4858037.1068,-9241094.7691 4858177.7566,-9241108.2695 4858214.7213,-9241111.7641 4858224.5163,-9241117.3553 4858240.1883,-9241130.1998 4858285.2692,-9241137.3826 4858345.8684,-9241139.2303 4858460.9418,-9241141.3726 4858647.631,-9241138.64 4858693.611,-9241134.5617 4858737.0801,-9241127.805 4858766.0774,-9241110.7329 4858803.7003,-9241066.1824 4858869.3342,-9240987.7162 4858913.349,-9240931.3092 4858941.8465,-9240922.8098 4858946.1403,-9240843.3083 4858985.6009,-9240724.5478 4859043.0139,-9240611.7682 4859087.2213,-9240541.7387 4859106.8691,-9240483.2792 4859119.8644,-9240409.6751 4859131.9326,-9240259.4203 4859152.5374,-9240161.5758 4859161.1933,-9240081.6947 4859164.9133,-9240012.4437 4859165.5295,-9239898.1772 4859161.6574,-9239849.2048 4859156.8658,-9239791.5854 4859148.1829,-9239725.4635 4859136.28,-9239681.5143 4859127.4064,-9239590.481 4859111.171,-9239543.1305 4859104.874,-9239477.5213 4859102.7053,-9239435.7558 4859104.4557,-9239391.0803 4859107.9328,-9239247.5545 4859126.2708,-9239166.3829 4859138.8779,-9239095.7241 4859150.5042,-9238993.9509 4859164.6518,-9238964.2514 4859169.4495,-9238900.8925 4859179.6843,-9238885.7512 4859181.9439,-9238826.3601 4859190.0368,-9238788.7457 4859195.162,-9238656.645 4859214.631,-9238532.0892 4859229.4963,-9238325.3744 4859261.5253,-9238200.132 4859279.8624,-9238127.7259 4859290.4627,-9237925.539 4859320.4331,-9237857.3745 4859349.7272,-9237814.9529 4859368.1349,-9237783.9249 4859386.4798,-9237757.7749 4859404.1863,-9237729.4205 4859428.2364,-9237704.7228 4859451.0412,-9237690.7825 4859467.0947,-9237684.5868 4859474.2295,-9237669.141 4859493.308,-9237650.3833 4859523.8423,-9237636.4756 4859548.2228,-9237619.3894 4859587.1232,-9237611.1445 4859607.5338,-9237608.3642 4859618.0243,-9237599.4119 4859656.0637,-9237595.5727 4859676.1696,-9237590.7802 4859716.1607,-9237588.3085 4859748.6512,-9237584.7171 4859838.319,-9237583.9837 4859849.8307,-9237583.6924 4859858.1392,-9237578.1586 4860016.0029,-9237568.9567 4860164.6302,-9237564.8874 4860233.3401,-9237561.7867 4860320.2874,-9237563.7112 4860398.8958,-9237570.8294 4860463.1128,-9237583.2877 4860540.1523,-9237586.0542 4860558.1001,-9237615.4703 4860749.3387,-9237628.8662 4860836.6009,-9237638.6683 4860900.4517,-9237648.0907 4861005.9224,-9237656.9189 4861156.8168,-9237663.1352 4861217.7594,-9237669.5658 4861280.8039,-9237680.0695 4861383.7772,-9237686.876 4861467.9497,-9237688.8366 4861553.3707,-9237688.5808 4861646.3647,-9237686.6964 4861716.6859,-9237683.1725 4861762.0669,-9237679.6459 4861806.6932,-9237672.2262 4861875.8718,-9237661.6295 4861958.7564,-9237652.9715 4862029.8737,-9237642.8366 4862106.2901,-9237640.7876 4862148.6777,-9237639.968 4862165.6328,-9237639.7717 4862237.8639,-9237640.8432 4862297.5891,-9237645.1748 4862356.4925,-9237652.347 4862425.5886,-9237661.6562 4862487.5484,-9237663.0019 4862496.5072,-9237664.8352 4862508.87,-9237674.0015 4862570.6836,-9237686.2852 4862653.7883,-9237693.3484 4862701.5738,-9237714.3482 4862841.2359,-9237717.233 4862860.421,-9237717.5813 4862862.7592,-9237718.6266 4862869.7737,-9237725.9509 4862918.396,-9237729.4317 4862941.5516,-9237747.0147 4863060.3583,-9237775.0665 4863266.5775,-9237800.2112 4863445.6292,-9237822.5473 4863596.2593,-9237845.0711 4863743.8377,-9237850.0507 4863780.7774,-9237874.8772 4863952.7514,-9237885.0873 4864021.5565,-9237893.4979 4864088.7152,-9237898.9389 4864150.6522,-9237902.7558 4864219.6345,-9237902.4461 4864290.3092,-9237896.899 4864388.4458,-9237892.2211 4864470.4817,-9237889.4923 4864518.3361,-9237879.5234 4864649.0545,-9237864.5824 4864857.1695,-9237861.7401 4864898.5654,-9237851.7915 4865043.4523,-9237851.3652 4865049.6617,-9237831.3454 4865312.0866,-9237813.269 4865526.6185,-9237799.3188 4865727.9365,-9237792.7027 4865838.1177,-9237786.609 4866019.1522,-9237785.6983 4866046.2036,-9237783.6885 4866362.9431,-9237784.7819 4866394.8271,-9237783.8257 4866474.7439,-9237783.5497 4866595.3267,-9237787.3957 4866712.1925,-9237792.7702 4866770.8383,-9237802.2731 4866826.69,-9237815.9192 4866882.9817,-9237833.262 4866942.026,-9237853.8067 4866997.357,-9237872.9067 4867040.684,-9237891.9771 4867078.4667,-9237914.7394 4867118.0778,-9237940.2731 4867159.5222,-9237969.4825 4867200.0215,-9238017.2029 4867259.3652,-9238043.597 4867289.7171,-9238104.1911 4867346.6809,-9238128.9398 4867367.9837,-9238200.761 4867418.2343,-9238263.5832 4867461.325,-9238361.5036 4867526.8633,-9238449.7303 4867586.9105,-9238508.8776 4867630.9445,-9238562.9715 4867676.3923,-9238609.2329 4867720.4952,-9238642.3625 4867753.3692,-9238666.7595 4867782.7371,-9238702.7741 4867826.256,-9238733.8434 4867869.0573,-9238770.5102 4867925.6894,-9238802.0994 4867979.5769,-9238828.6174 4868031.6442,-9238856.2898 4868098.2094,-9238857.0804 4868100.1113,-9238879.9502 4868161.2041,-9238896.3444 4868213.3277,-9238913.2708 4868278.8497,-9238923.2706 4868340.2499,-9238931.6748 4868397.1769,-9238936.2301 4868437.6777,-9238947.461 4868555.91,-9238955.4488 4868670.0024,-9238960.7569 4868799.3611,-9238963.5327 4868886.2203,-9238964.1461 4868990.4399,-9238962.8828 4869096.2708,-9238961.56 4869148.9587,-9238956.2202 4869232.8439,-9238949.3344 4869320.6855,-9238941.8987 4869391.8932,-9238935.8234 4869444.2959,-9238934.1514 4869453.7021,-9238917.4319 4869547.7656,-9238894.65 4869677.7416,-9238875.9362 4869788.7687,-9238869.8189 4869848.8814,-9238865.1217 4869915.918,-9238863.5876 4869972.3088,-9238862.9994 4870033.3166,-9238865.1913 4870089.4755,-9238869.3991 4870140.2854,-9238874.5008 4870201.0181,-9238885.6234 4870298.9348,-9238900.7874 4870378.3447,-9238915.4181 4870444.3555,-9238931.443 4870504.5991,-9238950.1542 4870567.104,-9238971.6937 4870633.5388,-9238992.1514 4870692.8713,-9239022.6483 4870775.1498,-9239047.037 4870857.2857,-9239077.5693 4870968.5061,-9239097.3727 4871053.9045,-9239111.5731 4871125.005,-9239124.447 4871205.8204,-9239134.1424 4871294.9731,-9239141.0845 4871385.5285,-9239146.1672 4871472.8605,-9239171.1446 4871914.1529,-9239193.4009 4872320.3479,-9239202.8593 4872491.7951,-9239208.5709 4872609.6425,-9239212.2089 4872677.3639,-9239216.6722 4872752.8899,-9239225.3579 4872899.8611,-9239227.6346 4872937.136,-9239235.2234 4873061.387,-9239261.6422 4873554.0474,-9239273.6758 4873774.7387,-9239286.4065 4873996.3558,-9239304.33 4874324.9825,-9239311.593 4874445.3076,-9239314.0245 4874538.7689,-9239311.9405 4874620.6189,-9239307.8897 4874679.5923,-9239303.5394 4874726.0842,-9239294.4311 4874786.7053,-9239279.8379 4874860.4743,-9239272.9252 4874895.4176,-9239250.8269 4874981.3765,-9239226.5937 4875055.722,-9239201.3858 4875120.1333,-9239176.8475 4875180.838,-9239129.9021 4875270.8047,-9239076.51 4875361.2673,-9239024.0548 4875437.5811,-9238960.0184 4875519.6759,-9238883.6379 4875607.7247,-9238745.7163 4875758.8898,-9238608.7604 4875908.4151,-9238531.718 4875995.8144,-9238463.8012 4876084.4703,-9238405.967 4876170.7842,-9238353.3719 4876262.3011,-9238310.5212 4876349.1881,-9238272.2574 4876441.2821,-9238243.4132 4876528.4217,-9238217.1955 4876619.4703,-9238194.011 4876729.7981,-9238179.5847 4876833.2116,-9238176.2718 4876883.2617,-9238170.6514 4876993.9016,-9238152.7083 4877347.106,-9238134.6114 4877738.6967,-9238119.6462 4878039.8727,-9238115.9125 4878116.6714,-9238104.9866 4878334.2119,-9238095.9146 4878510.3946,-9238090.238 4878644.3381,-9238089.0153 4878673.1878,-9238088.1049 4878690.8048,-9238077.6935 4878892.096,-9238069.419 4879038.3436,-9238066.9663 4879080.327,-9238064.6362 4879120.2113,-9238041.4138 4879343.2968,-9238012.9965 4879542.8242,-9237969.5453 4879774.818,-9237933.9058 4879917.0296,-9237862.4951 4880177.4013,-9237794.0085 4880382.2497,-9237724.0367 4880568.1404,-9237628.0198 4880796.2747,-9237445.2224 4881172.004,-9237440.2198 4881181.4069,-9237400.1985 4881256.6306,-9237392.1943 4881271.6754,-9237384.9695 4881285.2895,-9237324.7512 4881401.6888,-9237283.2389 4881480.1285,-9237201.9304 4881644.8741,-9237083.1758 4881876.3847,-9237034.1842 4881972.4406,-9237026.4991 4881987.5084,-9237012.1362 4882014.7402,-9236994.7842 4882047.6404,-9236912.8831 4882202.7767,-9236876.6887 4882276.1628,-9236828.4263 4882391.4124,-9236757.9902 4882556.5887,-9236705.5069 4882692.4266,-9236704.8247 4882694.424,-9236618.1946 4882948.1054,-9236573.9406 4883086.4215,-9236543.5803 4883202.0881,-9236519.3463 4883304.9622,-9236503.1425 4883385.2165,-9236481.5292 4883490.6968,-9236478.9238 4883507.2617,-9236471.4336 4883554.8862,-9236453.641 4883666.9585,-9236435.4753 4883807.7387,-9236420.5865 4883958.2216,-9236406.9344 4884178.7702,-9236404.8541 4884212.378,-9236375.0287 4884681.8243,-9236337.875 4885474.8489,-9236330.1836 4885570.6629))
"""

pt = wkt.loads(strWkt)

# binary case
# wkb.dumps converts the shapely geometry into WKB format
sql = """
INSERT INTO geomTest VALUES (?,?,geometry::STGeomFromWKB(?,4326))
"""
params = [(1,'center',wkb.dumps(pt))]
cur.executemany(sql,params)
cur.commit()

# string case
sql = """
INSERT INTO geomTest VALUES (?,?,geometry::STGeomFromText(?,4326))
"""
params = [(1,'center',wkt.dumps(pt))]
cur.executemany(sql,params)
cur.commit()

For this example geometry, the binary case yields

'String data, right truncation: length 8130 buffer 8000'

and the string case yields

'String data, right truncation: length 52764 buffer 8000'

In the one row case in C# the insert works as expected (only binary case tested), but I'm not sure if C# statement preparation is closer to fast_executemany=False or fast_executemany=True...

using Microsoft.Data.SqlClient;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using System;

namespace ConsoleApp4
{
    class Program
    {
        static void Main(string[] args)
        {
            using SqlConnection conn = new SqlConnection("Server=localhost;Database=test;Trusted_Connection=Yes;");
            conn.Open();

            string strWkt =
                "MULTILINESTRING ((-9261149.6037 4838312.9676,-9260417.8851 4838751.429,-9260114.2814 4838934.1061,-9259842.8895 4839094.1943,-9259492.0113 4839305.9591,-9258761.6829 4839743.2902,-9258570.0851 4839856.7705,-9258559.3412 4839863.1338,-9258389.8833 4839961.7159,-9258267.5384 4840026.2165,-9258114.2646 4840101.9233,-9257972.4499 4840165.6426,-9257822.3257 4840225.8185,-9257702.8447 4840271.2441,-9257578.9731 4840313.8769,-9257392.6692 4840370.5445,-9257231.0474 4840413.204,-9257055.6474 4840454.8559,-9256848.8158 4840491.7562,-9256723.3732 4840510.9186,-9256713.0754 4840512.1247,-9256587.4414 4840526.837,-9256454.7894 4840539.4125,-9256319.9065 4840548.1315,-9256166.274 4840553.6667,-9255986.1681 4840554.4123,-9255853.3966 4840550.3912,-9255639.5794 4840535.3371,-9255414.7128 4840515.3806,-9255278.8457 4840502.2799,-9255243.8497 4840498.9052,-9255147.4068 4840491.2971,-9255029.1244 4840481.462,-9254779.2608 4840460.733,-9254626.2678 4840452.9629,-9254584.4191 4840453.2599,-9254452.596 4840454.1942,-9254292.9354 4840464.1662,-9254169.1012 4840477.755,-9254015.58 4840499.2922,-9253866.502 4840526.3262,-9253764.2202 4840550.2663,-9253627.8695 4840586.0557,-9253481.6729 4840631.8652,-9253333.3332 4840686.5367,-9252999.95 4840825.9871,-9252767.0154 4840923.2382,-9252763.1332 4840924.859,-9252331.5108 4841105.9801,-9251603.7254 4841412.8827,-9251082.4353 4841632.0817,-9250962.3394 4841682.9673,-9250788.0046 4841756.8312,-9250594.0887 4841838.3173,-9250487.5611 4841889.9086,-9250385.4652 4841945.341,-9250332.8051 4841977.3862,-9250309.1988 4841991.7511,-9250246.1248 4842033.6497,-9250227.5634 4842046.4077,-9250224.1886 4842048.7273,-9250208.8286 4842060.0992,-9250133.734 4842115.6953,-9250066.8773 4842169.2328,-9250003.3331 4842223.8541,-9249947.4809 4842275.659,-9249884.5266 4842336.3599,-9249806.0575 4842427.0388,-9249723.1495 4842526.3171,-9249656.5587 4842616.7891,-9249618.7631 4842672.3655,-9249590.1064 4842717.664,-9249583.2288 4842728.5356,-9249559.6387 4842768.0398,-9249553.9934 4842777.5571,-9249548.6726 4842786.5734,-9249457.5603 4842940.9581,-9249374.7187 4843076.8919,-9249327.5192 4843158.86,-9249316.7403 4843176.846,-9249116.2451 4843511.3911,-9249069.6853 4843591.0574,-9249033.7068 4843652.6181,-9248948.556 4843787.2669,-9248886.1434 4843879.537,-9248802.9348 4843996.206,-9248742.5491 4844078.1807,-9248653.9287 4844189.8538,-9248588.9837 4844269.9884,-9248570.8563 4844290.2616,-9248503.9236 4844365.1168,-9248399.2374 4844477.1649,-9248326.902 4844550.0136,-9248316.5684 4844560.4206,-9248199.5089 4844673.0421,-9248129.9923 4844739.2637,-9247981.8887 4844880.3444,-9247685.7316 4845162.4533,-9247507.5557 4845331.7882,-9247504.5099 4845334.6828,-9247200.9734 4845625.1819,-9247158.5311 4845665.8182,-9246990.2743 4845826.9114,-9246784.0003 4846024.1701,-9246472.7618 4846319.3418,-9246384.9156 4846403.3896,-9246294.0386 4846490.3356,-9246151.2061 4846627.0895,-9245965.4646 4846818.2712,-9245856.9901 4846942.8194,-9245791.0802 4847018.4945,-9245691.5542 4847143.7121,-9245594.6346 4847275.5154,-9245522.2995 4847381.5651,-9245427.8411 4847527.204,-9245349.5988 4847656.1397,-9245337.2436 4847678.355,-9245291.9406 4847759.8113,-9245229.1344 4847882.2682,-9245204.7499 4847930.7466,-9245019.4164 4848295.0792,-9245014.6395 4848304.4694,-9244934.2224 4848464.1697,-9244839.6109 4848652.0547,-9244800.4391 4848728.1721,-9244751.4734 4848823.3194,-9244703.4861 4848916.5645,-9244572.33 4849165.7498,-9244473.4559 4849345.7707,-9244360.8941 4849532.93,-9244234.9586 4849724.7105,-9244193.7422 4849787.4755,-9244080.9486 4849942.7333,-9244015.5852 4850032.7018,-9243924.7445 4850152.1563,-9243853.0779 4850245.0775,-9243850.5261 4850248.4608,-9243844.1464 4850256.9194,-9243816.6802 4850293.3351,-9243805.1442 4850308.7131,-9243739.6226 4850394.0971,-9243604.3156 4850570.5734,-9243436.5822 4850789.6653,-9243406.0758 4850829.5531,-9243291.6747 4850979.1327,-9243253.9603 4851029.1463,-9243153.3869 4851162.5167,-9243001.2788 4851360.8862,-9242897.3348 4851496.4396,-9242699.0248 4851757.6385,-9242637.7776 4851836.0583,-9242635.1207 4851839.4601,-9242617.8509 4851861.5724,-9242373.2326 4852179.7637,-9242105.7979 4852531.4309,-9241813.4879 4852913.4883,-9241602.2136 4853189.6219,-9241389.0832 4853467.8893,-9241345.6917 4853528.6221,-9241276.8191 4853620.972,-9241215.7855 4853717.3337,-9241160.6342 4853814.5119,-9241116.2824 4853905.2432,-9241101.4983 4853935.487,-9241038.654 4854083.1277,-9240985.1539 4854245.6224,-9240954.4039 4854364.7638,-9240927.0535 4854491.5715,-9240891.3411 4854710.0686,-9240748.0405 4855609.6636,-9240669.6804 4856094.1433,-9240597.4899 4856537.5292,-9240594.4957 4856555.9188,-9240551.0375 4856844.4836,-9240549.5877 4856913.5355,-9240551.6902 4856979.6739,-9240557.557 4857039.1121,-9240573.4415 4857108.0658,-9240591.0766 4857164.9547,-9240608.7074 4857212.4811,-9240639.8783 4857276.9528,-9240698.8449 4857385.7453,-9240708.2479 4857404.7415,-9240775.0104 4857539.6154,-9240934.2429 4857856.7989,-9241027.154 4858037.1068,-9241094.7691 4858177.7566,-9241108.2695 4858214.7213,-9241111.7641 4858224.5163,-9241117.3553 4858240.1883,-9241130.1998 4858285.2692,-9241137.3826 4858345.8684,-9241139.2303 4858460.9418,-9241141.3726 4858647.631,-9241138.64 4858693.611,-9241134.5617 4858737.0801,-9241127.805 4858766.0774,-9241110.7329 4858803.7003,-9241066.1824 4858869.3342,-9240987.7162 4858913.349,-9240931.3092 4858941.8465,-9240922.8098 4858946.1403,-9240843.3083 4858985.6009,-9240724.5478 4859043.0139,-9240611.7682 4859087.2213,-9240541.7387 4859106.8691,-9240483.2792 4859119.8644,-9240409.6751 4859131.9326,-9240259.4203 4859152.5374,-9240161.5758 4859161.1933,-9240081.6947 4859164.9133,-9240012.4437 4859165.5295,-9239898.1772 4859161.6574,-9239849.2048 4859156.8658,-9239791.5854 4859148.1829,-9239725.4635 4859136.28,-9239681.5143 4859127.4064,-9239590.481 4859111.171,-9239543.1305 4859104.874,-9239477.5213 4859102.7053,-9239435.7558 4859104.4557,-9239391.0803 4859107.9328,-9239247.5545 4859126.2708,-9239166.3829 4859138.8779,-9239095.7241 4859150.5042,-9238993.9509 4859164.6518,-9238964.2514 4859169.4495,-9238900.8925 4859179.6843,-9238885.7512 4859181.9439,-9238826.3601 4859190.0368,-9238788.7457 4859195.162,-9238656.645 4859214.631,-9238532.0892 4859229.4963,-9238325.3744 4859261.5253,-9238200.132 4859279.8624,-9238127.7259 4859290.4627,-9237925.539 4859320.4331,-9237857.3745 4859349.7272,-9237814.9529 4859368.1349,-9237783.9249 4859386.4798,-9237757.7749 4859404.1863,-9237729.4205 4859428.2364,-9237704.7228 4859451.0412,-9237690.7825 4859467.0947,-9237684.5868 4859474.2295,-9237669.141 4859493.308,-9237650.3833 4859523.8423,-9237636.4756 4859548.2228,-9237619.3894 4859587.1232,-9237611.1445 4859607.5338,-9237608.3642 4859618.0243,-9237599.4119 4859656.0637,-9237595.5727 4859676.1696,-9237590.7802 4859716.1607,-9237588.3085 4859748.6512,-9237584.7171 4859838.319,-9237583.9837 4859849.8307,-9237583.6924 4859858.1392,-9237578.1586 4860016.0029,-9237568.9567 4860164.6302,-9237564.8874 4860233.3401,-9237561.7867 4860320.2874,-9237563.7112 4860398.8958,-9237570.8294 4860463.1128,-9237583.2877 4860540.1523,-9237586.0542 4860558.1001,-9237615.4703 4860749.3387,-9237628.8662 4860836.6009,-9237638.6683 4860900.4517,-9237648.0907 4861005.9224,-9237656.9189 4861156.8168,-9237663.1352 4861217.7594,-9237669.5658 4861280.8039,-9237680.0695 4861383.7772,-9237686.876 4861467.9497,-9237688.8366 4861553.3707,-9237688.5808 4861646.3647,-9237686.6964 4861716.6859,-9237683.1725 4861762.0669,-9237679.6459 4861806.6932,-9237672.2262 4861875.8718,-9237661.6295 4861958.7564,-9237652.9715 4862029.8737,-9237642.8366 4862106.2901,-9237640.7876 4862148.6777,-9237639.968 4862165.6328,-9237639.7717 4862237.8639,-9237640.8432 4862297.5891,-9237645.1748 4862356.4925,-9237652.347 4862425.5886,-9237661.6562 4862487.5484,-9237663.0019 4862496.5072,-9237664.8352 4862508.87,-9237674.0015 4862570.6836,-9237686.2852 4862653.7883,-9237693.3484 4862701.5738,-9237714.3482 4862841.2359,-9237717.233 4862860.421,-9237717.5813 4862862.7592,-9237718.6266 4862869.7737,-9237725.9509 4862918.396,-9237729.4317 4862941.5516,-9237747.0147 4863060.3583,-9237775.0665 4863266.5775,-9237800.2112 4863445.6292,-9237822.5473 4863596.2593,-9237845.0711 4863743.8377,-9237850.0507 4863780.7774,-9237874.8772 4863952.7514,-9237885.0873 4864021.5565,-9237893.4979 4864088.7152,-9237898.9389 4864150.6522,-9237902.7558 4864219.6345,-9237902.4461 4864290.3092,-9237896.899 4864388.4458,-9237892.2211 4864470.4817,-9237889.4923 4864518.3361,-9237879.5234 4864649.0545,-9237864.5824 4864857.1695,-9237861.7401 4864898.5654,-9237851.7915 4865043.4523,-9237851.3652 4865049.6617,-9237831.3454 4865312.0866,-9237813.269 4865526.6185,-9237799.3188 4865727.9365,-9237792.7027 4865838.1177,-9237786.609 4866019.1522,-9237785.6983 4866046.2036,-9237783.6885 4866362.9431,-9237784.7819 4866394.8271,-9237783.8257 4866474.7439,-9237783.5497 4866595.3267,-9237787.3957 4866712.1925,-9237792.7702 4866770.8383,-9237802.2731 4866826.69,-9237815.9192 4866882.9817,-9237833.262 4866942.026,-9237853.8067 4866997.357,-9237872.9067 4867040.684,-9237891.9771 4867078.4667,-9237914.7394 4867118.0778,-9237940.2731 4867159.5222,-9237969.4825 4867200.0215,-9238017.2029 4867259.3652,-9238043.597 4867289.7171,-9238104.1911 4867346.6809,-9238128.9398 4867367.9837,-9238200.761 4867418.2343,-9238263.5832 4867461.325,-9238361.5036 4867526.8633,-9238449.7303 4867586.9105,-9238508.8776 4867630.9445,-9238562.9715 4867676.3923,-9238609.2329 4867720.4952,-9238642.3625 4867753.3692,-9238666.7595 4867782.7371,-9238702.7741 4867826.256,-9238733.8434 4867869.0573,-9238770.5102 4867925.6894,-9238802.0994 4867979.5769,-9238828.6174 4868031.6442,-9238856.2898 4868098.2094,-9238857.0804 4868100.1113,-9238879.9502 4868161.2041,-9238896.3444 4868213.3277,-9238913.2708 4868278.8497,-9238923.2706 4868340.2499,-9238931.6748 4868397.1769,-9238936.2301 4868437.6777,-9238947.461 4868555.91,-9238955.4488 4868670.0024,-9238960.7569 4868799.3611,-9238963.5327 4868886.2203,-9238964.1461 4868990.4399,-9238962.8828 4869096.2708,-9238961.56 4869148.9587,-9238956.2202 4869232.8439,-9238949.3344 4869320.6855,-9238941.8987 4869391.8932,-9238935.8234 4869444.2959,-9238934.1514 4869453.7021,-9238917.4319 4869547.7656,-9238894.65 4869677.7416,-9238875.9362 4869788.7687,-9238869.8189 4869848.8814,-9238865.1217 4869915.918,-9238863.5876 4869972.3088,-9238862.9994 4870033.3166,-9238865.1913 4870089.4755,-9238869.3991 4870140.2854,-9238874.5008 4870201.0181,-9238885.6234 4870298.9348,-9238900.7874 4870378.3447,-9238915.4181 4870444.3555,-9238931.443 4870504.5991,-9238950.1542 4870567.104,-9238971.6937 4870633.5388,-9238992.1514 4870692.8713,-9239022.6483 4870775.1498,-9239047.037 4870857.2857,-9239077.5693 4870968.5061,-9239097.3727 4871053.9045,-9239111.5731 4871125.005,-9239124.447 4871205.8204,-9239134.1424 4871294.9731,-9239141.0845 4871385.5285,-9239146.1672 4871472.8605,-9239171.1446 4871914.1529,-9239193.4009 4872320.3479,-9239202.8593 4872491.7951,-9239208.5709 4872609.6425,-9239212.2089 4872677.3639,-9239216.6722 4872752.8899,-9239225.3579 4872899.8611,-9239227.6346 4872937.136,-9239235.2234 4873061.387,-9239261.6422 4873554.0474,-9239273.6758 4873774.7387,-9239286.4065 4873996.3558,-9239304.33 4874324.9825,-9239311.593 4874445.3076,-9239314.0245 4874538.7689,-9239311.9405 4874620.6189,-9239307.8897 4874679.5923,-9239303.5394 4874726.0842,-9239294.4311 4874786.7053,-9239279.8379 4874860.4743,-9239272.9252 4874895.4176,-9239250.8269 4874981.3765,-9239226.5937 4875055.722,-9239201.3858 4875120.1333,-9239176.8475 4875180.838,-9239129.9021 4875270.8047,-9239076.51 4875361.2673,-9239024.0548 4875437.5811,-9238960.0184 4875519.6759,-9238883.6379 4875607.7247,-9238745.7163 4875758.8898,-9238608.7604 4875908.4151,-9238531.718 4875995.8144,-9238463.8012 4876084.4703,-9238405.967 4876170.7842,-9238353.3719 4876262.3011,-9238310.5212 4876349.1881,-9238272.2574 4876441.2821,-9238243.4132 4876528.4217,-9238217.1955 4876619.4703,-9238194.011 4876729.7981,-9238179.5847 4876833.2116,-9238176.2718 4876883.2617,-9238170.6514 4876993.9016,-9238152.7083 4877347.106,-9238134.6114 4877738.6967,-9238119.6462 4878039.8727,-9238115.9125 4878116.6714,-9238104.9866 4878334.2119,-9238095.9146 4878510.3946,-9238090.238 4878644.3381,-9238089.0153 4878673.1878,-9238088.1049 4878690.8048,-9238077.6935 4878892.096,-9238069.419 4879038.3436,-9238066.9663 4879080.327,-9238064.6362 4879120.2113,-9238041.4138 4879343.2968,-9238012.9965 4879542.8242,-9237969.5453 4879774.818,-9237933.9058 4879917.0296,-9237862.4951 4880177.4013,-9237794.0085 4880382.2497,-9237724.0367 4880568.1404,-9237628.0198 4880796.2747,-9237445.2224 4881172.004,-9237440.2198 4881181.4069,-9237400.1985 4881256.6306,-9237392.1943 4881271.6754,-9237384.9695 4881285.2895,-9237324.7512 4881401.6888,-9237283.2389 4881480.1285,-9237201.9304 4881644.8741,-9237083.1758 4881876.3847,-9237034.1842 4881972.4406,-9237026.4991 4881987.5084,-9237012.1362 4882014.7402,-9236994.7842 4882047.6404,-9236912.8831 4882202.7767,-9236876.6887 4882276.1628,-9236828.4263 4882391.4124,-9236757.9902 4882556.5887,-9236705.5069 4882692.4266,-9236704.8247 4882694.424,-9236618.1946 4882948.1054,-9236573.9406 4883086.4215,-9236543.5803 4883202.0881,-9236519.3463 4883304.9622,-9236503.1425 4883385.2165,-9236481.5292 4883490.6968,-9236478.9238 4883507.2617,-9236471.4336 4883554.8862,-9236453.641 4883666.9585,-9236435.4753 4883807.7387,-9236420.5865 4883958.2216,-9236406.9344 4884178.7702,-9236404.8541 4884212.378,-9236375.0287 4884681.8243,-9236337.875 4885474.8489,-9236330.1836 4885570.6629))";
            WKTReader rdrWKT = new WKTReader();
            Geometry pt = rdrWKT.Read(strWkt);
            WKBWriter wtrWKB = new WKBWriter();
            byte[] wkb = wtrWKB.Write(pt);

            // one row with SqlCommand
            using SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO geomTest VALUES (@objid,@attr,geometry::STGeomFromWKB(@wkb,4326))";
            cmd.Parameters.AddWithValue("@objId", 2);
            cmd.Parameters.AddWithValue("@attr", "center");
            cmd.Parameters.AddWithValue("@wkb", wkb);
            cmd.ExecuteNonQuery();

        }
    }
}
v-makouz commented 3 years ago

Can you post an ODBC trace? Also is this related to https://github.com/mkleehammer/pyodbc/issues/867?

gordthompson commented 3 years ago

Also is this related to #867?

@v-makouz - Yes. See

https://github.com/mkleehammer/pyodbc/issues/867#issuecomment-777072597

ww2406 commented 3 years ago

Here they are:

Windows: SQL.LOG OS X: odbc.txt

(binary case only)

gordthompson commented 3 years ago

Solution: #490