Quantcast

EF handling - as string parameter

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

EF handling - as string parameter

Nikolaus Kern

Hello,

 

I am using following query to get all workitems for a specific organisation unit:

 

ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') ORDER BY PRODUKTIONSAUFTRAG_ID";

tmpPPA = this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery).ToList();

 

This works fine, unless the parameter contains a -. In this case I receive an exception "arithmetic exception, numeric overflow, or string truncation\r\nstring right truncation“

 

In the case I concatenate the sql query it works fine:

 

ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND AUSLASTUNG_GEHOERT_ZU = '" + this.CurrentHalle + "' ORDER BY PRODUKTIONSAUFTRAG_ID";

tmpPPA = this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery, this.CurrentHalle).ToList();

 

Its EF 6.13 and Firebird .net Provider 4.10 (I am aware of the newer versions but need to migration a big number of assemblies).

 

I would like to understand if there is a way to resolve this problem in a more elegant way.

 

Thanks

 

Niko


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Jiří Činčura-2
What parameter? I don't see any parameter in the query.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Nikolaus Kern
Hi,

I just saw that the query and the execution do not match correct. This is the query with the parameter that creates an exception if the parameter is "Halle 6 - Schlosserei".

ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND AUSLASTUNG_GEHOERT_ZU = {0} ORDER BY PRODUKTIONSAUFTRAG_ID";
tmpPPA = this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery, this.CurrentHalle).ToList();

Niko

-----Ursprüngliche Nachricht-----
Von: Jiří Činčura [mailto:[hidden email]]
Gesendet: Dienstag, 2. Mai 2017 09:43
An: [hidden email]
Betreff: Re: [Firebird-net-provider] EF handling - as string parameter

What parameter? I don't see any parameter in the query.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Jiří Činčura-2
You should use @p0 instead of {0}
(https://msdn.microsoft.com/en-us/library/system.data.entity.dbset.sqlquery%28v=vs.113%29.aspx).
Optionally you can also specify the Size in FbParameter.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

On Tue, May 2, 2017, at 09:58, Nikolaus Kern wrote:

> Hi,
>
> I just saw that the query and the execution do not match correct. This is
> the query with the parameter that creates an exception if the parameter
> is "Halle 6 - Schlosserei".
>
> ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE
> PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND
> AUSLASTUNG_GEHOERT_ZU = {0} ORDER BY PRODUKTIONSAUFTRAG_ID";
> tmpPPA = this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery,
> this.CurrentHalle).ToList();
>
> Niko
>
> -----Ursprüngliche Nachricht-----
> Von: Jiří Činčura [mailto:[hidden email]]
> Gesendet: Dienstag, 2. Mai 2017 09:43
> An: [hidden email]
> Betreff: Re: [Firebird-net-provider] EF handling - as string parameter
>
> What parameter? I don't see any parameter in the query.
>
> --
> Mgr. Jiří Činčura
> https://www.tabsoverspaces.com/
>
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Nikolaus Kern
Hello Jiri,

thanks for the response.

I guess I am missing something here. The code below produces the same error as {0}.

ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND AUSLASTUNG_GEHOERT_ZU = @p0 ORDER BY PRODUKTIONSAUFTRAG_ID";
tmpPPA = this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery, new object[]{this.CurrentHalle}).ToList();

If I copy the query to the SQL tool and execute the query it works fine (even when it does not return a record, which is also the case in other working queries).
SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND AUSLASTUNG_GEHOERT_ZU = 'Halle 6 - Schlosserei' ORDER BY PRODUKTIONSAUFTRAG_ID

Do you have an idea whats missing?

Thanks

Niko

-----Ursprüngliche Nachricht-----
Von: Jiří Činčura [mailto:[hidden email]]
Gesendet: Dienstag, 2. Mai 2017 10:02
An: [hidden email]
Betreff: Re: [Firebird-net-provider] EF handling - as string parameter

You should use @p0 instead of {0}
(https://msdn.microsoft.com/en-us/library/system.data.entity.dbset.sqlquery%28v=vs.113%29.aspx).
Optionally you can also specify the Size in FbParameter.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

On Tue, May 2, 2017, at 09:58, Nikolaus Kern wrote:

> Hi,
>
> I just saw that the query and the execution do not match correct. This
> is the query with the parameter that creates an exception if the
> parameter is "Halle 6 - Schlosserei".
>
> ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE
> PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND
> AUSLASTUNG_GEHOERT_ZU = {0} ORDER BY PRODUKTIONSAUFTRAG_ID"; tmpPPA =
> this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery,
> this.CurrentHalle).ToList();
>
> Niko
>
> -----Ursprüngliche Nachricht-----
> Von: Jiří Činčura [mailto:[hidden email]]
> Gesendet: Dienstag, 2. Mai 2017 09:43
> An: [hidden email]
> Betreff: Re: [Firebird-net-provider] EF handling - as string parameter
>
> What parameter? I don't see any parameter in the query.
>
> --
> Mgr. Jiří Činčura
> https://www.tabsoverspaces.com/
>
>
> ----------------------------------------------------------------------
> -------- Check out the vibrant tech community on one of the world's
> most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
>
> ----------------------------------------------------------------------
> -------- Check out the vibrant tech community on one of the world's
> most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Jiří Činčura-2
What's the AUSLASTUNG_GEHOERT_ZU's datatype?

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

On Tue, May 2, 2017, at 11:55, Nikolaus Kern wrote:

> Hello Jiri,
>
> thanks for the response.
>
> I guess I am missing something here. The code below produces the same
> error as {0}.
>
> ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE
> PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND
> AUSLASTUNG_GEHOERT_ZU = @p0 ORDER BY PRODUKTIONSAUFTRAG_ID";
> tmpPPA = this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery, new
> object[]{this.CurrentHalle}).ToList();
>
> If I copy the query to the SQL tool and execute the query it works fine
> (even when it does not return a record, which is also the case in other
> working queries).
> SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE PRODUKTIONSSTATUS NOT IN
> ('Abgeschlossen','Storno') AND AUSLASTUNG_GEHOERT_ZU = 'Halle 6 -
> Schlosserei' ORDER BY PRODUKTIONSAUFTRAG_ID
>
> Do you have an idea whats missing?
>
> Thanks
>
> Niko
>
> -----Ursprüngliche Nachricht-----
> Von: Jiří Činčura [mailto:[hidden email]]
> Gesendet: Dienstag, 2. Mai 2017 10:02
> An: [hidden email]
> Betreff: Re: [Firebird-net-provider] EF handling - as string parameter
>
> You should use @p0 instead of {0}
> (https://msdn.microsoft.com/en-us/library/system.data.entity.dbset.sqlquery%28v=vs.113%29.aspx).
> Optionally you can also specify the Size in FbParameter.
>
> --
> Mgr. Jiří Činčura
> https://www.tabsoverspaces.com/
>
> On Tue, May 2, 2017, at 09:58, Nikolaus Kern wrote:
> > Hi,
> >
> > I just saw that the query and the execution do not match correct. This
> > is the query with the parameter that creates an exception if the
> > parameter is "Halle 6 - Schlosserei".
> >
> > ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE
> > PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND
> > AUSLASTUNG_GEHOERT_ZU = {0} ORDER BY PRODUKTIONSAUFTRAG_ID"; tmpPPA =
> > this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery,
> > this.CurrentHalle).ToList();
> >
> > Niko
> >
> > -----Ursprüngliche Nachricht-----
> > Von: Jiří Činčura [mailto:[hidden email]]
> > Gesendet: Dienstag, 2. Mai 2017 09:43
> > An: [hidden email]
> > Betreff: Re: [Firebird-net-provider] EF handling - as string parameter
> >
> > What parameter? I don't see any parameter in the query.
> >
> > --
> > Mgr. Jiří Činčura
> > https://www.tabsoverspaces.com/
> >
> >
> > ----------------------------------------------------------------------
> > -------- Check out the vibrant tech community on one of the world's
> > most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> > _______________________________________________
> > Firebird-net-provider mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
> >
> >
> > ----------------------------------------------------------------------
> > -------- Check out the vibrant tech community on one of the world's
> > most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> > _______________________________________________
> > Firebird-net-provider mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most engaging
> tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Nikolaus Kern
Hello Jiri,

its my fault: AUSLASTUNG_GEHOERT_ZU is Varchar(20), the parameter "Halle 6 -  Schlosserei" is 21 chars long.... After correcting the length to 30 it works fine. Interessting the slq console does not compain about the too long input for the query.

Thanks for answering to my posting that fast.

Can you please add 1 line to describe the difference between using {0} vs. @p0 ?

Thanks

Niko

-----Ursprüngliche Nachricht-----
Von: Jiří Činčura [mailto:[hidden email]]
Gesendet: Dienstag, 2. Mai 2017 11:57
An: [hidden email]
Betreff: Re: [Firebird-net-provider] EF handling - as string parameter

What's the AUSLASTUNG_GEHOERT_ZU's datatype?

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

On Tue, May 2, 2017, at 11:55, Nikolaus Kern wrote:

> Hello Jiri,
>
> thanks for the response.
>
> I guess I am missing something here. The code below produces the same
> error as {0}.
>
> ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE
> PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND
> AUSLASTUNG_GEHOERT_ZU = @p0 ORDER BY PRODUKTIONSAUFTRAG_ID"; tmpPPA =
> this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery, new
> object[]{this.CurrentHalle}).ToList();
>
> If I copy the query to the SQL tool and execute the query it works
> fine (even when it does not return a record, which is also the case in
> other working queries).
> SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE PRODUKTIONSSTATUS NOT IN
> ('Abgeschlossen','Storno') AND AUSLASTUNG_GEHOERT_ZU = 'Halle 6 -
> Schlosserei' ORDER BY PRODUKTIONSAUFTRAG_ID
>
> Do you have an idea whats missing?
>
> Thanks
>
> Niko
>
> -----Ursprüngliche Nachricht-----
> Von: Jiří Činčura [mailto:[hidden email]]
> Gesendet: Dienstag, 2. Mai 2017 10:02
> An: [hidden email]
> Betreff: Re: [Firebird-net-provider] EF handling - as string parameter
>
> You should use @p0 instead of {0}
> (https://msdn.microsoft.com/en-us/library/system.data.entity.dbset.sqlquery%28v=vs.113%29.aspx).
> Optionally you can also specify the Size in FbParameter.
>
> --
> Mgr. Jiří Činčura
> https://www.tabsoverspaces.com/
>
> On Tue, May 2, 2017, at 09:58, Nikolaus Kern wrote:
> > Hi,
> >
> > I just saw that the query and the execution do not match correct.
> > This is the query with the parameter that creates an exception if
> > the parameter is "Halle 6 - Schlosserei".
> >
> > ppaQuery = "SELECT * FROM LPP_PRODUKTIONSAUFTRAEGE WHERE
> > PRODUKTIONSSTATUS NOT IN ('Abgeschlossen','Storno') AND
> > AUSLASTUNG_GEHOERT_ZU = {0} ORDER BY PRODUKTIONSAUFTRAG_ID"; tmpPPA
> > = this._ctx.LPP_PRODUKTIONSAUFTRAEGE.SqlQuery(ppaQuery,
> > this.CurrentHalle).ToList();
> >
> > Niko
> >
> > -----Ursprüngliche Nachricht-----
> > Von: Jiří Činčura [mailto:[hidden email]]
> > Gesendet: Dienstag, 2. Mai 2017 09:43
> > An: [hidden email]
> > Betreff: Re: [Firebird-net-provider] EF handling - as string
> > parameter
> >
> > What parameter? I don't see any parameter in the query.
> >
> > --
> > Mgr. Jiří Činčura
> > https://www.tabsoverspaces.com/
> >
> >
> > --------------------------------------------------------------------
> > --
> > -------- Check out the vibrant tech community on one of the world's
> > most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> > _______________________________________________
> > Firebird-net-provider mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
> >
> >
> > --------------------------------------------------------------------
> > --
> > -------- Check out the vibrant tech community on one of the world's
> > most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> > _______________________________________________
> > Firebird-net-provider mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
> ----------------------------------------------------------------------
> -------- Check out the vibrant tech community on one of the world's
> most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
>
> ----------------------------------------------------------------------
> -------- Check out the vibrant tech community on one of the world's
> most engaging tech sites, Slashdot.org! http://sdm.link/slashdot 
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Jiří Činčura-2
> Can you please add 1 line to describe the difference between using {0}
> vs. @p0 ?

Well, AFAIK only the @p0 is supported. Maybe the {0} works as well, but
I know only about the @p0 from documentation. Of course we can check EF6
sources, but I'm too lazy to do that. :D

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EF handling - as string parameter

Daniel Rail
Hi,

At May 2, 2017, 7:55 AM, Jiří Činčura wrote:

>> Can you please add 1 line to describe the difference between using {0}
>> vs. @p0 ?

> Well, AFAIK only the @p0 is supported. Maybe the {0} works as well, but
> I know only about the @p0 from documentation. Of course we can check EF6
> sources, but I'm too lazy to do that. :D

Actually, it's not. I'm using the Firebird's default parameter syntax,
the question mark. And, I use "new object[] {paramValue1, paramValue2,
etc..}" to pass the parameters, and paramValueX are just simple
variables or object properties, don't have to be FbParameter. I don't
remember trying @p0, although I did try with actual names(i.e.:
@PDate), since it was also documented in the MSDN documentation, but I
was getting an invalid field error coming from Firebird.

But,  as  you  mentioned, one might have to look at the EF6 sources to
find out the real behavior.

--
Best regards,
 Daniel Rail
 Senior Software Developer
 ACCRA Solutions Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Loading...