Support for SAP Anywhere database ?

[sravangontla]

Hi Team,
can you please let me know the PetaPoco supports SAP Anywhere ?

note:
Inside SAP Anywhere parameterized query use ":" instead of "@"
ex : ParameterName = ":Name";

More error details :
https://stackoverflow.com/questions/30688879/why-does-sql-anywhere-ignore-the-named-parameter

Looking for :
Asp.net core
SAP SQL Anywhere (database)

Thanks,
Sravan G

[iadaz]

I can't help with SAP Anywhere, but I am using PetaPoco with SAP Hana. Although there's no support for it out-of-the-box, it's fairly trivial to create your own database provider to act as the link between PetaPoco and the SAP .net provider. See https://github.com/CollaboratingPlatypus/PetaPoco/wiki/Custom-DB-Providers

[sravangontla]

Thanks for information. Its good to hear that it supports custom-DB, I am looking for the same.
I have gone through the above link and its very simple to configure.

Do we have any sample project with custom DB provider using asp.net core ?

And it supports any generic way to get the DB result instead of writing SQL queries (ex: get) ?

I am facing issue while implementing custom provider with help of above link, error msg below:
Could not match connection to a provider. (Parameter 'type')

Thanks,
Sravan G

[iadaz]

Here is an example of how my database provider for SAP Hana is set up:

public class PetaPocoHanaDatabaseNetCoreProvider : DatabaseProvider
    {
        public override DbProviderFactory GetFactory()
        {
            try
            {
                var factory = GetFactory("Sap.Data.Hana.HanaFactory, Sap.Data.Hana.Core.v2.1, Culture=neutral, PublicKeyToken=0326b8ea63db4bc4");
                return factory;
            }
            catch (Exception e)
            {
                Logger.Error("GetFactory failed for 'Sap.Data.Hana, Sap.Data.Hana.Core.v2.1'");
                throw e;
            }
        }

        public override string GetParameterPrefix(string connectionString)
        {
            return ":";
        }

       //other overrides here depending on syntax requirements
    }

You will probably need to override the GetExistsSql() and EscapeSqlIdentifier() methods (etc) depending on what syntax the SAP Anywhere server expects.

You would then create a DatabaseConfiguration with:

   var dbconfig = DatabaseConfiguration.Build()
                       .UsingConnectionString(ConnectionString)
                       .UsingProvider<PetaPocoHanaDatabaseNetCoreProvider>();

Connection string should match the format expected by the underlying connector. Then use the dbconfig to open a connection as per the examples.

This will enable you to use PetaPoco's automatic SQL statement generation to fetch, update, etc (based on property attributes).

[sravangontla]

Hi @iadaz
Thanks for providing the detail example.

I have implemented same code in my project but got below error for insert into database
"Syntax error near ':' on line 1" where as It's working for get call's.

Is there any way to print the insert query which is created by PetaPoco ?

        using (var db = this._dbconfig.Create())
        {
            var res = await db.InsertAsync(entity); 
        }

My code:

    public override DbProviderFactory GetFactory()
    {
        // Need to specify class and assembly of the DbProviderFactory  
        return Sap.Data.SQLAnywhere.SAFactory.Instance; 
    }


    public override string GetParameterPrefix(string connectionString)
    {
        // This database prefixes parameters in SQL statements with : instead of @
        return ":";
    } 

I don't find much information specific to custom provider in wiki..
Can you please provide some more info regarding "GetExistsSql() and EscapeSqlIdentifier()" ?

[iadaz]

After an error you can check the LastSQL and LastCommand properties of the database object to see what was sent to the provider.