Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #5275 > unrolled thread

VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky

Started byFrans Wauters <info@wautersit.com>
First post2013-05-24 07:28 -0700
Last post2013-05-24 09:17 -0700
Articles 5 — 3 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Frans Wauters <info@wautersit.com> - 2013-05-24 07:28 -0700
    Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky "andrej Terkaj" <aterkaj@task.si> - 2013-05-24 16:53 +0200
    Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Malcolm G <malcolm-news2012delete@delete.jobstream.com> - 2013-05-24 17:15 +0100
      Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Frans Wauters <info@wautersit.com> - 2013-05-24 09:27 -0700
    Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Frans Wauters <info@wautersit.com> - 2013-05-24 09:17 -0700

#5275 — VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky

FromFrans Wauters <info@wautersit.com>
Date2013-05-24 07:28 -0700
SubjectVO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky
Message-ID<e08bf7ae-8fa1-44d3-b7de-04b64dff7b36@googlegroups.com>
Hi there,

I’m having a “spooky” problem. I can use INSERT to put in a new row, but when I request this using a SELECT, it does not find it. More, when I do a new INSERT, it seems to update the existent row. I know, spooky, but I when I do a select from SQL Server Management Studio, I see the inserted row and I also see that the content is updated after a new INSERT…

I'm updating some routines to VO2ADO to let my program running with the OLE-DB (VO2ADO), so clients don’t need to prep an ODBC configuration (standard VO SQL).

I create the connect string once from AdoDriverEdit(…) and save this connect string encrypted in a file. Other programs can read this file, decrypt de connect string and connect to the database. If the database does not exist, it will be created with all tables needed.

I create tables like :


FUNCTION SQL_CreateTable( oServer AS OBJECT, cTableName AS STRING, aSQLstruct AS ARRAY ) AS LOGIC

   LOCAL oAdoCommand AS AdoCommand
   LOCAL cStatement  AS STRING

   oAdoCommand := AdoCommand{}
   oAdoCommand:ActiveConnection := oServer
   cStatement := SQL_CreateTableString( cTableName, aSQLstruct )
   oAdoCommand:CommandText := cStatement
   oAdoCommand:Execute( NIL, NIL ,NIL )

   RETURN TRUE


In a nutshell, the content of the cStatement variable will look like this:

CREATE TABLE SYS_Users ( 
 [UserID] NUMERIC PRIMARY KEY IDENTITY, 
 [Account] varchar(32) not null default '', 
 [FirstName] varchar(32) not null default '', 
 [LastName] varchar(32) not null default '', 
 [Password] varchar(32) not null default '', 
 [Level] numeric not null default 0, 
 [ExpireDate] date )


or


CREATE TABLE SYS__VERSIE ( 
 [VERSIE] numeric not null default 0, 
 [DATUM] date )


and it creates my table.

My insert instruction:


METHOD pbn_Save( ) CLASS dlw_User

   LOCAL oAdoCommand AS AdoCommand
   LOCAL cStatement  AS STRING

   cStatement := "INSERT INTO SYS_USERS ( "
   cStatement := cStatement + "Account, "  
   cStatement := cStatement + "FirstName, "  
   cStatement := cStatement + "LastName, "  
   cStatement := cStatement + "Password, "  
   cStatement := cStatement + "Level, "  
   cStatement := cStatement + "ExpireDate ) VALUES ( "  
   cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
   cStatement := cStatement +       AllTrim( Str( oDCcbx_Level:VALUE ) ) + ", "
   cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) + "' )"

   oAdoCommand := AdoCommand{}
   oAdoCommand:ActiveConnection := gloServer
   oAdoCommand:CommandType := adCmdText
   oAdoCommand:CommandText := cStatement
   * gloServer:BeginTrans()
   oAdoCommand:Execute( NIL, NIL ,NIL )
   * gloServer:CommitTrans()

   SELF:EndDialog()

   RETURN NIL


In a the content of the cStatement variable will look like this:


INSERT INTO SYS_USERS ( Account, FirstName, LastName, Password, Level, ExpireDat
e ) VALUES ( 'FWA', 'Fxxxxx', 'Wxxxxxxxx', 'xxxxxxxxxx', 8, '20130621' )


My User test function. This function gives always a 0 (zero) for RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT * FROM SYS__Versie.


FUNCTION CheckUser( cUserName AS STRING ) AS LOGIC

   LOCAL oAdoRecSet  AS AdoRecordSet
   LOCAL oGebruiker  AS dlw_Gebruiker
   LOCAL cStatement  AS STRING

   cStatement := "SELECT * FROM SYS_Users WHERE Account='" + cUserName + "'"
   * cStatement := "SELECT * FROM SYS_Users"
   * cStatement := "SELECT * FROM SYS__Versie"

   oAdoRecSet := AdoRecordset{}
   oAdoRecSet:Open( cStatement, gloServer, adOpenForwardOnly, AdLockReadOnly, adCmdText )

   IF oAdoRecSet:RecordCount == 0
      oAdoRecSet:Close()
      lSuperUser := TRUE
      oGebruiker := dlw_Gebruiker{}
      oGebruiker:Show( SHOWCENTERED )
      RETURN FALSE
   ELSE
      lSuperUser := FALSE
      oAdoRecSet:Close()
   ENDIF

   RETURN TRUE



Thanks,

Frans

[toc] | [next] | [standalone]


#5276

From"andrej Terkaj" <aterkaj@task.si>
Date2013-05-24 16:53 +0200
Message-ID<knnut5$kg3$1@news.siol.net>
In reply to#5275
Hi Frans !

Probably, there is a mistake in a parameter adLockReadOnly --> try 
AdLockOptimistic in a method Adorecordset:Open()

-  You can try also with:
oSqlSrv := AdoServer{sStatement, oGL_Conn, adOpenForwardOnly, 
adLockOptimistic, ,TRUE}

oSqlSrv:Append()

oSqlSrv:FIELDPUT("FieldName_1",newValue)

I hope this will help !

Best regards Andrej Terkaj


"Frans Wauters" <info@wautersit.com> wrote in message 
news:e08bf7ae-8fa1-44d3-b7de-04b64dff7b36@googlegroups.com...
Hi there,

I’m having a “spooky” problem. I can use INSERT to put in a new row, but 
when I request this using a SELECT, it does not find it. More, when I do a 
new INSERT, it seems to update the existent row. I know, spooky, but I when 
I do a select from SQL Server Management Studio, I see the inserted row and 
I also see that the content is updated after a new INSERT…

I'm updating some routines to VO2ADO to let my program running with the 
OLE-DB (VO2ADO), so clients don’t need to prep an ODBC configuration 
(standard VO SQL).

I create the connect string once from AdoDriverEdit(…) and save this connect 
string encrypted in a file. Other programs can read this file, decrypt de 
connect string and connect to the database. If the database does not exist, 
it will be created with all tables needed.

I create tables like :


FUNCTION SQL_CreateTable( oServer AS OBJECT, cTableName AS STRING, 
aSQLstruct AS ARRAY ) AS LOGIC

   LOCAL oAdoCommand AS AdoCommand
   LOCAL cStatement  AS STRING

   oAdoCommand := AdoCommand{}
   oAdoCommand:ActiveConnection := oServer
   cStatement := SQL_CreateTableString( cTableName, aSQLstruct )
   oAdoCommand:CommandText := cStatement
   oAdoCommand:Execute( NIL, NIL ,NIL )

   RETURN TRUE


In a nutshell, the content of the cStatement variable will look like this:

CREATE TABLE SYS_Users (
 [UserID] NUMERIC PRIMARY KEY IDENTITY,
 [Account] varchar(32) not null default '',
 [FirstName] varchar(32) not null default '',
 [LastName] varchar(32) not null default '',
 [Password] varchar(32) not null default '',
 [Level] numeric not null default 0,
 [ExpireDate] date )


or


CREATE TABLE SYS__VERSIE (
 [VERSIE] numeric not null default 0,
 [DATUM] date )


and it creates my table.

My insert instruction:


METHOD pbn_Save( ) CLASS dlw_User

   LOCAL oAdoCommand AS AdoCommand
   LOCAL cStatement  AS STRING

   cStatement := "INSERT INTO SYS_USERS ( "
   cStatement := cStatement + "Account, "
   cStatement := cStatement + "FirstName, "
   cStatement := cStatement + "LastName, "
   cStatement := cStatement + "Password, "
   cStatement := cStatement + "Level, "
   cStatement := cStatement + "ExpireDate ) VALUES ( "
   cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "', 
"
   cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
   cStatement := cStatement +       AllTrim( Str( oDCcbx_Level:VALUE ) ) + 
", "
   cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) 
+ "' )"

   oAdoCommand := AdoCommand{}
   oAdoCommand:ActiveConnection := gloServer
   oAdoCommand:CommandType := adCmdText
   oAdoCommand:CommandText := cStatement
   * gloServer:BeginTrans()
   oAdoCommand:Execute( NIL, NIL ,NIL )
   * gloServer:CommitTrans()

   SELF:EndDialog()

   RETURN NIL


In a the content of the cStatement variable will look like this:


INSERT INTO SYS_USERS ( Account, FirstName, LastName, Password, Level, 
ExpireDat
e ) VALUES ( 'FWA', 'Fxxxxx', 'Wxxxxxxxx', 'xxxxxxxxxx', 8, '20130621' )


My User test function. This function gives always a 0 (zero) for 
RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT * 
FROM SYS__Versie.


FUNCTION CheckUser( cUserName AS STRING ) AS LOGIC

   LOCAL oAdoRecSet  AS AdoRecordSet
   LOCAL oGebruiker  AS dlw_Gebruiker
   LOCAL cStatement  AS STRING

   cStatement := "SELECT * FROM SYS_Users WHERE Account='" + cUserName + "'"
   * cStatement := "SELECT * FROM SYS_Users"
   * cStatement := "SELECT * FROM SYS__Versie"

   oAdoRecSet := AdoRecordset{}
   oAdoRecSet:Open( cStatement, gloServer, adOpenForwardOnly, 
AdLockReadOnly, adCmdText )

   IF oAdoRecSet:RecordCount == 0
      oAdoRecSet:Close()
      lSuperUser := TRUE
      oGebruiker := dlw_Gebruiker{}
      oGebruiker:Show( SHOWCENTERED )
      RETURN FALSE
   ELSE
      lSuperUser := FALSE
      oAdoRecSet:Close()
   ENDIF

   RETURN TRUE



Thanks,

Frans


[toc] | [prev] | [next] | [standalone]


#5278

FromMalcolm G <malcolm-news2012delete@delete.jobstream.com>
Date2013-05-24 17:15 +0100
Message-ID<UmMnt.48257$LC7.45593@fx06.am4>
In reply to#5275
> My User test function. This function gives always a 0 (zero) for RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT * FROM SYS__Versie.

interesting (I would expect you to get -1 some of the time
but zero is a surprise)

http://msdn.microsoft.com/en-us/library/windows/desktop/ms676701%28v=vs.85%29.aspx

The cursor type of the Recordset object affects whether the number of
records can be determined. The RecordCount property will return -1 for a
forward-only cursor; the actual count for a static or keyset cursor; and
either -1 or the actual count for a dynamic cursor, depending on the
data source.

[toc] | [prev] | [next] | [standalone]


#5280

FromFrans Wauters <info@wautersit.com>
Date2013-05-24 09:27 -0700
Message-ID<e7987bac-5cd5-4f33-bd5b-0d28a96dee7c@googlegroups.com>
In reply to#5278
Op vrijdag 24 mei 2013 18:15:47 UTC+2 schreef Malcolm G het volgende:
> > My User test function. This function gives always a 0 (zero) for RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT * FROM SYS__Versie.
> 
> 
> 
> interesting (I would expect you to get -1 some of the time
> 
> but zero is a surprise)
> 
> 
> 
> http://msdn.microsoft.com/en-us/library/windows/desktop/ms676701%28v=vs.85%29.aspx
> 
> 
> 
> The cursor type of the Recordset object affects whether the number of
> 
> records can be determined. The RecordCount property will return -1 for a
> 
> forward-only cursor; the actual count for a static or keyset cursor; and
> 
> either -1 or the actual count for a dynamic cursor, depending on the
> 
> data source.

Hi Malcom,

And 0 if there is no error and no row in the table. I found that a subroutine re-created the SYS_Users tabel in the INI section. I Fixed that and I found in the maintime several ways to add data using VO2ADO :)

Kind regards,
Frans

[toc] | [prev] | [next] | [standalone]


#5279

FromFrans Wauters <info@wautersit.com>
Date2013-05-24 09:17 -0700
Message-ID<d809b738-56f9-4e56-a1c1-2a1310fca119@googlegroups.com>
In reply to#5275
Hi Terki,

I tried your suggestion... In fact I have created 3 save (test)buttons:

METHOD pbn_Save1( ) CLASS dlw_Gebruiker 

   LOCAL oAdoRecSet AS AdoRecordSet
   LOCAL cStatement AS STRING
   
   cStatement := "INSERT INTO SYS_USERS ( "
   cStatement := cStatement + "Account, "  
   cStatement := cStatement + "FirstName, "  
   cStatement := cStatement + "LastName, "  
   cStatement := cStatement + "Password, "  
   cStatement := cStatement + "Level, "  
   cStatement := cStatement + "ExpireDate ) VALUES ( "  
   cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
   cStatement := cStatement +       AllTrim( Str( oDCcbx_Level:VALUE ) ) + ", "
   cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) + "' )"

   oAdoRecSet := AdoRecordset{}
   oAdoRecSet:Open( cStatement, gloServer, adOpenForwardOnly, adLockOptimistic, adCmdText )

   RETURN NIL

METHOD pbn_Save2( ) CLASS dlw_Gebruiker 

   LOCAL oUsers AS AdoServer

   oUsers := AdoServer{ 'SYS_USERS', gloServer, adOpenForwardOnly, adLockOptimistic, adCmdTable }
   oUsers:Append()
   oUsers:FIELDPUT( 'Account', AllTrim( oDCsle_Account:VALUE ) )
   oUsers:FIELDPUT( 'FirstName', AllTrim( oDCsle_FirstName:VALUE ) ) 
   oUsers:FIELDPUT( 'LastName', AllTrim( oDCsle_LastName:VALUE ) )
   oUsers:FIELDPUT( 'Password', AllTrim( oDCsle_Password:VALUE ) )
   oUsers:FIELDPUT( 'Level', oDCcbx_Level:VALUE ) 
   oUsers:FIELDPUT( 'ExpireDate', DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) )
   oUsers:Commit() 
   oUsers:Close()

   RETURN NIL

METHOD pbn_Save3( ) CLASS dlw_Gebruiker 

   LOCAL oAdoCommand AS AdoCommand
   LOCAL cStatement  AS STRING

   cStatement := "INSERT INTO SYS_USERS ( "
   cStatement := cStatement + "Account, "  
   cStatement := cStatement + "FirstName, "  
   cStatement := cStatement + "LastName, "  
   cStatement := cStatement + "Password, "  
   cStatement := cStatement + "Level, "  
   cStatement := cStatement + "ExpireDate ) VALUES ( "  
   cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
   cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
   cStatement := cStatement +       AllTrim( Str( oDCcbx_Level:VALUE ) ) + ", "
   cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) + "' )"

   oAdoCommand := AdoCommand{}
   oAdoCommand:ActiveConnection := gloServer
   oAdoCommand:CommandType := adCmdText
   oAdoCommand:CommandText := cStatement
   * gloServer:BeginTrans()
   oAdoCommand:Execute( NIL, NIL ,NIL )
   * gloServer:CommitTrans()

   RETURN NIL


All buttons write a row to the table. I think I will go for the pbn_Save2() way: less typing and more readable :)

As all buttons work like it supposed... I was thinking it is something else, so I debug all routines and found a wrong IF switch what recreated the SYS_Users tabel... Oeps, silly me.


Kind regards,
Frans

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web