Page 1 of 1
Database question
Posted: Mon Sep 22, 2014 7:14 am
by 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?
Re: Database question
Posted: Mon Sep 22, 2014 7:48 am
by 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
Re: Database question
Posted: Mon Sep 22, 2014 8:07 am
by roleg
citystate wrote: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!
Re: Database question
Posted: Mon Sep 22, 2014 1:54 pm
by TI-994A
roleg wrote:...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.
