|
| Re: WXReplication and SQL Server |
| Iniciado por guest, 23,jul. 2015 08:10 - 11 respuestas |
| |
| | | |
|
| |
| Publicado el 23,julio 2015 - 08:10 |
Hello,
I modified the WXReplication module by Fabrice Harari to access SQL Server database. Added the following connection method, if SQL Server
:m_cnxHFCSRealConnection..Provider = "SQLOLEDB"
:m_cnxHFCSRealConnection..User = INIRead("DB","User","sa",:m_ccsIniFile)
:m_cnxHFCSRealConnection..Password = INIRead("DB","Password","a123#",:m_ccsIniFile)
:m_cnxHFCSRealConnection..Server = INIRead("DB","Server","localhost",:m_ccsIniFile)
:m_cnxHFCSRealConnection..Database = INIRead("DB","DBName",cstDBName,:m_ccsIniFile)
//:m_cnxHFCSRealConnection..CryptMethod = hCryptRC5_16
:m_cnxHFCSRealConnection..Access= hOReadWrite
//MyConnection..ExtendedInfo = "CRYPT=RC5_16"
IF HOpenConnection(:m_cnxHFCSRealConnection)=False THEN
Info(HErrorInfo)
:SetHError()
RESULT False
END
It connects and works fine as far as new record (inserts) are concerned. I get error if I edit the same record, after WXREngine sends that data. ie I edit one record (tried with color file) and WXREngine sends that data and I again edit the same record, It doesn't get saved, instead the following error appears. The following error I get
Error at line 23 of Method hModify Process.
HModify function called.
OLE DB access error.
Error Number = 170147
Faiulure updating record in database
System error details:
Description = Query timeout expired
Source = Microsoft OLE DB Provider for SQL Server
SQL State = HYT00
Error Number = -2147467259 (0x800004005)
Description = Multiple-step OLE DB operation generated errors.Check each OLE DB status value, if available. No work was done.
Source = Microsoft OLE DB Provider for SQL Server
Error Number = -2147217887 (ox80040e21)
and the software quits. Hope some locking is happening on that particular record (by WXREngine) that prevents the WXReplication screen to update the record.
If I edit the same record, before the WXREngine sending the packets, there is no such problem.
As I'm new to WX products, I couldn't locate the error, which is causing this problem. Any suggestion to solve this problem is really appreciable.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,julio 2015 - 12:07 |
Hi
I haven't tested in that configuration, so everything I'm telling you here is pure speculation.
I have in the past encountered the case of an OLD DB error 170147 on a hmodify on MSSQL. It was coming from the fact that TWO hmodify were made on the same record (seems that it matches your problem) without READING the record again between the two.
I have never found an explanation for this behavior of the OLD DB access, but a simple hread of the record before the second edit may solve the problem.
And in that case, no change would be required in wxreplication, as the problem would lay in the logic of your application.
Best regards, and please keep us posted. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,julio 2015 - 12:57 |
Hello Fabrice,
Thanks for your time and reply.
Without trying the the solution you mentioned, I'm given this reply. This problem doesn't occur when I try to edit the same record continuously (any no. of times, but I tried around 20-25 times), but only occurs after WXREngine sends the modified data. Once the packets are send successfully, if I try to edit the record, it gives the error at hModify.
While it tries to save the record, if WXREngine works again, it correctly updates the data, without any error.
Let me try the solution you mentioned, in the mean time.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,julio 2015 - 13:23 |
Hello Fabrice,
Btw, I'm using only your demo package, particularly color table alone for my test. Just switched the database to sql server. That's all.
Added new color, tried to modify the entered color. That's all.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,julio 2015 - 14:19 |
Hi again
now that's mighty strange... the engine does NOT touch (read, modify or anything else) the MAIN file (color file in your case) AT ALL... it works only on the WXReplication file. So the engine running or not has NO effect on the COLOR file... The engine reads the wxreplication file and writes in it, that's all.
Could you tell me :
- where the error is happening (in the engine, or in the demo app)?
- WHERE the error is happening ? (we know it's in hmodify, but what code is doing the hmodify and on what file)
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,julio 2015 - 15:14 |
Hello Fabrice,
Let me give the full detail. I"m using 1.1 version in Windev 19, with the following modifications done to accommodate SQL Server.
1. Object clUD_Replication has been added with initDB method. A case for 'SQL Server' has been added to connect to a SQL Server. (code in my first message).
2. Window w_WXRInitWizard modified to accept a third option (in Plane 3) for SQL Server. Captions in Plane 5 also modified to reflect these changes.
Above changes done in both projects WXReplication and WXREngine.
And now to your question.
1. Error happens in WXReplication project, when I modify the color and validate it.
1a. If WXREngine is on auto update mode, and the engine is creating packets and sending it, the data gets saved without any error.
1b. Even if error is there, I could see the updated values in color table.
2. Stack
Method hModify (clWXR_DB.hModify), line 23
Method hModify (clWXR_Replication.hModify), line 55
Global Procedure hModify (Global_WXRProcedures.hModify), line 16
Click BTN_Validate (w_ColorForm.BTN_Validate), line 14
Click BTN_Edit (w_FileInspector.BTN_Edit), line 33
Selecting the menu of _Menu.OPT_File.OPT_File_Inspector (w_Home._Menu.OPT_File.OPT_File_Inspector), line 1
Line no. 32 of clWXR_DB.hModify, in project WXReplication, points to the line
IF WL.HModify(ccsFileName)=False THEN
This makes to think the color file is creating the problem. That's what I can infer from this error message.
As I can visualise and from your message, the WXREngine is not touching the color file. Also the color file's data is getting updated even when the error occurs. Also if the update happens when the data is sent by WXREngine, this error doesn't occur.
And I developed the application in Powerbuilder to enter, edit color file (table). The same error occurs.
So, I don't think it's a problem with OLE DB driver, but some lock is applied to record(s) by WXREngine, while reading the WXReplication file, which doesn't get released. Since Sql Server 2000 goes for page locking, another application couldn't insert / read a particular record, since that record is not released by WXREngine. This is my guess.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,julio 2015 - 17:30 |
Hi again,
your changes are logical and where they should be, so I doubt the problem is there (maybe in the parameters used for the OLEDB connexion)...
However, the reason why this is happening eludes me... From what you are telling me:
- The errors occurs on a hmodify of the main file (color).
- The errors occurs only when the engine is running
BUT, the engine is not reading or writing in the color file at all, and is just reading and writing in wxreplication...
So, except if the OLEDB connexion would somehow block access to all files as soon as there is a concurrent usage of the DB, I fail to see what could be causing the problem...
And if that's the case, then it's easy to test:
- Create the exe for WXReplication
- run it and change things in color, as you have been doing
- run a SECOND instance of WXRplication and dowhatever you want in it
- try to edit a color in the first instance.
If, as I suspect, you get the same error, than either your MSSQL DB accepts only ONE connexion, or the parameters you are using for your connexion are incorrect.
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,julio 2015 - 18:56 |
Hi guys,
If I remember right, record blocking is not working with OLE DB. (But I'm no expert on this subject.) I think with native access this is somewhat possible.
If you are using HTransaction*() then in OLE DB all files are in transaction at once.
Best regards,
Alexandre Leclerc |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,julio 2015 - 07:57 |
Hello Fabrice,
//
However, the reason why this is happening eludes me... From what you are telling me:
- The errors occurs on a hmodify of the main file (color).
- The errors occurs only when the engine is running
No. After the engine runs (atleast) once, this problem occurs. Before that, it works fine.
As far as the OLE DB parameters are concerned, I've given the connection details in my first message.
And the same problems occurs when I try to update records from Powerbuilder. I'm using Powerbuilding, SQL Server and OLE DB for the past 5 years, without any trouble in multi user environment.
I'll try to update records by different users. Let me give you the result.
Btw, I've totally forgot to mention one more changes I've made. In MakeRecordContent method of clWXR_Replication, on edit of a record, an alias is made to check with old values. There the following HReadSeek is giving error. So I commented it out to proceed further.
IF HReadSeek(ccsAlias,"usGuid"+ccsFileName, ccsFileName+".usGuid" + ccsFileName,indItem}, hIdentical) = False THEN
::m_usErrorinfo="Original record not found in the database. Modification is impossible."
RESULT ""
END
Error at line 35 of Method MakeRecordContent process.
HReadSeek function called.
OLE DB access error.
Error Number = 170124
Failure opening connection:
Data Source: <E:\WX 19 Test\WX Replication 1.1\WD_WXReplication\Exe\Executable\WebMobile\SQL2000>
OLEDB Provider: <SQLOLEDB>
System Error Details:
Description = Invalid connection string attribute
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 01S00
Error Number = -2147467259 (0x80004005)
Description = [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 08001
Error Number = -2147467259 (0x80004005)
Native Error Number = 17 (0x11)
----- Technical Information -----
Project : WXReplication
WL call:
Process of 'Method MakeRecordContent' (clWXR_Replication.MakeRecordContent), line 35, thread 0
'HReadSeek' function, syntax 1
What happened?
OLE DB access error.
Error Number = 170124
Failure opening connection:
Data Source: <E:\WX 19 Test\WX Replication 1.1\WD_WXReplication\Exe\Executable\WebMobile\SQL2000>
OLEDB Provider: <SQLOLEDB>
Error code: 73001
Level: fatal error (EL_FATAL)
WD55 error code: 3001
System error message:
Description = Invalid connection string attribute
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 01S00
Error Number = -2147467259 (0x80004005)
Description = [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 08001
Error Number = -2147467259 (0x80004005)
Native Error Number = 17 (0x11)
Dump of the error of 'WD190HF.DLL' module (19.0.102.2).
Identifier of detailed information (.err): 72801
Debugging information:
IEWDOLDB=105.3
Module=<WDOLDB>
Version=<19.0.14.0>
Provider: SQLOLEDB
User: sa
Data source: E:\WX 19 Test\WX Replication 1.1\WD_WXReplication\Exe\Executable\WebMobile\SQL2000
Database: WXReplication_SQLServer
Unicode supported: 1
Page code of WL: 1252
Page code of the connection: UTF-16
MDAC Version = <6.1.7601.17514>.
[Data format]
Query Parameter Checked = <0> = <>
Query Parameter Needing conversion = <96> = <BOOLDECIMAL>
DecimalSeparator = <Undefined>
DateFormat = <Undefined>
[Cursor settings Match]
LockType = <adLockUnspecified>, Location = <adUseNone>, Type = <adOpenUnspecified>, Capacities = <ffffffff>
LockType = <adLockUnspecified>, Location = <adUseNone>, Type = <adOpenUnspecified>, Capacities = <ffffffff>
LockType = <adLockUnspecified>, Location = <adUseNone>, Type = <adOpenUnspecified>, Capacities = <ffffffff>
Fonction (7,118)
Additional Information:
EIT_NATIVECODE : <170124>
EIT_ADOCODE : <-2147467259>
EIT_BASECODE : <17>
EIT_ODBCDESCRIPTION : <Invalid connection string attribute>
EIT_ODBCCODE : <01S00>
EIT_LOGICALTABLENAME : <AliasColor>
EIT_PILEWL :
Method MakeRecordContent (clWXR_Replication.MakeRecordContent), line 35
Method hModify (clWXR_Replication.hModify), line 42
Global Procedure hModify (Global_WXRProcedures.hModify), line 16
Click BTN_Validate (w_ColorForm.BTN_Validate), line 14
Click BTN_Edit (w_FileInspector.BTN_Edit), line 33
Selecting the menu of _Menu.OPT_File.OPT_File_Inspector (w_Home._Menu.OPT_File.OPT_File_Inspector), line 1
EIT_DATEHEURE : 24/07/2015 11:24:42
Help
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,julio 2015 - 07:58 |
Hello,
Any parameter to set isolation level in the OLE DB Connection ?
We used to set it to Read Committed in our powerbuilder applications. Any equivalent here ?
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,julio 2015 - 12:33 |
Hi,
Now we are going somewhere!
Quote
Sivaprakash
Btw, I've totally forgot to mention one more changes I've made. In MakeRecordContent method of clWXR_Replication, on edit of a record, an alias is made to check with old values. There the following HReadSeek is giving error. So I commented it out to proceed further.
IF HReadSeek(ccsAlias,"usGuid"+ccsFileName, ccsFileName+".usGuid" + ccsFileName,indItem}, hIdentical) = False THEN
::m_usErrorinfo="Original record not found in the database. Modification is impossible."
RESULT ""
END
Error at line 35 of Method MakeRecordContent process.
HReadSeek function called.
OLE DB access error.
Clearly, there is NO WAY the replication system is going to work if the system cannot read the the original record with the halias.
So, you need to create a small test code in which you :
- read and modify a record in color file
- create a halias of color file
- read the same record in the halias
I checked in the help and halias is supported in OLEDB, (but not in ODBC).
You'll need to find out why it's creating this error, and change what needs to be changed, and I unfortunately cannot help you at that level, as I've never used OLE DB with MSSQL, so I don't know what connection settings you need to use
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,julio 2015 - 13:56 |
Hello Fabrice.
Two problems.
1. hAlias. It worked for color table, even after those lines are commented out. But with another problem (timed out).
2. Timed out problem. It looks like a locking problem for me. And I found a reply given by another member to another thread. His reply (solution) is
/////////////////////////////////////////
Re: MSSQL OLEDB How to lock a file? #
Posted by: Thomas Brauner
Date: June 24, 2014 07:40PM
Thanks a lot for all the answers!
It seems, that the locking-problem ist interesting for many people.
@Paulo:
until Version 18, the transaction was not able with Connection string. Thanks to your post, i found out that with 19 the transaction concept has been changed.
And you are right! With the following code it works even under OLEDB:
hTransactionStart(connid)
hReadFirst(Testfile, hLockReadWrite)
testfile.nr += 1
hModify(testfile)
hTransactionEnd()
But now the Goal!
While testing in conjunction with my good old dataflex Programm i found out, that even under OLEDB the hReadFirst(Testfile, hLockReadWrite) command LOCKS the file!
But either hUnlockFile() nor hUnlockRecord() frees this lock.
It's a bug or a feature????
Instead of hUnlockFile, i tried
hReadFirst(Tstfile, hNoLock)
AND IT WORKS! UNDER OLEDB!
BTW. If Windev 19 seems to be stable (not yet!!!), i will prefer the transaction code.
Thanks again to all the community!
Thomas
/////////////////////////////////////////
So my assumption is that HReadSeek may also be locking the record (?), but not sure it's color file or wxreplication file. Should find out. That's why an hModify, during the data transmission by WXREngine, is successful. Somewhere those record are re-read which releases the lock. And an hModify after that fails.
Will verify these in the coming days.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | | | |
| | |
|