Unable to select User Defined Types
Closed this issue · 11 comments
Since We are using user defined types in the database uepg/laravel-sybase fails to decide if this data types should or should not use quotes, this are not defined inside the $without_quotes array for example if I want to select * from table where data=1234 and data is a user defined DataType the QueryBuilder Will do something like Select * from table where data='1234' even if when Base DataType from data is integer, numeric or some other dataType that should not be Quoted.
is there anyway to declare a setter function that will allow me and everyone else to add more datatypes (user defined dataTypes) to the SybaseConnection.php class?
or a constructor that can load a diferent array from somewhere in the app an merge it to the $without_quotes array? in my short knowledge of Laravel I'm not capable yet of figure how it should be done
Something like
public function __construct() {
$this->without_quotes= array_merge($this->without_quotes, (arrae));
}
or something like
public function setWithoutQuotes($newArr){
$this->without_quotes= array_merge($this->without_quotes, ($newArr));
}
I'm investigating a solution, I will probably have to use some other identifier in the types than the name (some other field that determines "yes, this field will single quotes" or "no, this will not"). If this is not possible by syscollums and systypes or other system table, then I try to implement a solution to the developer overload types without quotes.
Time and again we have problems with types not documented and it definitely needs to be solved.
Great Thanks for the quick answer looking around a litte bit I found this Query:
select s1.name,
(select name
from systypes s2
where s2.usertype=(
select min( s3.usertype)
from systypes s3
where s3.hierarchy=s1.hierarchy)
) base_type, USER_NAME(s1.uid) as owner
from systypes s1 ;
in this Query s1.name is the name of the datatype can be uddt or legacy datatype and the base_type column is the datatype that it extends i'm sure you would have found it eventualy i'm just trying to help good Luck ahdn thanks again.
You can check if it worked?
sure i can check, but how should i Do it ? should i just run composer update ?? or copy paste the database/SybaseConnection File Content ?
added
Since I didn't know how to checked if it worked i did both ran composer update and it updated to version 12.0.0.2 it didn't work.
then I copied the actual SybaseConnection.php from Github into the server directly it Didn't work either
First Things First I’m sorry about the query I send you before it does not work properly, so since that didn't work properly I went and did some digging and searching find out about a system procedure in sybase that give you info about the database object you pass to it, you can call it doing sp_help UserDefinedDataType it also work with tables views and god know what else this system procedure code can be found in procedures on the sybsystemprocs database(in case you ever need it) so inside this procedure I Found the next query
/*
** Print help about a data type
*/
select Type_name = s.name,
Storage_type = st.name,
Length = s.length/ @lenfactor,
Nulls = s.allownulls,
Prec = s.prec,
Scale = s.scale,
Default_name = object_name(s.tdefault),
Rule_name = object_name(s.domain),
Access_Rule_name = object_name(s.accessrule),
Ident = s.ident
into #sphelp4rs
from systypes s, systypes st
where s.usertype = @typeid
and s.type = st.type
and st.name not in ("timestamp", "sysname", "longsysname", "nchar", "nvarchar")
and st.usertype < 100
And make this
select s.name,
st.name as type
from systypes s, systypes st
where s.usertype = @usertype
and s.type = st.type
and st.name not in ("timestamp", "sysname", "longsysname", "nchar", "nvarchar")
and st.usertype < 100
Long story short I Modified the SybaseConnection.php with some trial and error >.< and finaly it worked it at least the query containing theobject_name(a.id) = "'.$tables.'"'); at the end I really don’t know what does the other query do, the one with object_name(a.id, db_id('".$explicitDB[0]."')) = '".$explicitDB[1]."'"); so I'm Attaching the file so you can prove it, improve it, and make it look a lot prettier Thanks a lot for your help
Apparently, your query works correctly in joint with the previous query to identify types. Your last don't works with me.
Now, you can check the 1.2.0.3 version running composer update.
If don't fix, reopen this please!
The second Query does Work Correctly for my Sybase ASE version wich is 15.7, and the first Query Doesn't work correctly to my version it returns UDDT(user deffined data types) the first Query returned int as uint and varchars as tiny int the second query returned the int as int and the varchars as varchars i did the composer update and my fixes stoped working :P so it doesn't work for me could you pls check your Sybase ASE version? and also please check the Store Procedure sp_help in the database sybsystemprocs inside the procedures, sp_help UDDT will return a result set with Storage_type wich represent de base type of the UDDT,
i dont know how to reopen this bug report
Can you give me a query from a table that you are having problems!? Because we have succeeded in all our tests.
Our version is 15.7 too. I go check with our DBA your problem.
Ok I got this Query
SELECT s0.name,
s1.name as ChildType,
(SELECT name FROM systypes s2 noholdlock WHERE
s2.usertype=(SELECT min( s3.usertype)
FROM systypes s3 noholdlock WHERE s3.hierarchy=s1.hierarchy)) AS type
FROM systypes s1 noholdlock, syscolumns s0 noholdlock WHERE s0.usertype = s1.usertype AND object_name(s0.id) = 'viewname'
this returns the next resultset
name ChildType type
FE_NACIMIENTO Dm_Fecha datetime-> this is fine
FACULTAD Dm_Descripcion_250 tinyint-> should be a varchar( 250)
ESCUELA Dm_Descripcion_250 tinyint-> should be a varchar( 250)
DA_SEXO char char ---------------------------> this is fine
CEDULA Dm_Cedula uintn--------------------->should be a int
NOMBRE Dm_Descripcion_250 tinyint-------> should be a varchar( 250)
UBICACION Dm_Ubicacion bit------------------> should be a char(6)
ANO Dm_Ano bigint----------------------------------> this should be smallint
PERIODO Dm_Periodo bit ------------------> should be a char
ESTATUS char char
DESCRIPCION Dm_Descripcion_30 tinyint-> should be a varchar(30)
Now running this query which I made after looking inside the code of sp_help
select a.name,
b.name as ChildType,
(select st.name
from systypes s, systypes st
where s.usertype = b.usertype
and s.type = st.type
and st.name not in ("timestamp", "sysname", "longsysname", "nchar", "nvarchar")
and st.usertype < 100 ) AS type FROM syscolumns a noholdlock
JOIN systypes b noholdlock ON a.usertype = b.usertype and object_name(a.id) = "viewname"
I get
name ChildType type
FE_NACIMIENTO Dm_Fecha datetime-------------> this is fine
FACULTAD Dm_Descripcion_250 varchar--------> this is fine
ESCUELA Dm_Descripcion_250 varchar--------> this is fine
DA_SEXO char char-------------------------------------> this is fine
CEDULA Dm_Cedula int----------------------------------> this is fine
NOMBRE Dm_Descripcion_250 varchar--------------> this is fine
UBICACION Dm_Ubicacion char------------------------> this is fine
ANO Dm_Ano smallint---------------------------------------> this is fine
PERIODO Dm_Periodo char------------------------> this is fine
ESTATUS char char-------------------------------------------> this is fine
DESCRIPCION Dm_Descripcion_30 varchar-> this is fine
From The content of sp_help I Get This
/*
** Print help about a data type
*/
select Type_name = s.name,
Storage_type = st.name,
Length = s.length/ @lenfactor,
Nulls = s.allownulls,
Prec = s.prec,
Scale = s.scale,
Default_name = object_name(s.tdefault),
Rule_name = object_name(s.domain),
Access_Rule_name = object_name(s.accessrule),
Ident = s.ident
into #sphelp4rs
from systypes s, systypes st
where s.usertype = @typeid
and s.type = st.type
and st.name not in ("timestamp", "sysname", "longsysname", "nchar", "nvarchar")
and st.usertype < 100
exec sp_autoformat @fulltabname = #sphelp4rs,
@selectlist = "Type_name, Storage_type,Length,Prec,Scale,Nulls,Default_name,Rule_name,Access_Rule_name,'Identity' = Ident"
drop table #sphelp4rs
return (0)
end -- }
That is part of the sp_help store procedure from Sybase
if you do sp_help Dm_Cedula u get
Type_name Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity Dm_Cedula int 4 NULL NULL 0 0
I Really find it very hard to believe that sybase got this wrong, you can ask your dba for the full content on the sp_help procedure, it can be found on the sybsystemprocs database inside your server if you use Sybase Central maybe you can see it too
Its a view or a normal table? I want to believe strongly that the problem is because it is a view.
In any case, we will produce your table in our environment and come back with the results.
Thinking it may be view Related I did the select one of the tables of the view, and even when the base dataType(char, varchar, int) returned the right data Type, the UDDT(any custom dataType) still don't return what it should.
I have Mention several times how ASE manage to always get the right base Data Type from the UDDT with the sp_help procedure, also paste part of this procedure so you can see that it works correctly maybe the procedure is different on your database, but i don't see how or why it should be! ¿could you please check the procedure sp_help? for now I’ll lock my composer.json to version 1.2.0.3 and try to keep the file I Send you the same, because the one I send you it’s working for me I wish I could be more helpful but I don’t know what else can I do, I even show one of my views which I Shouldn’t have done, I hope We can fix this soon enough any other thing you need just let me know, and again thanks a lot for your help