search

mithrandyr / SimplySql

PowerShell module for querying various SQL databases
MIT License
202 stars 30 forks source link

Incorrect order of parameters in Oracle Update Command #9

Closed KES48 closed 6 years ago

KES48 commented 6 years ago

Dear sirs,

we tried to use your module for an oracle DB.

We used the update command with 8 oracle parameters as hash value. The order of the parameters seems to be incorrect, perhaps randomly. Maybe the hash parameter should be an [ordered}[hashtable] type?

Thank you very much for your help! The module is great :-)

kind regards Klaus.

mithrandyr commented 6 years ago

@Abrechnung1 -- Could you give me an example statement where this is happening? perhaps a bit of powershell code to reproduce the issue? Thanks!

KES48 commented 6 years ago

Good morning Mithrandyr,

a bit of code is here :-)

foreach ($Feld in $csv) { $sqlInsert=('INSERT INTO KVNET.DIABETES_TMP(LANR, BSNR,KT, IK,NACHNAME,VORNAME,GEB,EGKVERSICHERTENNUMMER) VALUES (:LANR,:BSNR,:KT,:IK,:NACHNAME,:VORNAME,:GEB,:EGKVERSICHERTENNUMMER)')

hier

$hash = [ordered]@{LANR=$Feld.LANR;BSNR=1;KT=$Feld.KTNAME;IK=$Feld.IK;NACHNAME=$feld.NACHNAME;VORNAME=$feld.VORNAME;GEB=$feld.geb;EGKVERSICHERTENNUMMER=$feld.EGKVERSICHERTENNUMMER} Invoke-SqlUpdate -Query $sqlInsert -Parameters $hash -ConnectionName SRConnection -ErrorAction Stop }

The DB table is:

CREATE TABLE DIABETES_TMP ( LANR VARCHAR2(9 CHAR) NOT NULL, BSNR VARCHAR2(9 CHAR) NOT NULL, KT VARCHAR2(45 CHAR) NOT NULL, IK VARCHAR2(9 CHAR) NOT NULL, NACHNAME VARCHAR2(45 CHAR) NOT NULL, VORNAME VARCHAR2(45 CHAR) NOT NULL, GEB VARCHAR2(10 CHAR) NOT NULL, EGKVERSICHERTENNUMMER VARCHAR2(10 CHAR) NOT NULL )

inserting values results in errors like "you can't insert value X into column Y because it can not take values of length Z" Obviously the records are OK to fit in the columns. But the Invoke statement seems to insert the values not in the defined order. It probably mixes the columns in a "random" order.

I hope this helps to identify the problem ...

Have a good day kind regards Klaus

mithrandyr commented 6 years ago

@Abrechnung1, I closed out this issue because I was able to reproduce it and fix it (version 1.5.2) (prior to you sending me an example, but thanks for the example -- it confirmed the steps that I found to reproduce the issue). Can you please test and make sure that your issue is resolved and let me know? Thanks.