Hi all !

I'm new to this forum (and also to LLBLGEN), and I first want to thank you all for this wonderful product that will tremendously help us during the development of our project...

First post among many more to come I guess.

I'm currently playing with the demo version 2.6, the runtime version of the ORM Support Class is 2.6.8.1114 My project is built on .NET 2.0.

My test project uses Postgres 8.3.

I have a very simple table (Slony users will certainly recognize it):

CREATE TABLE _biocluster.sl_table
(
  tab_id integer NOT NULL, -- Unique key for Slony-I to use to identify the table
  tab_reloid oid NOT NULL, -- The OID of the table in pg_catalog.pg_class.oid
  tab_relname "name" NOT NULL, -- The name of the table in pg_catalog.pg_class.relname used to recover from a dump/restore cycle
  tab_nspname "name" NOT NULL, -- The name of the schema in pg_catalog.pg_namespace.nspname used to recover from a dump/restore cycle
  tab_set integer, -- ID of the replication set the table is in
  tab_idxname "name" NOT NULL, -- The name of the primary index of the table
  tab_altered boolean NOT NULL, -- Has the table been modified for replication?
  tab_comment text, -- Human-oriented description of the table
  CONSTRAINT "sl_table-pkey" PRIMARY KEY (tab_id),
  CONSTRAINT "tab_set-set_id-ref" FOREIGN KEY (tab_set)
      REFERENCES _biocluster.sl_set (set_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT sl_table_tab_reloid_key UNIQUE (tab_reloid)
)
WITH (OIDS=FALSE);

All "name" typed fields are correctly mapped into string attibutes by llglgen.

then here is my piece of code, the goal is to retrieve all distinct tab_nspname values that are not prefixed by "sys" :

private void formTest_Load (object sender, EventArg e)
{
  ResultsetFields schemaListFields = new ResultsetFields(1);
  schemaListFields.DefineField (SlTableFields.TabNspname, 0);
  IRelationPredicateBucket bucket = new RelationPredicateBucket();

  // My "WHERE tab_nspname NOT LIKE 'sys%' " statement 
  bucket.PredicateExpression.Add ( !(SlTableFields.TabNspname % "sys%) );

  DataTable schemaList = new DataTable();

  SortExpression sorter = new SortExpression (SlTableFields.TabNspname | SortOperator.Ascending);

  using (DataAccessAdapter adapter = new DataAccessAdapter() )
  {
    adapter.SchemaNameUsageSetting = SchemaNameUsage.ForceName;
    adapter.SchemaNameToUse = "_biocluster";

    adapter.FetchTypedList (schemaListFields,  schemaList, bucket, 0, sorter, false);
  }
}

When run, I got a ORMQueryExecutionException :

_An exception was caught during the execution of a retrieval query: ERROR: 22007: invalid input syntax for type timestamp: "%sys%". Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. _

Fact is, the error is genrated by Postgres server, because the generated query is incorrect, here it is:

SELECT DISTINCT "_biocluster"."sl_table"."tab_nspname" AS "TabNspname" FROM "_biocluster"."sl_table" WHERE ( ( NOT "_biocluster"."sl_table"."tab_nspname" LIKE '%sys%'::timestamp)) ORDER BY "_biocluster"."sl_table"."tab_nspname" ASC

The "LIKE '%sys%'::timestamp " is wrong, I don't know where this timestamp comes from...

Even if I change the type of tab_nspname column from "name" to character varying(255) I end up with the same result...

I tried to google that but could not find any answer...

Thanks so much in advance for your help...

Michael

FYI, here is the stack trace:

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase .ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates) at UT.Core.Database.frmImageInsert.frmImageInsert_Load(Object sender, EventArgs e) in Y:\UT\Core\Database\frmImageInsert.cs:line 69 at System.Windows.Forms.Form.OnLoad(EventArgs e) at System.Windows.Forms.Form.OnCreateControl() at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible) at System.Windows.Forms.Control.CreateControl() at System.Windows.Forms.Control.WmShowWindow(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ContainerControl.WndProc(Message& m) at System.Windows.Forms.Form.WmShowWindow(Message& m) at System.Windows.Forms.Form.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.IntCreateWindowEx(Int32 dwExStyle, String lpszClassName, String lpszWindowName, Int32 style, Int32 x, Int32 y, Int32 width, Int32 height, HandleRef hWndParent, HandleRef hMenu, HandleRef hInst, Object pvParam) at System.Windows.Forms.UnsafeNativeMethods.CreateWindowEx(Int32 dwExStyle, String lpszClassName, String lpszWindowName, Int32 style, Int32 x, Int32 y, Int32 width, Int32 height, HandleRef hWndParent, HandleRef hMenu, HandleRef hInst, Object pvParam) at System.Windows.Forms.NativeWindow.CreateHandle(CreateParams cp) at System.Windows.Forms.Control.CreateHandle() at System.Windows.Forms.Form.CreateHandle() at System.Windows.Forms.Control.get_Handle() at System.Windows.Forms.Form.SetVisibleCore(Boolean value) at System.Windows.Forms.Control.Show() at UT.Core.Database.frmMain.LaunchChildForm(Form ChildForm) in Y:\UT\Core\Database\frmMain.cs:line 40 at UT.Core.Database.frmMain.insertImagesToolStripMenuItem_Click(Object sender, EventArgs e) in Y:\UT\Core\Database\frmMain.cs:line 62 at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e) at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e) at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ToolStrip.WndProc(Message& m) at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms. UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at UT.Core.Database.Program.Main() in Y:\UT\Core\Database\Program.cs:line 17

bucket.PredicateExpression.Add ( !(SlTableFields.TabNspname % "sys%) );

Just to make sure, your real code did contain the ending qoutes of the "sys%", right?

SELECT DISTINCT "_biocluster"."sl_table"."tab_nspname" AS "TabNspname" FROM "_biocluster"."sl_table" WHERE ( ( NOT "_biocluster"."sl_table"."tab_nspname" LIKE '%sys%'::timestamp)) ORDER BY "_biocluster"."sl_table"."tab_nspname" ASC

Did you get this query from VS output window using LLBLGen Pro tracing? If not please do so and post the produced SQL query here.

Yes Walaa, sorry for the typo (I use two computers), for sure my code DOES contain the ending quote....

Btw, thanks a lot for the answer, that was quick !

I got this query from the ErrorSql field provided by the InnerException (when viewing the details of the thrown exception in VS)...

here is the generated query from the ouput window :

Generated Sql query:

Query: SELECT DISTINCT "_biocluster"."sl_table"."tab_nspname" AS "TabNspname" FROM "_biocluster"."sl_table" WHERE ( ( NOT "_biocluster"."sl_table"."tab_nspname" LIKE :TabNspname1)) ORDER BY "_biocluster"."sl_table"."tab_nspname" ASC

Parameter: :TabNspname1 : DateTime. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: %sys%.

This is strange, because if I change my bucket from a LIKE type into this:

  bucket.PredicateExpression.Add(SlTableFields.TabNspname != "sysrep");

Then the generated query becomes:

Generated Sql query:

Query: SELECT DISTINCT "_biocluster"."sl_table"."tab_nspname" AS "TabNspname" FROM "_biocluster"."sl_table" WHERE ( ( "_biocluster"."sl_table"."tab_nspname" <> :TabNspname1)) ORDER BY "_biocluster"."sl_table"."tab_nspname" ASC

Parameter: :TabNspname1 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "sysrep".

How weird is that, I just regenerated my code to make sure that this field is mapped into a string, and still the same behaviour...

Thx a lot for your help

I have no idea what's going on, but another thing noticed is that on your code you only specified an end wildard % but in the generated SQl there was a starting one also, which is strange if the posted code is the real one.

Just out of despair I want you to try the following:

1st trial: Use bucket.PredicateExpression.Add(new FieldLikePredicate(SlTableFields.TabNspname, null, "%sys%"));

2nd trial: Use anything else other than %sys%, for example %xyz%, and check the output query.

Sorry for the typo, here is a real copy/paste of my code :

private void frmImageInsert_Load(object sender, EventArgs e)

{

_adapter = new DataAccessAdapter();

ResultsetFields projectListFields = new ResultsetFields(1);

projectListFields.DefineField(SlTableFields.TabNspname, 0);

IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.PredicateExpression.Add(new FieldLikePredicate(SlTableFields.TabNspname, null, "%sys%"));

_adapter.SchemaNameUsageSetting = SchemaNameUsage.ForceName;

_adapter.SchemaNameToUse = "_" + ((NameValueCollection)ConfigurationManager.GetSection("DatabaseReplication/Cluster"))["Name"];

_adapter.FetchTypedList(projectListFields, projectList, bucket, 0, new SortExpression(SlTableFields.TabNspname | SortOperator.Ascending), false);

}

Here is the generated query :

Generated Sql query:

Query: SELECT DISTINCT "_biocluster"."sl_table"."tab_nspname" AS "TabNspname" FROM "_biocluster"."sl_table" WHERE ( ( "_biocluster"."sl_table"."tab_nspname" LIKE :TabNspname1)) ORDER BY "_biocluster"."sl_table"."tab_nspname" ASC

Parameter: :TabNspname1 : DateTime. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: %sys%.

Then with :

bucket.PredicateExpression.Add(new FieldLikePredicate(SlTableFields.TabNspname, null, "%xyz%")); 

I end up with :

Query: SELECT DISTINCT "_biocluster"."sl_table"."tab_nspname" AS "TabNspname" FROM "_biocluster"."sl_table" WHERE ( ( "_biocluster"."sl_table"."tab_nspname" LIKE :TabNspname1)) ORDER BY "_biocluster"."sl_table"."tab_nspname" ASC
Parameter: :TabNspname1 : DateTime. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: %xyz%.

Same thing

thx

Mmm.. weird

My PG tests work very nice using "LIKE" and "NOT LIKE" against the Sakila DB. Could you attach some repro project zip (the DDL script with some data script and your LGP project).

ok so that's good news for me...

I'll send you all this as soon as I get back to the office !

thx

Hi !

here are some files for your test purposes...

I included a backup of my "llblgen" postgres schema (schema only, no data, no owner), my LGP project plus visual studio files (Program.cs and app.config)...

I'm having the error any time I use a FieldLikePredicate on any single character varying column...

thx for your time

michael

Hi Michael,

I did a lot of tests with your project and I can't reproduce the first problem. I don't receive any exceptions.

LLBLGen RTL: 2.6.8.1125 PG version: 8.3.1 .NET 2.0 / VS2005

This is my generated code using your test:

Query: SELECT DISTINCT "stringtest"."Patient"."PatientName" FROM "stringtest"."Patient" WHERE ( ( "stringtest"."Patient"."PatientName" LIKE :PatientName1)) ORDER BY "stringtest"."Patient"."PatientName" ASC
Parameter: :PatientName1 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "A%".

So, I'm out of clues right now Could you zip and attach the whole project (whitout bin/obj) with your generated code in it? (you just post the DDL and the LGP, which is fine, but we can reproduce the issue).

Hi daelmo !

Here are the requested files...

I'm actually using PG 8.3 provided by EnterpriseDB .NET 2.0 and VS2008

I'm also using Npgsql 2.0.1 downloaded from here : http://pgfoundry.org/frs/?group_id=1000140&release_id=1244

Maybe that's the source of the problem.

Thx again for your time...

Michael

Here are the project files

Reproduced with Npgsql2.0.X.

RTL 2.6.8.1125

Npgsql 2.0.1.0 (RT: v2.0.50727

Repro code

EntityCollection<PatientEntity> patients = new EntityCollection<PatientEntity>();
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(new FieldLikePredicate(PatientFields.PatientName, null, "A%"));      

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(patients, bucket);
}

Exception message

An exception was caught during the execution of a retrieval query: ERROR: 22007: invalid input syntax for type timestamp: "%sys%". Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Exception stack trace

en SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) en C:\Program Files\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\Query\RetrievalQuery.cs:línea 113 en SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) en C:\Program Files\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:línea 546 en SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) en C:\Program Files\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:línea 4286 en SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) en C:\Program Files\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:línea 2418 en SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket) en C:\Program Files\Solutions Design\LLBLGen Pro v2.6\Sourcecode\RuntimeLibraries\Net2.x\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:línea 2228 en TestFieldLikePredicate.Program.EntityCollectionLikePredicate() en C:\Users\David\Desktop\project\code\ConsoleApp\Program.cs:línea 55 en TestFieldLikePredicate.Program.Main(String[] args) en C:\Users\David\Desktop\project\code\ConsoleApp\Program.cs:línea 65 en System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) en System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) en Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() en System.Threading.ThreadHelper.ThreadStart_Context(Object state) en System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) en System.Threading.ThreadHelper.ThreadStart()

Exception SQL

SELECT DISTINCT "stringtest"."Patient"."PatientName" FROM "stringtest"."Patient" WHERE ( ( "stringtest"."Patient"."PatientName" LIKE 'A%'::timestamp)) ORDER BY "stringtest"."Patient"."PatientName" ASC

The constructor of _NpgsqlParameter _seems to return the wrong parameter type. We will look into it. In the meantime please use the Npgsql1.0.1, which works ok.

Thx a lot for the advice and for yoru time daelmo !!

This forum rocks

Weird... first of all, the query should have a parameter name at the spot of the pattern, and not the pattern + some weird '::timestamp' suffix, second of all, the parameter is created normally, with as type 'Varchar' and as value the pattern.

When I create a normal connection + command + dataadapter, it works, so I'll dig some deeper.

Bug in npgsql.

v1.0, the one we build against, has NpgsqlDbType.Varchar as value 21. As at runtime an assembly redirect is used to v2.x, this '21' changes value, as in v2.x if you want to use v2 of Npgsql, the NpgsqlDbType.Timestamp has value '21'. (as varchar has value 22 )

Will report this to npgsql.

edit: http://pgfoundry.org/forum/forum.php?thread_id=2011&forum_id=518

Thx a lot, I'm sure this will help other developpers...

Reactivity is very impressive here, my bosses will love it

michaelsalomon78 wrote:

Thx a lot, I'm sure this will help other developpers...

Reactivity is very impressive here, my bosses will love it

Support is part of the products quality

Apparently, Postgres guys are no in a "bug fixing" mood today

michaelsalomon78 wrote:

Apparently, Postgres guys are no in a "bug fixing" mood today

His reasoning is silly, the people with assembly redirects (like... us) have broken code now, see your reprocase. So changing it back does nothing to existing code, and FIXES code which redirects.

It only affects people who refer to the int values instead of the enum values... though they too have broken code (varchar was 21, is now 22... )

Josh posted a followup which explains their reason, and I now understand why.

What's funny is that if I look at the statement which causes problems in reflector on a machine with npgsql 2.x, I see:

public override IDataParameter CreateLikeParameter(string fieldName, string pattern)
{
    return new NpgsqlParameter(this.CreateParameterName(fieldName), NpgsqlDbType.Timestamp, pattern.Length, "", ParameterDirection.Input, false, 0, 0, DataRowVersion.Current, pattern);
}

so clearly something is wrong, but I think it's caused by the int value stored in the IL which is then casted to the type it isn't meant for.

There's a workaround, using the DbType type, so I'll use that instead.

thx Frans

Sounds good to me Glad to have pointed you a pb that can make llblgen become even better

Michael, could you please test the attached DQE with v2.x of the npgsql dll to see if this fixes things for you?

Hi Frans,

It's working perfectly great with npgsql v2 and LIKE predicates now !!

Thx so much !!

Michael