Settings.ParameterPrefix is not affected in method using StatementAttribute

yamac commented 1 year ago

SmartSql version

4.1.64

Database provider and version

DbProvider = "MySql" MySql.Data: 8.0.31 MySql Server: 8.0.21

Steps to reproduce

I've searched the documentation but couldn't find if this is the spec or not.

Prepare a SmartSqlMapConfig.xml. Set ParameterPrefix to $ in Settings tag.

SmartSqlMapConfig.xml

<SmartSqlMapConfig xmlns="http://SmartSql.net/schemas/SmartSqlMapConfig.xsd">
  <Settings ParameterPrefix="$"/>
  <Database>
    <DbProvider Name="MySql"/>
    <Write Name="WriteDB" ConnectionString="${ConnectionString}"/>
  </Database>
  <SmartSqlMaps>
    <SmartSqlMap Path="Mappers" Type="Directory"/>
  </SmartSqlMaps>
</SmartSqlMapConfig>

Prepare the following two interface methods. The first one applies Sql from Xml, The next one applies Sql from StatementAttribute's Sql parameter.

public interface IItemRepository : IRepository
{
    Item FindByIdWithXml(long id);

    [Statement(Sql = "SELECT * FROM item WHERE id = $id")]
    Item FindByIdWithAttribute(long id);
}

Prepare Mapper.

Item.xml

<SmartSqlMap Scope="Item" xmlns="http://SmartSql.net/schemas/SmartSqlMap.xsd">
  <Statements>
    <Statement Id="FindByIdWithXml">
      SELECT * FROM item WHERE id = $id
    </Statement>
  </Statements>
</SmartSqlMap>

Executes two methods.

  var itemWithXml = _itemRepository.FindByIdWithXml(id);
  var itemWithAttribute = _itemRepository.FindByIdWithAttribute(id);

FindByIdWithXml succeeds. I defined the placeholder as $id on the Xml because I set the Settings.ParameterPrefix to $.

But FindByIdWithAttribute fails with error message "Unknown column '$id' in 'where clause'". It seems that Settings.ParameterPrefix is not affected in method using StatementAttribute.

I'm not sure if this is the spec or not.

Expected result

FindByIdWithXml and FindByIdWithAttribute behave the same.

Actual result

FindByIdWithXml succeeds. But FindByIdWithAttribute fails with error message "Unknown column '$id' in 'where clause'".

It works if change the placeholder from $id to ?id like below.

    [Statement(Sql = "SELECT * FROM item WHERE id = ?id")]
    Item FindByIdWithAttribute(long id);
}

Stack trace

   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at SmartSql.Command.CommandExecuter.<>c__DisplayClass11_0.<ExecuteReader>b__0()
   at SmartSql.Command.CommandExecuter.ExecuteWrap[TResult](Func`1 executeImpl, ExecutionContext executionContext, String operation)
   at SmartSql.Command.CommandExecuter.ExecuteReader(ExecutionContext executionContext)
   at SmartSql.Middlewares.CommandExecuterMiddleware.Invoke[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.AbstractMiddleware.InvokeNext[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.DataSourceFilterMiddleware.Invoke[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.AbstractMiddleware.InvokeNext[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.TransactionMiddleware.Invoke[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.AbstractMiddleware.InvokeNext[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.AbstractMiddleware.Invoke[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.AbstractMiddleware.InvokeNext[TResult](ExecutionContext executionContext)
   at SmartSql.Middlewares.InitializerMiddleware.Invoke[TResult](ExecutionContext executionContext)
   at SmartSql.DbSession.DefaultDbSession.Invoke[TResult](AbstractRequestContext requestContext)
   at SmartSql.DbSession.DefaultDbSession.QuerySingle[TResult](AbstractRequestContext requestContext)
   at SmartSql.SqlMapper.<>c__DisplayClass16_0`1.<QuerySingle>b__0(IDbSession dbSession)
   at SmartSql.SqlMapper.ExecuteImpl[TResult](Func`2 executeFunc)
   at SmartSql.SqlMapper.QuerySingle[T](AbstractRequestContext requestContext)
   at temRepository_Impl_3c1a3cbb5d9d4205845e255b255e6c33.FindById(Int64 )
   at TrySmartSql.Item.ItemService.FindItemById() in /Users/main/Documents/Visual Studio/Projects/aspnet-trial/src/TrySmartSql/Item/ItemService.cs:line 70
   at Program.<Main>$(String[] args) in /Users/main/Documents/Visual Studio/Projects/aspnet-trial/src/TrySmartSql/Program.cs:line 61

xiangxiren commented 1 year ago

From reading the XML file and loading the Satement,

https://github.com/dotnetcore/SmartSql/blob/d3df83b1a06f8ee1f047060bcd55f138216eeacd/src/SmartSql/Configuration/Tags/TagBuilders/SqlTextBuilder.cs#L10-L20

It can be seen that the final SQL statement uses DbProvider.ParameterPrefix to replace SmartSqlConfig.Settings.ParameterPrefix.In your example, ? Replaced $.

However, this part is missing when loading Satement from StatementAttribute. https://github.com/dotnetcore/SmartSql/blob/d3df83b1a06f8ee1f047060bcd55f138216eeacd/src/SmartSql.DyRepository/EmitRepositoryBuilder.cs#L317-L329

xiangxiren commented 1 year ago

Fixed in 4.1.65

dotnetcore / SmartSql