Database question

[roleg]

Hi all!
I'm try some functionality of PB 5.X to use ODBC conection to my MSSQL database and I don't see function to retrieve num rows affected. Only I see in the help is AffectedDatabaseRows, but it's for DatabaseUpdate() only.
I need retrieve num rows returned from DatabaseQuery() command.

Like:

Code: Select all

If Not UseODBCDatabase()
  MessageRequester("Ошибка","Драйвер для работы с базой данных ODBC, не обнаружен")
  End
EndIf
  
db = OpenDatabase(#PB_Any, "ASKU", "sa", "123456")
If Not db
  MessageRequester("Ошибка","Неудачное подключение к базе данных Altair_DB")
EndIf

If DatabaseQuery(db, "SELECT Kod FROM Tabl_C WHERE Num485=123")
    While NextDatabaseRow(db)
      counter.i=GetDatabaseLong(db, 0)
      Debug counter.i
    Wend
    FinishDatabaseQuery(db)
EndIf

how many rows will be returned and how can I find out?

[citystate]

probably the easiest way is with the following:

Code: Select all

If DatabaseQuery(db, "SELECT COUNT(*) FROM Tabl_C WHERE Num485=123")
    While NextDatabaseRow(db)
      counter.i=GetDatabaseLong(db, 0)
      Debug counter.i
    Wend
    FinishDatabaseQuery(db)
EndIf

[roleg]

probably the easiest way is with the following:

Code: Select all

If DatabaseQuery(db, "SELECT COUNT(*) FROM Tabl_C WHERE Num485=123")
    While NextDatabaseRow(db)
      counter.i=GetDatabaseLong(db, 0)
      Debug counter.i
    Wend
    FinishDatabaseQuery(db)
EndIf

but I need retrive Kod and others columns in this query
OK, I think the way is use two separate queries

Thanks!

[TI-994A]

...how many rows will be returned and how can I find out?

Hi roleg. Based on the example you posted, perhaps you could try this approach:

Code: Select all

;dummy structure and variables for example purposes only
;replace with actual field structure of your database

Structure databaseFields
  Kod.l
  Num485.l
  name.s{50}
  addr.s{50}
EndStructure

Dim results.databaseFields(0)

If Not UseODBCDatabase()
  MessageRequester("Error", "Driver for the database ODBC not found")
  End
EndIf

db = OpenDatabase(#PB_Any, "ASKU", "sa", "123456")
If Not db
  MessageRequester("Error", "Failed to connect to the database Altair_DB")
  End
EndIf

If DatabaseQuery(db, "SELECT * FROM Tabl_C WHERE Num485=123")
  While NextDatabaseRow(db)
    ReDim results(counter)
    results(counter)\Kod = GetDatabaseLong(db, 0)
    results(counter)\Num485 = GetDatabaseLong(db, 1)
    results(counter)\name = GetDatabaseString(db, 2)
    results(counter)\addr = GetDatabaseString(db, 3)
    counter + 1
  Wend
  FinishDatabaseQuery(db)
EndIf

Debug "Total records found: " + counter
Debug ""
For loop = 0 To ArraySize(results())
  Debug "record #" + Str(loop + 1)
  Debug " Kod: " + results(loop)\Kod
  Debug " Num485: " + results(loop)\Num485
  Debug " Name: " + results(loop)\name
  Debug " Addr: " + results(loop)\addr
  Debug ""
Next

Just a suggestion.