By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Help with MSSQL/PHP

P: n/a
Hi wondering if anyone can help.

What i'm trying to do is get a company from a MSSQL database with the
COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want to
reference it to another table (on same database) to extract more details.

When I get the company name and company no the company no outputs like...
63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?

I wanted to reference another table so I...
$query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
COMPANYNO='$companyno'";

And of course this wouldn't work and the error was:

Warning: Sybase message: Syntax error converting from a character string to
uniqueidentifier.

I need help with this as binary/hex sometimes goes over my head. If anyone
can point me in the right direction that would be great. I've had this same
problem before and did a crappy 'work around' - now i've realised why I did
the work around!

Thanks a lot for ANY help at all :)

(if nothing makes sense please say so :))

Darren
Jul 17 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <ze**@zeen.co.uk>
wrote:
Hi wondering if anyone can help.

When I get the company name and company no the company no outputs like...
63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?


So really, you need to know what datatype that field is?

mysql_fetch_field($result) should get you an object holding the
appropriate information.

Jul 17 '05 #2

P: n/a
> >When I get the company name and company no the company no outputs like...
63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?


So really, you need to know what datatype that field is?

mysql_fetch_field($result) should get you an object holding the
appropriate information.


No no, i want to use the first SQL output of the GUID into another SELECT
and i get the error..

Warning: Sybase message: Syntax error converting from a character string to
uniqueidentifier.

I'm not sure how to use '63180001000000000000000000000000' GUID output into
another SQL SELECT.

(sorry for emailing you directly a second ago, haven't used newsgroups for
ages :P)
Jul 17 '05 #3

P: n/a
I have been looking at...

http://uk.php.net/manual/en/function...uid-string.php

But when using mssql_guid_string i get...

Fatal error: Call to undefined function: mssql_guid_string() in
/export/home/darrenm/web/scripts/guid.php on line 32.

php-4.3.1
freetds-0.61

and yes it was compiled with --enable-msdblib --with-tdsver=7.0

I guess this is the function i need!

If i do...

$query = "SELECT cast(COMPANYNO as varchar(36)),USERSTRING1 FROM CONTACT
WHERE USERSTRING1='$iusername'";

it gets one result (which is correct) but COMPANYNO is blank when i try and
print it (because it's something that can't be printed? i have no idea!)

Welp!
Jul 17 '05 #4

P: n/a
On Thu, 13 Nov 2003 14:43:34 +0000 (UTC), "Darren" <ze**@zeen.co.uk>
wrote:
No no, i want to use the first SQL output of the GUID into another SELECT
and i get the error..

Warning: Sybase message: Syntax error converting from a character string to
uniqueidentifier.

I'm not sure how to use '63180001000000000000000000000000' GUID output into
another SQL SELECT.


It is still a data representation problem. You need to find some
documentation on the syntactic representation of data types for the
Sybase SQL engine.

63180001 00000000 00000000 00000000

The above data looks like binary to me... or perhaps it is a complete
fluke that it splits nicely into a 64 bit word ?
kafooey
- ka*****@nospam.yahoo.co.uk
- http://www.pluggedout.com/blog
Jul 17 '05 #5

P: n/a
"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...
Hi wondering if anyone can help.

What i'm trying to do is get a company from a MSSQL database with the
COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want to reference it to another table (on same database) to extract more details.
Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
often used as "synthetic" primary keys!

The uniqueidentifier data type stores 16-byte binary values that operate
as globally unique identifiers (GUIDs). A GUID is a unique binary number;
no other computer in the world will generate a duplicate of that GUID value.
The main use for a GUID is for assigning an identifier that must be unique
in a network that has many computers at many sites.

It is usually of the form:
* Character string format
'6F9619FF-8B86-D011-B42D-00C04FC964FF'

* Binary format
0xff19966f868b11d0b42d00c04fc964ff

The thing i suspect you want is an "int identity" Primary key, and you make
one like this:
create table CONTACT
(
Id_Contact int identity,
Name int not null,
Phone varchar(255) null,
constraint PK_Contact primary key (Id_Contact)
)
go

--
Dag.


When I get the company name and company no the company no outputs like...
63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?

I wanted to reference another table so I...
$query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
COMPANYNO='$companyno'";

And of course this wouldn't work and the error was:

Warning: Sybase message: Syntax error converting from a character string to uniqueidentifier.

I need help with this as binary/hex sometimes goes over my head. If anyone
can point me in the right direction that would be great. I've had this same problem before and did a crappy 'work around' - now i've realised why I did the work around!

Thanks a lot for ANY help at all :)

(if nothing makes sense please say so :))

Darren

Jul 17 '05 #6

P: n/a
Forget I said something, Darren!

(Just read your follow-up posts, and it seems you are
aware of what a GUID is).

:-)

--
Dag.
"Dag Sunde" <da******@orion.no.way> wrote in message
news:3f******@news.wineasy.se...
"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...
Hi wondering if anyone can help.

What i'm trying to do is get a company from a MSSQL database with the
COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want to
reference it to another table (on same database) to extract more details.
Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
often used as "synthetic" primary keys!

The uniqueidentifier data type stores 16-byte binary values that operate
as globally unique identifiers (GUIDs). A GUID is a unique binary number;
no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique
in a network that has many computers at many sites.

It is usually of the form:
* Character string format
'6F9619FF-8B86-D011-B42D-00C04FC964FF'

* Binary format
0xff19966f868b11d0b42d00c04fc964ff

The thing i suspect you want is an "int identity" Primary key, and you make one like this:
create table CONTACT
(
Id_Contact int identity,
Name int not null,
Phone varchar(255) null,
constraint PK_Contact primary key (Id_Contact)
)
go

--
Dag.


When I get the company name and company no the company no outputs
like... 63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?

I wanted to reference another table so I...
$query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
COMPANYNO='$companyno'";

And of course this wouldn't work and the error was:

Warning: Sybase message: Syntax error converting from a character string to
uniqueidentifier.

I need help with this as binary/hex sometimes goes over my head. If

anyone can point me in the right direction that would be great. I've had this

same
problem before and did a crappy 'work around' - now i've realised why I

did
the work around!

Thanks a lot for ANY help at all :)

(if nothing makes sense please say so :))

Darren


Jul 17 '05 #7

P: n/a
I think i found the answer...

You can't compare a 'uniqueidentifier' to just any string,
it *MUST* be on the format (Like a GUID:

'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

Where each x is a hex digit. So you must convert your number
to hex, and add the hypens...

63180001000000000000000000000000
'00000000-0000-0000-D12F-846681000000'

hth...

--
Dag
"Dag Sunde" <da******@orion.no.way> wrote in message
news:3f********@news.wineasy.se...
Forget I said something, Darren!

(Just read your follow-up posts, and it seems you are
aware of what a GUID is).

:-)

--
Dag.
"Dag Sunde" <da******@orion.no.way> wrote in message
news:3f******@news.wineasy.se...
"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...
Hi wondering if anyone can help.

What i'm trying to do is get a company from a MSSQL database with the
COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I
want
to
reference it to another table (on same database) to extract more details.

Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
often used as "synthetic" primary keys!

The uniqueidentifier data type stores 16-byte binary values that operate
as globally unique identifiers (GUIDs). A GUID is a unique binary

number; no other computer in the world will generate a duplicate of that GUID

value.
The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.

It is usually of the form:
* Character string format
'6F9619FF-8B86-D011-B42D-00C04FC964FF'

* Binary format
0xff19966f868b11d0b42d00c04fc964ff

The thing i suspect you want is an "int identity" Primary key, and you

make
one like this:
create table CONTACT
(
Id_Contact int identity,
Name int not null,
Phone varchar(255) null,
constraint PK_Contact primary key (Id_Contact)
)
go

--
Dag.


When I get the company name and company no the company no outputs like... 63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?

I wanted to reference another table so I...
$query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
COMPANYNO='$companyno'";

And of course this wouldn't work and the error was:

Warning: Sybase message: Syntax error converting from a character string
to
uniqueidentifier.

I need help with this as binary/hex sometimes goes over my head. If

anyone can point me in the right direction that would be great. I've had this

same
problem before and did a crappy 'work around' - now i've realised why

I did
the work around!

Thanks a lot for ANY help at all :)

(if nothing makes sense please say so :))

Darren



Jul 17 '05 #8

P: n/a
Hi Darren!

On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <ze**@zeen.co.uk>
wrote:
Hi wondering if anyone can help.

What i'm trying to do is get a company from a MSSQL database with the
COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want to
reference it to another table (on same database) to extract more details.

When I get the company name and company no the company no outputs like...
63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?

I wanted to reference another table so I...
$query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
COMPANYNO='$companyno'";


Apart from your problems, can't you use a join and one query or even a
view?

HTH, Jochen
--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/
Jul 17 '05 #9

P: n/a
Excellent, I'll give this a shot!

Cheers

:Darren
"Dag Sunde" <da******@orion.no.way> wrote in message
news:3f********@news.wineasy.se...
I think i found the answer...

You can't compare a 'uniqueidentifier' to just any string,
it *MUST* be on the format (Like a GUID:

'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

Where each x is a hex digit. So you must convert your number
to hex, and add the hypens...

63180001000000000000000000000000
'00000000-0000-0000-D12F-846681000000'

hth...

--
Dag
"Dag Sunde" <da******@orion.no.way> wrote in message
news:3f********@news.wineasy.se...
Forget I said something, Darren!

(Just read your follow-up posts, and it seems you are
aware of what a GUID is).

:-)

--
Dag.
"Dag Sunde" <da******@orion.no.way> wrote in message
news:3f******@news.wineasy.se...
"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...
> Hi wondering if anyone can help.
>
> What i'm trying to do is get a company from a MSSQL database with the > COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want to
> reference it to another table (on same database) to extract more details.

Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
often used as "synthetic" primary keys!

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID

value.
The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.

It is usually of the form:
* Character string format
'6F9619FF-8B86-D011-B42D-00C04FC964FF'

* Binary format
0xff19966f868b11d0b42d00c04fc964ff

The thing i suspect you want is an "int identity" Primary key, and you

make
one like this:
create table CONTACT
(
Id_Contact int identity,
Name int not null,
Phone varchar(255) null,
constraint PK_Contact primary key (Id_Contact)
)
go

--
Dag.
>
> When I get the company name and company no the company no outputs

like...
> 63180001000000000000000000000000
> In basic terms I'm only getting it like...
> $query = "SELECT COMPANYNO FROM CONTACT";
>
> Now I know this isn't a 16 byte binary - is it?
>
> I wanted to reference another table so I...
> $query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE > COMPANYNO='$companyno'";
>
> And of course this wouldn't work and the error was:
>
> Warning: Sybase message: Syntax error converting from a character

string
to
> uniqueidentifier.
>
> I need help with this as binary/hex sometimes goes over my head. If

anyone
> can point me in the right direction that would be great. I've had this same
> problem before and did a crappy 'work around' - now i've realised
why I did
> the work around!
>
> Thanks a lot for ANY help at all :)
>
> (if nothing makes sense please say so :))
>
> Darren
>
>



Jul 17 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.