Oracle 9i <-> VB.NET en data types.

[H!GHGuY]

even situatie schetsen: (dit is NIET zomaar een huiswerktopic)

we moeten voor de lessen databanken en GUI's een kookboek in mekaar flansen.
dit gebeurt met Oracle 9i en VB.NET. de gebruikte datatoegang werkt via OLEDB.

we hebben ons ontwerp opgesteld voor de DB, en triggers/procedures geschreven.
dit alles werkt lekker, maar nu komen we even met de GUI in problemen.

even 1 tabel om als voorbeeld te gebruiken:

SQL:

create table Ingredienten
(
    ID             int              not null            ,   
    Naam               varchar(40)  not null                 ,
    MassaDichtheid    float            default 1        not null,
    MaatID             int          not null                ,   
    VoedselGroepID    int              not null             ,   
    Datum             varchar(20)   not null            ,   
    Actief           int              not null                 ,    
    check (Actief IN (0,1))                             ,
    primary key (Naam, Datum, Actief)                   ,
    foreign key (MaatID) references Volumes(ID)         ,
    foreign key (VoedselGroepID) references VoedselGroepen(ID)
);

datum en actief dienen voor versioning.

1) die Datum was eerste van het 'timestamp' type. allemaal lekker en wel, maar bij het inlezen van de tabel met een dataadapter crashte het spul erop.
--> oplossing: procedure getDatum() die een string maakt van de SYSDATE op de server en die in een varchar stopt, gemakkelijk inleesbaar en converteerbaar in VB.NET
we moeten nu wel voor die versioning queries schrijven die die handel verwerken, maar met TO_DATE() trekken we onze slag wel denk ik...

2) het 2de probleem is ook weer datatype-gerelateerd.
wanneer ik een SELECT/DELETE-query uitvoer werkt alles lekker. wanneer ik echter een UPDATE doe loopt het mis.

Visual Basic .NET:

'DE UPDATE QUERY
Private Const update_ingredient As String = "UPDATE Ingredienten SET Naam=:naam, MassaDichtheid=:massadichtheid,MaatID=:maatid,VoedselGroepID=:voedselgroepid WHERE ID=:id"
'--------------------------------------------------------
'VOLGENDE CODE IS PUUR TER VERDUIDELIJKING VAN MIJN WERKWIJZE
        frmMain.dSource.openConnection()
        For Each rij As DataRow In ingredienten.Rows
            Try
                If Convert.ToInt32(rij("ID")) = -1 Then 'insert
                    Dim cmd As IDbCommand = frmMain.dSource.getCommand(insert_ingredient)

                    Dim p As IDataParameter = frmMain.dSource.getParameter("massadichtheid", Convert.ToDouble(rij("Massadichtheid")))
                    cmd.Parameters.Add(p)

                    p = frmMain.dSource.getParameter("maatid", Convert.ToInt32(rij("MaatID")))
                    cmd.Parameters.Add(p)

                    p = frmMain.dSource.getParameter("voedselgroepid", Convert.ToInt32(rij("VoedselGroepID")))
                    cmd.Parameters.Add(p)

                    p = frmMain.dSource.getParameter("naam", Convert.ToString(rij("Naam")))
                    cmd.Parameters.Add(p)

                    Try
                        frmMain.dSource.executeNonQuery(cmd)
                    Catch ex As Exception
                        'ORA-20000 IS EEN CUSTOM TRIGGER-EXCEPTION DIE AANDUIDT DAT ALLES GELUKT IS
                        If ex.Message.StartsWith("ORA-20000") Then
                            cmd = frmMain.dSource.getCommand(insert_getID)

                            cmd.Parameters.Add(p)

                            rij("ID") = frmMain.dSource.executeScalar(cmd)
                        Else
                            MessageBox.Show(ex.Message)
                        End If
                    End Try
                    
                Else                                    'update
                        Dim cmd As IDbCommand = frmMain.dSource.getCommand(update_ingredient)

                        Dim p As IDataParameter = frmMain.dSource.getParameter("naam", Convert.ToString(rij("Naam")))
                        cmd.Parameters.Add(p)

                        p = frmMain.dSource.getParameter("massadichtheid", Convert.ToDouble(rij("Massadichtheid")))
                        cmd.Parameters.Add(p)

                        p = frmMain.dSource.getParameter("maatid", Convert.ToInt32(rij("MaatID")))
                        cmd.Parameters.Add(p)

                        p = frmMain.dSource.getParameter("voedselgroepid", Convert.ToInt32(rij("VoedselGroepID")))
                        cmd.Parameters.Add(p)

                        p = frmMain.dSource.getParameter("id", Convert.ToInt32(rij("ID")))
                        cmd.Parameters.Add(p)

                        frmMain.dSource.executeNonQuery(cmd)
                End If
            Catch ex As Exception
                If ex.Message.StartsWith("ORA-20000") Then
                    'ALLES OK
                Else
                    MessageBox.Show(ex.Message)
                End If
            End Try
        Next
        frmMain.dSource.closeConnection()
    End Sub
'-------------------------------------------------

wanneer ik hier dan voor de massadichtheid '1,2' (double) aan meegeef wil die het plots niet doen. ik krijg een error ala invalid number. ik denk dus aan hetzelfde als van die timestamp: incompatibele data types...
ik heb reeds vanalles geprobeerd:
(volgens mij ligt het probleem op die massadichtheid die hij niet wel converteren naar een float type in oracle, hoewel die beide ANSI compatible zouden moeten zijn?)
-->convert.tosingle()
-->in m'n getParameter functie:

Visual Basic .NET:

Return New OleDbParameter(":" + parameterName, value)

vervangen door

Visual Basic .NET:

Return New OleDbParameter(":" + paramterName,"'" + value.toString() + "'")

en zelfs gewoon "'" + value + "'" hoewel dit hetzelfde is
-->google
-->got search
en nog wat andere normale dingetjes...

PS: ik bedenk net: zou het die Naam niet zijn??? want die heeft single quotes nodig?

[whoami]

Als je een parameter toevoegt aan je OleDbCommand, geef dan niet enkel de naam en de waarde voor die parameter mee, maar geef ook het datatype mee dat hij moet gebruiken.

Voor versioning zou ik trouwens geen datetime column gebruiken, maar een GUID.

Je moet zelf geen quotes rond die naam gaan zetten, want dat gebeurt automatisch als je parametrized queries gebruikt, nu zal je die naam met quotes in de DB zelf ook krijgen, als je zelf voor die quotes zorgt.

Volgens mij krijg je die 'Invalid number' exception omdat je 1,2 ingeeft (komma, dit is de decimal separator met jouw regional settings), terwijl de DB daar toch een . verwacht. Echter, als je parametrized queries gebruikt, zou dat eigenlijk geen verschil mogen maken.

Probeer eerst eens wat het geeft als je het datatype meegeeft met je parameter.

[ Voor 60% gewijzigd door whoami op 23-12-2004 22:19 ]

[EfBe]

Waarom gebruiken jullie geen native oracle provider voor .NET? Er zit 1 in .NET en ODP.NET is gratis te downloaden... OleDb is veelal maar brak mbt connectie tot een database in .NET, en het is lang niet altijd duidelijk welk type je moet gebruiken voor welke parameter.

[H!GHGuY]

EfBe schreef op vrijdag 24 december 2004 @ 10:37:
Waarom gebruiken jullie geen native oracle provider voor .NET? Er zit 1 in .NET en ODP.NET is gratis te downloaden... OleDb is veelal maar brak mbt connectie tot een database in .NET, en het is lang niet altijd duidelijk welk type je moet gebruiken voor welke parameter.

ik had eerst binnen m'n datalaag de oracle data provider gebruikt, maar die kon niet verbinden met de databank op de PC's in school omdat er in een config bestand van hen (op alle PC's) een instelling verkeerd stond.

whoami schreef op donderdag 23 december 2004 @ 22:15:
Als je een parameter toevoegt aan je OleDbCommand, geef dan niet enkel de naam en de waarde voor die parameter mee, maar geef ook het datatype mee dat hij moet gebruiken.

Voor versioning zou ik trouwens geen datetime column gebruiken, maar een GUID.

Je moet zelf geen quotes rond die naam gaan zetten, want dat gebeurt automatisch als je parametrized queries gebruikt, nu zal je die naam met quotes in de DB zelf ook krijgen, als je zelf voor die quotes zorgt.

Volgens mij krijg je die 'Invalid number' exception omdat je 1,2 ingeeft (komma, dit is de decimal separator met jouw regional settings), terwijl de DB daar toch een . verwacht. Echter, als je parametrized queries gebruikt, zou dat eigenlijk geen verschil mogen maken.

Probeer eerst eens wat het geeft als je het datatype meegeeft met je parameter.

wat die komma betreft: dit is de windows instelling die gebruikt wordt voor een double, dus de interne voorstelling zou hetzelfde moeten zijn. ik had toen ook al geprobeerd om een integer (bvb 1 mee te geven) maar ook dat werkte niet.

wat die versioning betreft: ik heb die GUID niet uitgepluisd maar de versioning moet op datum gebeuren. dit was een flexibele snelle oplossing, en zeker wegens het tijdsgebrek voor de implementatie etc de beste methode. op dit moment is het ontwerp van de DB al ingediend, dus die kunnen we neit meer wijzigen.

ik zal even zoeken hoe'k het datatype kan meegeven...

[whoami]

Je kunt het datatype gewoon meegeven aan de parameter.
Een OleDbParamater heeft een overloaded constructor. Enkele van die constructors nemen het datatype als argument.
Je kan het datatype ook als argument meegeven met de AddParameter method van het Command object:

code:

myCommand.Parameters.Add ("myParam", OleDbType.Int);
myCommand.Parameters["myParam"].Value = ...

bv.

of:

code:

OleDbParameter p = new OleDbParameter ("myParam", OleDbType.Int);
myCommand.Parameters.Add (p);
myCommand.Parameters["myParam"].Value = ...

[H!GHGuY]

de code was al geschreven voor de vorige post en dit was het resultaat:
(ik gebruik een interface met daarvan afgeleide classes voor datatoegang tot verschillende types DB's, dit omdat ik thuis geen oracle draai, maar wel eens SQL Express server.)

Visual Basic .NET:

        Public Function getParameter(ByVal parameterName As String, ByVal value As Object) As System.Data.IDataParameter Implements IDataSource.getParameter
            'Return New OleDbParameter(":" + parameterName, value)

            Dim ret As OleDbParameter
            Dim type As Type = value.GetType
            If (type Is System.Type.GetType("System.Double")) Then
                ret = New OleDbParameter(":" + parameterName, OleDbType.Double)
            ElseIf (type Is System.Type.GetType("System.String")) Then
                ret = New OleDbParameter(":" + parameterName, OleDbType.VarChar)
            ElseIf (type Is System.Type.GetType("System.Integer")) Then
                ret = New OleDbParameter(":" + parameterName, OleDbType.Integer)
            ElseIf (type Is System.Type.GetType("System.DateTime")) Then
                ret = New OleDbParameter(":" + parameterName, OleDbType.Date)
             Else
                Return New OleDbParameter(":" + parameterName, value)
            End If
            ret.Value = value
        End Function

dit zou dan moeten werken?

[H!GHGuY]

ik heb op dit moment dan toch maar oracle geinstalleerd op een oude PC die nu dus als servertje draait.

ik moest op m'n PC ook nog de OLEDB oracle provider installeren.

ik krijg echter geen verbinding:
ORA-12560: TNS: fout in protocoladapter.

ik heb reeds gegoogled en dit is weer zo'n error die ALLES kan betekenen
ik heb reeds logging aangezet, en volgens mij probeert ie toch op de eigen PC te zoeken:

code:

connectionstring = "Provider=OraOLEDB.Oracle;Host=195.168.123.105;Port=1521;SID=III;User Id=system;Password=oracle"

terwijl in de log staat:

code:

nigini: Count in NI global area now: 1
nigini: Count in NI global area now: 1
nrigbni: Unable to get data from navigation file tnsnav.ora
niotns: niotns: setting up interrupt handler...
niotns: Not trying to enable dead connection detection.
niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\Documents and Settings\H!GHGuY\Mijn documenten\School\DB\ProjectKookBoek\KookBoek\bin\Kookboek.exe)(HOST=STIJN)(USER=H!GHGuY))))
nscall: connecting...
nsopen: opening transport...
nserror: nsres: id=0, op=65, ns=12560, ns2=0; nt[0]=530, nt[1]=126, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
nsopen: unable to open transport

hier staat dus blijkbaar foute info
is dit het tsnames.ora bestand dat'k moet in prutsen?

zelfs met de software die bij de oracle OLEDB provider zit, kan ik geen verbinding maken.
(op de server zelf, met SQL PLus werken is geen probleem, en let diezelfde software naar de eigen PC verbinden is ook geen probleem)
ik heb al enorm zitten klooien met de software maar kan niet tot een werkende config komen.

ik ben nu aan het prutsen met een tsnames.ora bestand maar of dat zal werken?

[EfBe]

Wat is die SID=III ?

Bij elke client van Oracle (en die heb je op je pc staan neem ik aan) zit een Net configuration assistent en een net manager. met die 2 kun je je client configureren om een TSN name te herkennen. Die geef je dan op in je connectionstring.

Maar ik blijf erbij, oledb is crap icm .NET. Gebruik gewoon ODP.NET of de ms oracle provider.

[ Voor 16% gewijzigd door EfBe op 25-12-2004 12:05 ]

[H!GHGuY]

die SID is een unieke ID van de DB. die heb'k op de server zo ingesteld dus dat zou moeten werken.

en wat die configprogramma's betreft: die heb'k al geprobeerd, en nix werkt...

als ik het programma vanop de server laat lopen, krijg ik de error "request failed"

en wat die ODP betreft: ik kan het niet maken een programma te schrijven dat niet werkt wanneer het gequoteerd moet worden he ik heb een datalaag voor ODP, maar die werkt gewoon niet op de school-PC's

[ Voor 31% gewijzigd door H!GHGuY op 25-12-2004 12:43 ]

[H!GHGuY]

OK EINDELIJK...

ik heb de DB aanspreekbaar gekregen vanop mijn PC !!
ik kan select queries etc uitvoeren zoals daarvoor.

dit brent me terug bij mijn oorspronkelijk probleem, nl die *grmbl* parameters

ORA-01722: Ongeldig Getal
ook met de laatste nieuwe code heb'k die error...
wederom googlen...

[H!GHGuY]

ok ik heb het probleem dus herleid tot iets wat me onlogisch lijkt:

de volgorde waarop je parameters toevoegt moet dezelfde zijn als de volgorde zoals ze in je query staan.

ik voegde parameter naam als laatste toe, waardoor ie em als voedselgroepID ging beschouwen.
ik kan nu dus updaten/inserten etc.

alleen werkt m'n trigger nog niet 100%
ff verder zoeken

[gorgi_19]

HIGHGuY,

Kan je voortaan de edit knop gebruiken als je nog wat aan een eerdere post wil toevoegen Een monoloog zien we liever niet terug @ Got

[whoami]

HIGHGuY schreef op zaterdag 25 december 2004 @ 15:11:
ok ik heb het probleem dus herleid tot iets wat me onlogisch lijkt:

de volgorde waarop je parameters toevoegt moet dezelfde zijn als de volgorde zoals ze in je query staan.

Het is niet onlogisch, als je de OleDb provider gebruikt, dan gebruikt die provider niet de namen van de parameters, maar moet je idd ze in de 'juiste volgorde toevoegen'.
klik

Opgepast:

Als je gebruik maakt van de OleDbClient, moet je je parameters in dezelfde volgorde 'adden' zoals je ze in je SQL statement gedefinieerd hebt. Dit komt omdat OleDb geen rekening gaat gaan houden met de parameternamen.

alleen werkt m'n trigger nog niet 100%
ff verder zoeken

Wat werkt er dan niet ?

[H!GHGuY]

dit zijn m'n triggers voor de tabel Ingredienten.

code:

CREATE OR REPLACE TRIGGER Ingredienten_ID
  BEFORE INSERT ON Ingredienten
  FOR EACH ROW
BEGIN
  IF :new.ID IS NULL THEN
    SELECT SEQ_Ingredienten.NEXTVAL INTO :new.ID FROM DUAL;
  END IF;
  :new.Actief:=1;
  SELECT getDatum() INTO :new.Datum FROM DUAL;
END;
/
---------------------- DEZE TRIGGER WERKT NOG NEIT NAAR BEHOREN
CREATE OR REPLACE TRIGGER Ingredienten_UPDATE
  BEFORE UPDATE ON Ingredienten
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  alles_ok EXCEPTION;
BEGIN
  DBMS_OUTPUT.PUT_LINE('w00t');
  IF (:new.Actief IS NULL) THEN
    UPDATE Ingredienten SET Actief=0 WHERE ID=:old.ID;
    INSERT INTO Ingredienten (ID, Naam, MassaDichtheid, MaatID, VoedselGroepId)
      VALUES (:old.ID,:new.Naam,:new.Massadichtheid, :new.MaatID, :new.VoedselGroepId);
     -------------------------------- HIER REKEN IK OP DE Ingredienten_ID TRIGGER om
     -------------------------------- Datum en Actief In te Vullen
    COMMIT;
    RAISE alles_ok;
    DBMS_OUTPUT.PUT_LINE('w00t');
  END IF;
EXCEPTION
  WHEN alles_ok THEN
    RAISE_APPLICATION_ERROR('-20000','Update Uitgevoerd');
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR('-20001','Kon niet Updaten');
END;
/

bij een update krijg ik m'n custom error 'kon niet updaten'

kan dit aan recursiviteit liggen?
dit was ook 1 van m'n vragen aan de docenten: als ik binnen m'n update een insert en/of een update aanroep, worden de triggers voor update/insert dan NOG eens opgeroepen of wordt dit rechtstreeks uitgevoerd zonder triggers? hier wisten ze natuurlijk geen antwoord op

het is nu volop blokvakantie, dus studeren is op dit moment eventjes belangrijker, maar dit is het eerste wat'k ga proberen: de velden van Actief en Datum ook invullen in de insert binnen de update-trigger.