XAMP, MsSQL-Server 2008, PHP 5.3.x, MS pdo_sqlsrv_vc6_ts.dll

I get the problem, but i don't understand the solutions

Can you explain a bit more?

1. how do i leave a datatype empty? and which one?
2. how do i use char/varchar when saving?

I think this is just a problem of unspecific descriptions

Hi Benjamin

Sorry, for this long delay.
I had a deeper inspection what happens when I persist data into a field of type varbinary(MAX) and I got this (using the SQL profiler):

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 nvarchar(36),@P2 nvarchar(max),@P3 nvarchar(34)',
N'INSERT INTO mc_dokument_data (id, data, mc_dokument_id) VALUES (@P1, @P2, @P3)',
N'03DDAAD1-4BFA-416B-A0C0-9B8B7148F31C',
N'0x3c3f786d6c20766572736...',
N'mwe3bc2c0da6543d1f48d7c83e64f5c449'
select @p1

But it has to be ( removed '-quotes and @P2 changed to varbinary(MAX) ):

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 nvarchar(36),@P2 varbinary(max),@P3 nvarchar(34)',
N'INSERT INTO mc_dokument_data (id, data, mc_dokument_id) VALUES (@P1, @P2, @P3)',
N'03DDAAD1-4BFA-416B-A0C0-9B8B7148F31C',
0x3c3f786d6c20766572736...,
N'mwe3bc2c0da6543d1f48d7c83e64f5c449'
select @p1

I am not really sure any more if this is caused from Doctrine or from the 'MS SQL-Server PHP-PDO-Driver 2.0.1' .
But I guess this comes from the driver.
If you can validate this, I will make a bug report to them.

The funny thing is if I do this via executeQuery it works...

$data = unpack("H*" , __some__data__);
$data = '0x'.$data[1];

$em->executeQuery(
    "INSERT INTO mc_dokument_data (id, mc_dokument_id, data)". 
   "VALUES('".$id."' , '" . $documentId . "', " . $data . " )"
);

Hopefully I could clarify my problem.

Regards
Martin Weise

2.1.0-DEV

This is an improvement

Hi,

If cache value is specified then syntax should contain CACHE keyword that follows the value and if value is not specified then it should go without CACHE keyword.
In this case sequence will be created with CACHE value 20 by default.
Min cache value is 2 and for determine max value we should have sequence max value defined which is by default 10^27 for an ascending sequence and -1 for a descending sequence.
Now the max cache value is calculated by the formula (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT).

My Idea is to add cache attribute to Sequence object and then under the previous story, set cache to given value if value is greater than 1 and less than calculation by the given formula.

For nocache,
I would use value of 0 so we will have values in domain (0,1<x<=(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)).

Some public function will return NOCACHE or CACHE n and that function could be called in getCreateSequenceSQL method from Sequence input parameter.

Anyone have a better idea or I can code this and request pull from my fork?

Duplicate issue http://www.doctrine-project.org/jira/browse/DBAL-348

Greetings
Ramon Ornelas

Oracle Express 11g and doctrine-dbal 2.2.2

When I put a breakpoint on the second line of the diffColumn method in my IDE and stop there during debugging I have those variable values:

$this				Doctrine\DBAL\Schema\Comparator		
$changedProperties		array[0]		
$column1			Doctrine\DBAL\Schema\Column		
  _type				Doctrine\DBAL\Types\DateTimeType	<-- datetime
  _precision			integer		10	
  _scale			integer		0	
  _unsigned			boolean		0	
  _fixed			boolean		0	
  _notnull			boolean		0	
  _autoincrement		boolean		0	
  _platformOptions		array[0]		
  _comment			string		""	
  _customSchemaOptions		array[0]		
  _name				string		"SDGDATE"	
  _quoted			boolean		0	
$column2			Doctrine\DBAL\Schema\Column		
  _type				Doctrine\DBAL\Types\DateType		<-- date
  _precision			integer		0	
  _scale			integer		0	
  _unsigned			boolean		0	
  _fixed			boolean		0	
  _notnull			boolean		0	
  _autoincrement		boolean		0	
  _platformOptions		array[1]		
  _customSchemaOptions		array[0]		
  _name				string		"SDGDATE"	
  _quoted			boolean		0	

You see that there are different types. But it would be nice if the comparison would say: "Theay are equal - no diff"

This issue can't be fixed. Doctrine has this type abstraction here which prevents a special case fix here. The solution in this case is obvious, set the type to "datetime" on Oracle.

Sorry, this issue commes up again.

Shipment:
  type: entity
  fields:
    id:
      type: integer
      id: true
      generator:
        strategy: AUTO
    pickupDate:
      type: date
      column: pickup_date
    pickupTimeFrom:
      type: time
      column: pickup_time_from
  lifecycleCallbacks: {  }

When I update my schema with the above YAML mapping, I always get the following error although I haven't changed anything at the mapping.

C:\projects\xyz\trunk>php app\console doctrine:schema:update --force
Updating database schema...
Der Befehl "stty" ist entweder falsch geschrieben oder
konnte nicht gefunden werden.

[Doctrine\DBAL\Driver\OCI8\OCI8Exception]
ORA-01442: column to be modified to NOT NULL is already NOT NULL

C:\projects\osl\trunk>php app\console doctrine:schema:update --dump-sql
ALTER TABLE SHIPMENT MODIFY (pickup_date DATE NOT NULL, pickup_time_from DATE NOT NULL)

Okay, I could change the types to 'datetime', but what if I have to move to MySQL? I just want to store 'date' and 'time', not 'datetime'.

Any PostgreSQL environment

PHP version 5.4.11
Oracle 11g Instant Client version 11.2.0.3.0
Oracle Database 11g Enterprise Edition version 11.2.0.3.0 (x64)
OCI8 DBAL driver

Hi there,
Wondering if there is any update on this? I'm having to use a workaround in my applications.
Cheers,
Max

Can you maybe show an entity definition with its sequence mapping?

Here you are mate. Please see attachment.

I cant seem to find the problem, in DBAL "lib/Doctrine/DBAL/Platforms/OraclePlatform.php" on line 171, the sequence statement is created with $sequence->getQuotedName($platform), but this only works if quoting is requrested for the sequence.

How do you actually create the sequence? Your entity doesnt have @GeneratedValue.

What does the create schema command say with "--dump-sql" flag? Is the SQL quoted?

PostgreSQL

It seems the only missing piece is the implementation of the getGuidExpression method in the PostgreSqlPlatform class.

Details on generating UUIDs from postgres can be found here:

http://www.postgresql.org/docs/current/static/uuid-ossp.html

Things that would need to be addressed:

1) It requires the uuid-ossp module to be installed in the database. Solve with documentation or ...?

2) There isn't just one function. Just pick one or make it configurable with a sane default?

I have implemented this for my own project, using what I think is a sane default:

public function getGuidExpression()

The other issue I think that needs addressing is that the extension must be loaded into the current database upon creation. Either this is a deal-breaker right here (relying on a PGSQL plugin that's not available by default)

OR

If not, maybe schema:update/create needs to check for whether postgresql is being used, whether there is a guid type/generator being used in an entity, and then either execute CREATE EXTENSION uuid-ossp;, and failing that return an exception that informs the user that uuid-ossp must be available as an extension before GUID generation can be used in entities.

Can someone explain to me please the position on this currently? Is it not ok to rely on non-core/standard functionality?

Is there any sort of decision on this item? I was trying to use UUID with Postgres and finally got it to work by adding

public function getGuidExpression()

and went to submit a patch, then found this ticket and see that it's the exact same solution that Ross Cousens submitted above. I'd really like to not maintain my own fork of the repository just to have this change in place, and it seems like a reasonable fix (barring the uuid-ossp extension not being a Postgres default extension). Can we get this in there so it's available, and worry about the issue of informing the user about the extension at a later point?

Or is there an easy way that anyone knows that I can add the function to my own class (in a Symfony2 project) that would extend PostgreSqlPlatform.php and add the function, without the need for me to fork Doctrine and add it on my own?

Thanks,
--mark

Pull request submitted

https://github.com/doctrine/dbal/pull/312

I hope this gets accepted but I fear it won't.

The original complaint against implementing GUID for the PostgreSQL platform driver was because a) it required a separate extension to be enabled on the server itself b) and there were a number of GUID generation functions available.

To use anything but v4 would be asinine unless some external constraint was forcing you to use the older generation algorithms, so I think argument b is mostly moot. Argument a can easily be solved with documentation, programmatically as well (would require more work) or just left as is because the error back from postgresql is very verbose. Cannot find function uuid_generate_v4. Google it, see that extension is required, see that it's available in PostgreSQL contrib packages, enable it, and voila it works.

Agreed, and I think that having SOMETHING that is usable (and has a very clear message that you need to install an extension) is better than nothing. The implementation can be expanded later to cover the extension not existing, or using something other than v4, etc. But this should be good enough for most people and it seems silly not to have, or at least not have a way to override and provide it.

I was looking at the Symfony configs to see if there was a way to define my own platform class that could then just extend the current PostgreSqlPlatform class and add a the function, but that doesn't seem to be doable (or I just missed it)

I just saw that this got merged into master. As soon as this hits 2.3* I can do away with the manual UUID generation for id's that we're currently doing

Thanks much Benjamin!

Yay, I am happy that this has been accepted and will make it through to next release. Thank you Mark/Benjamin!

This should be done in the master branch.

Another solution, avoiding to create many classes, would be to use the exception code, which is always kept as 0 currently (the default value of the Exception class). You could have a code for each case (with constants in the DBALException class) and then checking $e->getCode() to identify what went wrong.

I'd prefer actual named exceptions. It makes catching them simpler. However, adding some code defined in DBAL would be an acceptable alternative.

try {
    /* ... /*
} catch (NoSuchTableException $e) {
    // do something
} catch (DuplicateKeyException $e) {
    // do something else
}

v.s.

try {
    /* ... /*
} catch (DBALException $e) {
    if ($e->getCode() == DBALException::NO_SUCH_TABLE) {
        // do something
    } elseif ($e->getCode() == DBALException::DUPLICATE_KEY) {
        // do something else
    } else {
        throw $e;
    }
}

I would also prefer named exceptions. You're going to have a lot of problems providing the "code" value in DBALException in any case: SQLSTATE codes are alphanumeric, and will cause warnings/errors when creating new exception.

Besides we can get the SQL state code now:

    try {
        // ...
    } catch (\Doctrine\DBAL\DBALException $e) {
        $code = $e->getPrevious()->getCode();
        // do stuff with $code
    }

The problem is that there are a lot of error codes defined in the ANSI SQL standard: http://www.postgresql.org/docs/9.2/static/errcodes-appendix.html

Maybe throwing an specific exception for each "class" of SQLSTATE codes? So if the error code from a PDO exception starts with 23, DBAL would throw `\Doctrine\DBAL\Exception\IntegrityConstraintViolationException`.

This also seems like the logic to handle throwing exceptions should be contained in the platforms as some implementations may differ. You could have a method in `AbstractPlatform` that takes care of the ANSI SQLSTATE error code classes and leave it up subclasses to deal with platform specific cases. Whenever `Connection` catches a `PDOException`, dispatch it to the platform to deal with.

Example: https://gist.github.com/chrisguitarguy/e021918900e93dca304d

Thoughts?

I have implemented a thing of that kind in a personal project (on top of Doctrine). It is really useful to be able to catch a ForeignKeyViolationException, and get with entity/field caused the problem (for that my EntityManager wrapper parse the exception message).

However, note that exception codes differ from DB engines. In my case, I did it quick and used MySQL error codes, but managing different RDBMS implies more work.

I'm having the same issue on my production webserver, but not on the development webserver. I find that odd. It tries to drop all foreign keys and create them again, although without the CHANGE statement you are referring to, Dmitry.

This maybe fixable by making a hash out of the column definition and saving it into a database comment.

The Foreign Key problem maybe because of an old MySQL version 5.0.x

@beberlei, sounds good to me. any plan of implementing this?

How do you call the schema/platform code?

The code is correct, i think you are using it wrong.

I don't use it directly.
I use it in my entity class file with annotations.
e.g.
@Mapping\Column(type="text")
@Mapping\Column(type="text", length=65535)

I dont think this is worth including in the main distribution. A char does not save much compared to a varchar (1 Byte?) and you already have 2 options to make a char:

• create your own custom type (i.e. FixedString)
• Use @Column(..., columnDefinition="CHAR(2)")

IMHO, just use a string type with the length you want: @Column(type="string", length=2). That becomes a varchar with length 2.

Ya, it is only 1 extra byte. I'm not sure what to do, I like things as optimized as possible, but I guess I could just use columnDefinition if necessary.

Actually, I don't really see any reason not to include the char type.

How about code bloat?

How many LoC?

@darkangel Around 40. And lots of conditionals, which decreases efficiency of algorithm.

I vote for FixedString DBAL DataType.

We will not put every special data type someone comes up with in the core library. If we go this route, at the end we have 100+ data types (100+ classes plus a bloated type map) in the core library.

There are at least 2 decent options of making a char already if you care about byte counting (see above).

"Why not?" is not the question to ask for when it comes to new features. If it were, we would include a whole lot of stuff that is useless for 99% of the users. There must be strong arguments for "Why?" and there are none.

If we get 50+ votes on this issue we can talk again.

Of course not. I didn't know that char was a special data type (especially since it's supported in DC1.2).

I will use @columnDefinition.

You may close this issue.

No need to become defensive. There is still the chance that demand for this particular type gets very high and that can change things.

Thats why this stays open. Otherwise the next guy would probably just create a duplicate ticket (not sure whether non-owners can reopen other tickets).

@Glen

The problem with Doctrine 1 and having lots of different data-types is that of maintainability. You have to ensure that all the types work on all supported platforms with each and every version. The more datatypes we support by default the more complex will it be for the Doctrine 2 Core to ensure compability and maintainability in this regard.

For each new platform that we will support all datatypes have to be supported for example, something that might even become impossible for some databases.

Adding a datatype from the user perspective is rather simple though, it has to be tested once and is only about 20-40 LOC. I bet you 100 bucks that soon there will be code-snippets out there on all the different database specific types as a doctrine 2 implemention.

@Roman
Not being defensive, I have accepted/respected your decision.

@Benamin
I did think about cross-database compatibility. As far as I know, char is supported by the vast majority (or at least easily simulated). But I understand.

Thanks.

Sorry for party rocking but i think that 'fixed' annotation should be enabled in ORM column definitions.
In this case @Column(type="string", length=4, fixed=true) would result in "CHAR(4)" for MySQL, Oracle and Postgres, Sqlite, and DB2, "NCHAR(4)" for MQSQL. etc... It is much more elegant than that columnDefinition.
It is already supported by DBAL as well as 'default' heartlessly removed (DDC-100) from annotation driver due to bizzare reluctance to deal with proper escaping of values.