Sybase ASE using ODBC DB->schema

[Marcel]

Hi,

I am using ODBC driver to connect to Sybase ASE v.15. I am unable to create schema and use Mapper function.

In lib/db/sql.php on line 234 is query for information_schema metadata. But Sybase ASE do not have information_schema system table and function is on working.

I was trying to create something similar to output of this procedure. 

https://dev.mysql.com/doc/refman/5.0/en/show-columns.html

This is result of closes I could get:

Using Sybase infocenter: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1530/doc/html/san1278453250070.html

The query should populate these fields but I do not understand the field 'YES' and ,'PRIMARY KEY'

'field','type','defval','nullable','YES','pkey','PRIMARY KEY'

____________________________________________________________________

select distinct c.name as field, 

case 

    when t.name = 'sysname'     then 'varchar'

    when t.name = 'nvarchar'    then 'varchar'

    when t.name = 'longsysname' then 'varchar'

    when t.name = 'varbinary'   then 'timestamp'

    when t.name = 'nchar'       then 'char'

    else t.name

end as xtype

, null as defval ,

case

    when convert(bit, (c.status & 8))=0 then 'NO'

    when convert(bit, (c.status & 8))=1 then 'YES'

end as nullable, 

case 

    when c.colid = i.indid then 'PRI'

end as pKey,c.colid,i.indid

from syscolumns c 

INNER JOIN systypes t

on c.type = t.type

RIGHT OUTER JOIN sysindexes i

    on i.id = (select o.id from sysobjects o where name = '.($table).' and o.type = 'U')

where c.id =  (select o.id from sysobjects o where name = '.($table).' and o.type = 'U')

____________________________________________________________________

The setting of primary key is wrong if there is more then one key on table. 

Can you please help me to correct this query and help me to put updated query into lib/db/sql.php. Will the mapper function for DBS working after these steps ?

[ikkez]

well i've tested odbc drivers along with a sql server backend a while ago, but hardly failed due to some odbc driver issues with PDO bind parameters. I don't know if that also exists for other backends or if they are patched already, but you can give it a try and we'll see.

The query should populate these fields but I do not understand the field 'YES' and ,'PRIMARY KEY'

'field','type','defval','nullable','YES','pkey','PRIMARY KEY'

"YES" is the value that determines the if the field is nullable or not. The same applys to "PRIMARY KEY" for the "pkey"
 In other words: these are the results from the schema query that shows us if the appropriated field-properties (is nullalbe, is primary-key) are true or not.

[Marcel]

Thanks ikkez,

Can you be more specific

field - field name (id, name...)

type - datatype (int,varchar...)
defval - default value (NULL, 1,2, 'ABC')
nullable - if nullable value YES if not then NO (YES, NO)

YES - if nullable value YES if not then NO (YES, NO)
pkey - If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY. If Key is UNI, the column is the first column of a unique-valued index that cannot contain NULL values. If Key is MUL, multiple occurrences of a given value are permitted within the column. The column is the first column (PRI, UNI, MUL)
PRIMARY KEY - if primary key value YES if not then NO (YES, NO)

Is this correct ?

[Marcel]

How can I put condition into function schema that will detect that I am using {Adaptive Server Enterprise} and the function should run another query.

Database token.
$db=new \DB\SQL('odbc:Driver={Adaptive Server Enterprise};app=SYBAPP;server=192.168.1.xxx;port=xxx;db=dbtable','user','password');

[ikkez]

this array contains the schema query at index [0] and all further elements are the names of the columns that describe certain things:
 

column-name-that-contains-fieldname - field name (id, name...)

column-name-that-conains-type - datatype (int,varchar...)
column-name-of-the-default-value - default value (NULL, 1,2, 'ABC')
column-name-of-the-nullable-value - whatever your information schema tells you about this

nullable-true-value - the value which makes nullable = TRUE (if your nullable column contains YES or NO, you shouldwrite YES here)
column-name-of-pkey - so you say this could contain PRI, UNI, MUL... okay.
pkey-value - this is "PRI" in your case (as you said "If column-name-of-pkey is PRI, the column is a PRIMARY KEY ")

check this line to understand:

https://github.com/bcosca/fatfree/blob/dev/lib/db/sql.php#L310

 

[Marcel]

I have found solution. Special query need to run to create such a table for selected database. This query will create table with name information_schema_columns this table will need to be recreated every time new table or view is created in database. Rerun this query if you add new table or view or if change schema of columns. When you rerun query new stuff will be working with F3 mapping function. 

In file lib/sql/sql.php needs to be added following:

'odbc'=>array(

'select * from information_schema_columns where name='.$this->quote($table).

';',

'field','type','defval','nullable','1','pkey','PRI'), 

/*

This will query information_schema_columns table. 

I do not know how to set it directly for odbc:Driver={Adaptive Server Enterprise} and that's why I have deleted odbc from following line to make it work:

'mssql|sqlsrv|sybase|dblib|pgsql'=>array(

Note: I have not tested but this work flow should work for Sybase IQ and SQL Anywhere as well. 

*/

Query to create information_schema_columns 

drop table #pkindcols

create table aaa ( i int, j int, c char(35), primary key ( i, j ) )

select i.name colname, i.id, i.indid, i.keycnt, o.name objname,

       convert( varchar(255), "" ) cols

  into #pkindcols

  from sysindexes i, sysobjects o

  where i.id=o.id AND status&2048=2048

create unique index pkindcols_idindid on #pkindcols ( id, indid )

go

declare colnames

  cursor for

  select id, indid, keycnt, objname

    from #pkindcols

  for update

go

declare @i int, @id int, @indid int, @keycnt int, @objname char(30), @txt varchar(30)

open colnames

fetch from colnames into @id, @indid, @keycnt, @objname

while ( @@sqlstatus = 0 )

 begin

  select @i=1

  while ( @i <= @keycnt )

   begin

    select @txt=ltrim(rtrim(index_col( @objname, @indid, @i )))

    select @txt

    update #pkindcols set cols=cols+ltrim(rtrim(index_col( @objname, @indid, @i )))+"|"

      where indid=@indid AND id=@id

    select @i=@i+1

   end

  fetch from colnames into @id, @indid, @keycnt, @objname

 end

close colnames

deallocate colnames

select cols, * from #pkindcols

go

drop table information_schema_columns

select distinct o.name, c.name as field,

case 

   when t.name = "sysname"     then "varchar" 

   when t.name = "nvarchar"    then "varchar" 

   when t.name = "longsysname" then "varchar" 

   when t.name = "varbinary"   then "timestamp" 

   when t.name = "nchar"       then "char" 

   else t.name 

end as type,null as defval, 

convert(bit, (c.status & 8)) as nullable,

case when p.cols like "%"+c.name+"%" then "PRI" end as pkey 

into information_schema_columns

from sysobjects o 

INNER JOIN syscolumns c

    on c.id = o.id

INNER JOIN systypes t 

    on c.type = t.type 

LEFT OUTER JOIN #pkindcols p 

    on p.id = o.id

where (o.type = 'U' or o.type = 'V')