Recent

Author Topic: Null record problem workaround for MSSQL database  (Read 9140 times)

Ronan

  • Full Member
  • ***
  • Posts: 132
Null record problem workaround for MSSQL database
« on: July 19, 2011, 09:51:35 am »
hi all,

I've traversed through the database forum but couldn't locate the exact solution to that problem.
What I've got is ODBCConnection , SQLTranasaction, SQLQuery DataSource and finally DBGRid, MSSQL database is fairly simple with 2 fields where 1st field is primary. For record saving I use
Code: [Select]
procedure TForm1.Button1Click(Sender: TObject);
begin
SQLQuery1.Edit;
SQLQuery1.Post;
end;

procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
SQLQuery1.ApplyUpdates;
SQLTransaction1.Commit;
end;

DeleteSQL, InsertSQL and UpdateSQL properties are empty of SQLQuery.

It shows the records but doesn't write changes to database when I'm trying to change it manually in DBGRid (tried the DBnavigator as well w/o success), so I think that I'm suffering from that null record problem mentioned in this forum(http://forum.lazarus.freepascal.org/index.php/topic,13011.msg67883.html#msg67883) by Lacak2.

Somewhere in the forum it's been mentioned that setting the proper SQL command in InsertSQL, DeleteSQL and UpdateSQL properties might work, But I really don't get what SQL command to set into those properties, although their name explicitly depict their usage?

Help will be appreciated!!


Lacak2

  • Guest
Re: Null record problem workaround for MSSQL database
« Reply #1 on: July 19, 2011, 01:09:11 pm »
Do you have primary key defined on your table ? (it seems, that yes)
How do you have set these properties on SQLQuery1:
- UpdateMode
- ParseSQL
- UsePrimaryKeyAsKey
Can you insert new records ? (or problem exists only when updating existing record)
Do you insert null values in some columns ?

Ronan

  • Full Member
  • ***
  • Posts: 132
Re: Null record problem workaround for MSSQL database
« Reply #2 on: July 19, 2011, 01:32:59 pm »
Quote
Do you have primary key defined on your table ? (it seems, that yes)
YES, totally 2 fields in DB where 1 st is primary key,
I paid much attention when creating that table in MS managment studio, "Allow Nulls" to be unchecked in order to overcome that issue. But stil the same.

Updatemode = UpWhereKeyOnly
ParseSQL = True
UsePrimaryKeyAsKey = true

I simply use  SQLQuery.SQL := ' select * from Table1'

Quote
Can you insert new records ? (or problem exists only when updating existing record)
Actually the scenario is a bit different for me here, either inserting  or trying to change the existing records compiler throws exception with "Parapemeter 0 is of type WideString, which not supported yet"

Quote
Do you insert null values in some columns ?
NO,
But need to inform you about one weird thing, Even though I was deliberetly careful not use null values in records when I open the table in MS managment studio the last row is always filled with null values.

Thanks in advance,

Lacak2

  • Guest
Re: Null record problem workaround for MSSQL database
« Reply #3 on: July 19, 2011, 02:17:30 pm »
Quote
Do you have primary key defined on your table ? (it seems, that yes)
YES, totally 2 fields in DB where 1 st is primary key,
I paid much attention when creating that table in MS managment studio, "Allow Nulls" to be unchecked in order to overcome that issue.
OK

Quote
But stil the same.

Updatemode = UpWhereKeyOnly
ParseSQL = True
UsePrimaryKeyAsKey = true
OK

Quote
Quote
I simply use  SQLQuery.SQL := ' select * from Table1'
OK

Quote
Quote
Can you insert new records ? (or problem exists only when updating existing record)
Actually the scenario is a bit different for me here, either inserting  or trying to change the existing records compiler throws exception with "Parapemeter 0 is of type WideString, which not supported yet"
Hops this is the problem !
What datatype have your columns (NCHAR, NVARCHAR) ?

Quote
Quote
Do you insert null values in some columns ?
NO,
But need to inform you about one weird thing, Even though I was deliberetly careful not use null values in records when I open the table in MS managment studio the last row is always filled with null values.
it is OK, it is a "new record" ;-)

Ronan

  • Full Member
  • ***
  • Posts: 132
Re: Null record problem workaround for MSSQL database
« Reply #4 on: July 19, 2011, 03:13:25 pm »
Quote
What datatype have your columns (NCHAR, NVARCHAR) ?


Combo_Name =  nchar(30)   (Primary Key)
Multiplier         =  float

That's all
 

Ronan

  • Full Member
  • ***
  • Posts: 132
Re: Null record problem workaround for MSSQL database
« Reply #5 on: July 20, 2011, 07:53:03 am »
Any comment will be apprecitated.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Null record problem workaround for MSSQL database
« Reply #6 on: July 20, 2011, 10:21:25 am »
Well, what Lacak2 mentioned: if you get an error that widestrings as parameters aren't supported, you might want to use regular ansistrings...

If you don't have that error, you could write a minimal test program that replicates the fault, create a bug in the bugtracker and attach the program.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Ronan

  • Full Member
  • ***
  • Posts: 132
Re: Null record problem workaround for MSSQL database
« Reply #7 on: July 20, 2011, 11:06:00 am »
Quote
Well, what Lacak2 mentioned: if you get an error that widestrings as parameters aren't supported, you might want to use regular ansistrings...
Where actually I'm passing this 0 as parameter?
Weirdness is that, I couldn't even grasp the idea of from that exception is originating, as a result nowhere in program I'm assigning the zero value, but it complains from that.

IMHO, based on my previous posts regarding the DB components configuration, nothing seems to be wrong except that columns data type definition.

Code: [Select]
Combo_Name =  nchar(30)   (Primary Key)
Multiplier         =  float

could that really be something wrong with it ?

Lacak2

  • Guest
Re: Null record problem workaround for MSSQL database
« Reply #8 on: July 20, 2011, 11:13:33 am »
Nothing is wrong in your table definition.
There is just missing support for "nchar" datatype.

I reported it as a bug http://bugs.freepascal.org/view.php?id=19773

So until this bug will be fixed you must chage your table definition (use instead of "nchar" simply "char" ... I do not know if it is acceptable, but ATM there is no other way)

Ronan

  • Full Member
  • ***
  • Posts: 132
Re: Null record problem workaround for MSSQL database
« Reply #9 on: July 21, 2011, 10:30:57 am »
Thank you all in advance,

Ok changing the data type of columns eliminates the problem for newly inserted records, But this time it throws the exception for existing records if I try to update them.

Quote
Couldn't execute statement. ODBC error details LastReturnCode :SQL_error; Record1 :SQLState :24000 Native Error Message Invalid Cursor State


To apply the updates I use
Code: [Select]
procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
SQLQuery1.ApplyUpdates;
//SQLTransaction1.Commit;  //
end;

Lacak2

  • Guest
Re: Null record problem workaround for MSSQL database
« Reply #10 on: July 21, 2011, 01:04:33 pm »
About your error:
"In SQL Server Enterprise Manager, you may receive the "Invalid cursor state" error message when Microsoft® SQL Server™ runs out of resources while attempting to save selected tables or a database diagram. This error is returned because of insufficient space in your database or transaction log to complete the save process. To correct this problem, check to see if whether the database or the transaction log is full. If so, increase the size of the database to accommodate the change."

Or if it is reproducible, then please submit test program which can show this error.

 

TinyPortal © 2005-2018