Problems with using SQLServer

dc · 2012-11-19 11:07:44

Hi,
We are currently investigating possibility of using your great framework with our projects. However we have problem using it with MS SQLServer. Our example project works perfectly with default settings, but since we want to use SQLServer I had to add those two lines:

  Props := TOleDBMSSQLConnectionProperties.Create('localhost\SQLEXPRESS', 'testDB', 'sa', '12Qwerty');
...
  VirtualTableExternalRegister(Model, TSQLMyClass, Props, 'dbo.MyClass');

It works OK with insert, delete and update, however when it comes to select it fails on

TSQLMyClass.CreateAndFillPrepare

with exception Invalid column name ''RowID''. When I tried to investigate it further I found that there is inner exception ExecuteFromJSON: Invalid Types[0]=0.

Am I doing something wrong or is it a bug? Thanks in advance for any info.

Regards
dc

ab · 2012-11-19 11:57:06

How is your class defined?

dc · 2012-11-20 10:36:41

Hi, here is definition of TSQLMyClass

  TSQLMyClass= class(TSQLRecord)
  private
    fPLU: Int64;
    fArtNr: RawUTF8;
    fName: RawUTF8;
    fPurchDate: TDateTime;
    fExpiryDate: TDateTime;
    fPrice: Currency;
  published
    property PLU: Int64 read fPLU write fPLU;
    property Name: RawUTF8 read fName write fName;
    property ArtNr: RawUTF8 read fArtNr write fArtNr;
    property PurchDate: TDateTime read fPurchDate write fPurchDate;
    property ExpiryDate: TDateTime read fExpiryDate write fExpiryDate;
    property Price: Currency read fPrice write fPrice;
  end;

ab · 2012-11-20 13:16:52

Sounds right to me.

I can't find out directly what is wrong here.
The error "Invalid Types[0]=0" sounds like a column with an invalid type.
Can you debug your code, and check which column/field is faulty, and how it is retrieved from the DB point of view, at the SELECT level?

dc · 2012-11-20 15:22:03

ab wrote:

Sounds right to me.
I can't find out directly what is wrong here.
The error "Invalid Types[0]=0" sounds like a column with an invalid type.
Can you debug your code, and check which column/field is faulty, and how it is retrieved from the DB point of view, at the SELECT level?

At the select level it is adding RowId column to query. Here is SQLTableSimpleFields from debugger (table name was changed to Assortment, but it's irrelevant):

(
  ('PLU,Name,ArtNr,PurchDate,ExpiryDate,Price', 'Assortment.PLU,Assortment.Name,Assortment.ArtNr,Assortment.PurchDate,Assortment.ExpiryDate,Assortment.Price'),
  ('RowID,PLU,Name,ArtNr,PurchDate,ExpiryDate,Price', 'Assortment.RowID,Assortment.PLU,Assortment.Name,Assortment.ArtNr,Assortment.PurchDate,Assortment.ExpiryDate,Assortment.Price')
)

One more thing: there are proper values in Decoder.FieldValues, however Types has only ftUnknown values (then exception "ExecuteFromJSON: Invalid Types[%d]=%d" is raised.

Last edited by dc (2012-11-20 15:29:42)

ab · 2012-11-20 16:31:32

dc wrote:

One more thing: there are proper values in Decoder.FieldValues, however Types has only ftUnknown values (then exception "ExecuteFromJSON: Invalid Types[%d]=%d" is raised.

This means that the columns type of the SELECT result are all to unknown type, whereas they should be recognized by SynDB units.
Could you step in the code and find out what is wrong?
(I do not have MS SQL at hand here)

dc · 2012-11-22 09:35:30

OK, I've stepped through code and there is this peace of code

function TOleDBConnectionProperties.ColumnTypeNativeToDB(
  const aNativeType: RawUTF8; aScale: integer): TSQLDBFieldType;
begin
  result := OleDBColumnToFieldType(GetInteger(pointer(aNativeType)),aScale)
end;

Problem is, that function GetInteger(pointer(aNativeType)) returns 0 for all native types used in my example ('bigint', 'nvarchar', 'datetime', 'money'). In TSQLRestServerStaticExternal.ExecuteFromJSON fFields has this value:

(
  ('ID', 'bigint', ftUnknown, 0, 19, 0, False), 
  ('PLU', 'bigint', ftUnknown, 0, 19, 0, False), 
  ('Name', 'nvarchar', ftUnknown, 250, 0, 0, False), 
  ('ArtNr', 'nvarchar', ftUnknown, 100, 0, 0, False), 
  ('PurchDate', 'datetime', ftUnknown, 0, 0, 0, False), 
  ('ExpiryDate', 'datetime', ftUnknown, 0, 0, 0, False), 
  ('Price', 'money', ftUnknown, 0, 19, 4, False)
)

dc

ab · 2012-11-22 15:04:29

Which provider do you use?
SQLNCLI10?

I tried to fix the issue in http://synopse.info/fossil/info/a58196185f

dc · 2012-11-22 16:04:13

ab wrote:

Which provider do you use?
SQLNCLI10?
I tried to fix the issue in http://synopse.info/fossil/info/a58196185f

Yes, thank you your patch fixes both issues (rowid and type), however there is one more problem; when I ty to execute something like this:

DB.Delete(TSQLMyClass, 'PLU < ?', [20000]);

i get error "OLEDB Error 80040E14 - (line 1): Incorrect syntax near ':'." and query in ExecuteDirect method is: "delete from dbo.Assortment where PLU < :(20000):"

dc

ab · 2012-11-26 09:25:06

I've fixed an issue in TSQLRestServerStaticExternal.EngineDeleteWhere() when calling commands like MyDB.Delete(TSQLMyClass, 'PLU < ?', [20000]).
See http://synopse.info/fossil/info/73c7d1ce94

Hope it helps.

Thanks for the feedback.

dc · 2012-11-26 11:56:35

Thank you very much, works great.

mORMot Open Source

#1 2012-11-19 11:07:44

Problems with using SQLServer

#2 2012-11-19 11:57:06

Re: Problems with using SQLServer

#3 2012-11-20 10:36:41

Re: Problems with using SQLServer

#4 2012-11-20 13:16:52

Re: Problems with using SQLServer

#5 2012-11-20 15:22:03

Re: Problems with using SQLServer

#6 2012-11-20 16:31:32

Re: Problems with using SQLServer

#7 2012-11-22 09:35:30

Re: Problems with using SQLServer

#8 2012-11-22 15:04:29

Re: Problems with using SQLServer

#9 2012-11-22 16:04:13

Re: Problems with using SQLServer

#10 2012-11-26 09:25:06

Re: Problems with using SQLServer

#11 2012-11-26 11:56:35

Re: Problems with using SQLServer

Board footer