Bug - ODBC and stored-procedures (PB 4.2 up to 5.21)

[bobobo]

when dealing with a stored_procedure in MsSql , that creates (deletes beforehand if exits) a temptable
and fills it with data from a bunsch of tables with a cursor-controlled query, it results with a
connection via pb database odbc procedures in a not full result. some Datasets are missing.

when doing this with other tools (winsql, access, or PB with adomate and many others) it results
in a full dataset.

maybe a timingproblem with pb-database (odbc) ??

(this shortly depressed my confidence in pb-database-handling )

greetings

[fsw]

You are processing MsSQL stored-procedures with PureBasic?
I thought a SOAP implementation is needed for that.
(like GSOAP on Linux...)

[Frontier]

@bobobo: Can you share some code demostrating the bug?

[bobobo]

i guess that won't help .. you'll have to have a installed MS-SQL-instance (here it's a MSSQL2K),some tables and this (or this kind of) stored-procedures (collecting
data into a table with a cursor-processed query)

Then in PB it's a normal databasequery() btw. databaseupdate()
the databaseerror() results in No_Error

Never had problems with calling stored_procedures till now.
(but also never had to call a stored-procedure that collects data in this way )

i think it's some kind of timeout or a too little memoryarea somewhere in the
database-procedures.

ODBC-driver SQLSRV32.DLL (2000.82.1132.00 14.04.2008) Windows XP 32
(a compiled programm running on a win7 get the same incomplete result)

as written above: calling this stored-procedure in other odbc-based or ado-based
programms results in a complete table.

[bobobo]

i 've made some dislocations with creating a demo though

You need to have an Ms-SQL instance
I have a ms-sql200k herre (old but running)

creating tables with MS-Databasemanager , isql or something like that
f.i. you find the cool DbaMgr2k (0.21.1) (uses ado/ sql-dmo) somewhere in the depths of the net

Code: Select all

CREATE TABLE zz_test_b (
 "id"      int identity NOT NULL PRIMARY KEY,
  "b_no"   int,
  "r_name" varchar(255),
)
GO
CREATE UNIQUE INDEX b_no ON dbo.zz_test_b(b_no)
GO

create table zz_test_a(
  "id"           int identity NOT NULL PRIMARY KEY,
  "a_no"         int,
  "a_title"      varchar(50) NOT NULL,
  "a_name"       varchar(1024) NOT NULL,
  "a_callnumber" varchar(50),
  "a_b_no"       int
)

Go
CREATE UNIQUE INDEX a_idx_a_no ON dbo.zz_test_a(a_no)
GO

CREATE TABLE zz_test_ac (
  "id"          bigint identity NOT NULL PRIMARY KEY,
  "call_ni"     varchar(50),
  "ac_state"    bit,
  "ac_planned"  bit,
  "ac_last"     bit,
  "ac_dt"       datetime,
  "ac_b_no"     int,
  "ac_type"        int,
  "ac_noduty"        bit,
  "ac_changesource"   int,
  "ac_changedt"       datetime,
  "ac_change_a_no"    int,
  "ac_destcallno"     varchar(50),
  "ac_dest_a_no"      int,
  "ac_officeon"       datetime,
  "ac_officeoff"      datetime,
  "acd"               bit,
  "ac_dest_a_no_2"    int,
  "ac_dest_call_no_2" varchar(50)
)
GO

the stored_procedure (installed with the same tool like above)

Code: Select all

CREATE PROCEDURE _getitnow AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpitab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmpitab];
create table tmpitab (
ac_dt datetime,
a_title varchar(50),
a_name varchar(50),
a_no int,
a_b_no int,
b_no int,
r_name varchar(50),
ac_b_no int,
ac_dest_a_no int)
declare @tmpb as integer
declare tmpc CURSOR FOR (select b_no from zz_test_b)
open tmpc;
Fetch NEXT From tmpc Into @tmpb;
While @@FETCH_STATUS=0
BEGIN
insert into tmpitab
select top 1 ac.ac_dt,a.a_title,a.a_name,a.a_no,a.a_b_no,b.b_no,b.r_name,ac.ac_b_no,ac.ac_dest_a_no
from zz_test_a a left join zz_test_b b on a.a_b_no=b.b_no
left join zz_test_ac ac on a.a_no=ac.ac_dest_a_no
where b.b_no=@tmpb
and ac.ac_dt>getdate()
order by ac.ac_dt
Fetch NEXT From tmpc Into @tmpb;
END
CLOSE tmpc;
DEALLOCATE tmpc;
GO

now create an odbcconnection to the db and table



fill databases now purebasic-Code (takes some
time, i can't speed it up because my db is hot)

Code: Select all

UseODBCDatabase()
odbc_db.s="mydb"   ;your odbcconnection here
odbc_user.s="user" ;your odbcconnection here
odbc_pass.s="pass" ;your odbcconnection here

OpenDatabase(0,odbc_db,odbc_user,odbc_pass,#PB_Database_ODBC)

;acs
For i=1 To 150000
  d=Date()+Random(100000)+45000
  out.s+"insert into zz_test_ac values ('hnyuv_ntxwm_',1,0,0,'"+FormatDate("%hh:%ii %dd.%mm.%yyyy",d)+"',"+Str(Random(499)+1)+",1,0,9,'15:00 15.10.2013',999001,'0123 012345"+Str(i)+"',"+Str(Random(3000)+4500)+",NULL,NULL,1,NULL,NULL);"
  ii+1
  If ii>400 ;some collecting to speed it up
    Debug i
    ii=0
    DatabaseUpdate(0,out)
    try.s= DatabaseError()
    If tryold.s<>try
      Debug try
    EndIf
    tryold.s=try
    out.s=""
  EndIf
Next i
DatabaseUpdate(0,out)

Debug DatabaseError()


;regs
For i=1 To 500
  out.s="insert into zz_test_b values ("+Str(i)+",'bezreg_"+Str(i)+"');"
  DatabaseUpdate(0,out)
  try.s= DatabaseError()
  If tryold.s<>try
    Debug try
  EndIf
  tryold.s=try
  
Next i

;zoppos
For i=1 To 15000
  out.s="insert into zz_test_a values ("+Str(i+4500)+",'title_"+Str(i)+"','name_"+Str(i)+"','01234 5678910',"+Str(Random(499)+1)+");"
  DatabaseUpdate(0,out)
  try.s= DatabaseError()
  If tryold.s<>try
    Debug try
  EndIf
  tryold.s=try
  
Next i
CloseDatabase(0)
End

when calling the stored_procedure with the follwing code out of PB

Code: Select all

UseODBCDatabase()
odbc_db.s="mydb"   ;your odbcconnection here
odbc_user.s="user" ;your odbcconnection here
odbc_pass.s="pass" ;your odbcconnection here
OpenDatabase(0,odbc_db,odbc_user,odbc_pass,#PB_Database_ODBC)

SQL$ = "_getitnow"
DatabaseQuery(0,SQL$) ; databaseupdate doesn't change the resultset
While NextDatabaseRow(0)
  Debug "ui"
Wend
FinishDatabaseQuery(0)

CloseDatabase(0)

it results 91 Lines and that is wrong

When calling the stored_proc with pb and adomate or the tool above it results in 500 lines (witch seems to be correct)

[Frontier]

Tried your code with PB 5.21 and SQL Server 2008 R2.

Created tables and sp successfully.
Table population does not work for table zz_test_ac, the other two are populated fine (500 and 15000 records).
Please fix your code population code above so that we can continue testing.

Errors reported by MSSQL when inserting data to zz_test_ac

Code: Select all

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

[bobobo]

no error in creating testdata here ..

but try it with this code (no collection here and takes nore time to run, datetimefileds will be filled with correct datetimes)

Code: Select all

...
...
For i=1 To 150000
  d=Date()+Random(100000)+45000
  dd.s=FormatDate("%hh:%ii %dd.%mm.%yyyy",d)
  out.s="insert into zz_test_ac values ('hnyuv_ntxwm_',1,0,0,'"+dd+"',"+Str(Random(499)+1)+",1,0,9,'15:00 15.10.2013',999001,'0123 012345"+Str(i)+"',"+Str(Random(3000)+4500)+",'"+dd+"','"+dd+"',1,NULL,NULL);"
    DatabaseUpdate(0,out)
    try.s= DatabaseError()
    If tryold.s<>try
      Debug try
    EndIf
    tryold.s=try
    ii+1
    If ii>945
      Debug i
      ii=0
    EndIf
Next i
..
..

---
PB 522 results the same 91 lines in tmpitab versus 500 when running this sp in another tool
I think a timeout at some point and a bug

[HAnil]

is there a bug in PB 5.22 ?
when I connect to MSSQL stored procedure, no return value and sometime returns wrong numbers .
do you help me please ?
regards,


ex code:

Code: Select all

Procedure rSQL1(DataIsmi.s, RaporIsmi.s, CYil.s, CAy.i)
	Protected Sql.s, Key.w, RValue.i, RaporVarmi.i
	Sql = RaporIsmi+" @donem='"+CYil+"', @ay='"+Str(CAy)+"'"   ; this is stored procedure

	If DatabaseQuery(0,Sql)
		Debug DatabaseError()
		While NextDatabaseRow(0)
			RValue = GetDatabaseLong(0,0)
			MessageRequester("",Str(RValue))     ; waiting return 99 but no return no requester
		Wend
		FinishDatabaseQuery(0)
		ProcedureReturn #True
	Else
		; could not create the host, so log that
		ProcedureReturn #False          
	EndIf
	
EndProcedure 
If rSQL1(DataIsmi, RaporIsmi,BirOncekiYil,Counter)
  Debug #True
EndIf

[Fred]

I tried to reproduce without issue, can anyone else confirm ?

[bobobo]

playing arround with my testscenario from above in 6.02 the error is still there (only wheren using USEODBCDatabase())

i have to use the x86 as the database requires it

looks like a time out when the stored_procedure takes a longer time

greetings

[Fred]

A timeout might be possible, I will see if I can reproduce it here. How can we simulate a long stored proc ?

[bobobo]

select sleep(TIME) should do in my-sql

tested with maria db

created a procedure sleeper containing

SELECT SLEEP(10);

running it with

call sleeper

greetings

i thought it over:
select sleep(10),count(*) from EXISTING_TABLE
as procedure shows row 2 as result after the 10 seconds. the table must exist so a
SELECT SLEEP(10),COUNT(*) FROM information_schema.tables ;
could run as it is

with mssql

waitfor delay '00:01'
waits for 1 Minute to continue
or
waitfor delay '00:00:05'
waits 5 Seconds


BUT i cant replicate the error with the sleep or wait thing

i posted my testscenario above in this thread ..

[bobobo]

after some testing (6.02 x86) and mssql (old database)
i think it isn't a timeout, but more somethng that has to to with communications

a test with waitfor ... gets a result even after 1 minute pause.

my special test code posted above disconnects after shorter time , about 3 seconds if running the stored_procedure
i ave an select * from tmpitab at the end so there is valid output, in other clients it's fine
pb not

[bobobo]

i made another testscenarios similar to this above with mysql database and couldn't replicate this error
ok, long up to very long databaseactions but all ok .

6.02 and 5.24 both in 64bit and a 64bit Maria odbc connector

and i found a 32bit odbc driver , so i ran with an old pb 32bit 431 .. no error either

and that is ok

though this error above with mssql and stored procedure is still valid.